Copying a database table "fast" ?...

Im using ADO 1.5 to connect to an Access Database.

I have a table with some 20000 records. I want to write a
function that will copy this table into another table, faster
than just enumerating each record and adding it to the
targettable. (Which is very slow in this case :)

There must be some faster way to do this?

ADO have a GetRows() function that will return all data from
a recordset into a variant, fassst. I think i need a PutRows()
that will work the otherway around... heh, well - im not that
lucky...

/Mtoft
LVL 2
mtoftAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
shchukaConnect With a Mentor Commented:
Why do you need to copy the table?  Can you link the table instead?  The following code does the following:
    * Link a table from another database to this database
    * Copy all the data from the linked table into a new table in this database
    * Remove the link to external table
If you can use link, rather use it and remove unnecessary code in this case.

    Dim db1 As Database
    Dim tbdef As TableDef
   
    Set db1 = OpenDatabase("current.mdb")              'Open current database
    Set tbdef = db1.CreateTableDef("TempTable")       'Create TableDef for the link
    tbdef.Connect = ";DATABASE=external.mdb"       'Set the link source database...
    tbdef.SourceTableName = "Output"                      '... and table
    db1.TableDefs.Append tbdef                                'Add link to current database
'Remove the following two lines if you are fine with a link
    db1.Execute ("SELECT * INTO CopyTable FROM TempTable")   'Copy data
    db1.TableDefs.Delete "TempTable"                       'Remove link
    db1.Close                                                          'Close current database

0
 
mdannyCommented:
Have You thought about using a SQL query to accomplish that?
0
 
TheAnswerManCommented:
Use the Execute
I am not Sure on your ADO Connection
In RDO I would do this.

Assume a connection called g_rdo
which is opened to that database

'This line will do it.
g_rdo.execute "Select * into NewTable from OldTable"
'This way is 'Fast as balls'

'you can use a 'Where' clause to select only certain records if you like


0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
TheAnswerManCommented:
Here is something a little more ADO Like..

Dim CopyIt As New ADODB.Command
Set CopyIt.ActiveConnection = gado   'where gado is an ADO Connection
CopyIt.CommandText = "Select * into NewTable from OldTable"
CopyIt.Execute
 
0
 
TheAnswerManCommented:
Here is something a little more ADO Like..

Dim CopyIt As New ADODB.Command
Set CopyIt.ActiveConnection = gado   'where gado is an ADO Connection
CopyIt.CommandText = "Select * into NewTable from OldTable"
CopyIt.Execute
 
0
 
mtoftAuthor Commented:
The tables are in different databases.
0
 
majed100598Commented:
THE EASIEST WAY TO DO THIS IS BY USING ACCESS PROGRAMM
- CHOOSE AVERSION OF ACCESS COMPATABLE TO YOUR DATABASE
- OPEN DATABASE
- SELECT THE TABLE YOU WANT AND CHOOSE COPY FROM EDIT MENU
- CHOOSE PASTE FROM EDIT MENU
- SET THE NAME OF THE TABLE YOU WANT
0
 
mtoftAuthor Commented:
Thank you for that comment Majed, it was ... a comment...

However, this is a question of how to implement a fast table-copy function from one datasource to another, using visual basic. (This is a visual basic group, yes?)



0
 
mtoftAuthor Commented:
Thank you for that comment Majed, it was ... a comment...

However, this is a question of how to implement a fast table-copy function from one datasource to another, using visual basic. (This is a visual basic group, yes?)



0
 
TheAnswerManCommented:
"Select * into NewTable from OLDDatabase..OldTable"

When going from another Database...
0
 
mtoftAuthor Commented:
Ok, i cant do it like this in my case - but i think it answers my question...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.