Adding records to access table from ado recordset

[daveonion]

Hi I have an ado recordset that contains 1000 records, whats the fastest way to populate an access table with all records.

I am currently looping through each record but this seems to be taking a long time

thanks

 

[Golom]

If you're generating the recordset from an SQL statement (i.e. its not a disconnected recordset) then something like

Dim InsertSQL As String

InsertSQL = "INSERT INTO myTable (f1, f2, ... ) " & _
            "... Recordset SQL ... "

conn.Execute InsertSQL

where

"... Recordset SQL ... " is the SQL that you are using to create the recordset and

conn is an ADO connection.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[daveonion]

hi the above code trys to add the data into a table on the sql server but not a table in the current db, i need it to add the data into a local table

thanks

 

[Golom]

The code adds records to whatever table you specify as "myTable". If you want it to be a local table then specify one and specify the connection as Current.Connection

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[daveonion]

Hi Sorry i dont think ive made myself clear,

the ado recordset contains records from a SQL server, however i now want to insert those records into an access table in the local access database

 

[JerryKlmns]

It is either

Looping through SQL server recordset (.CursorLocation = adUseServer, .CursorType = adOpenForwardOnly, .LockType = adLockReadOnly) and adding records to the table with the MSAccess recordset (.CursorLocation = adUseClient, .CursorType = adOpenKeyset, .LockType = adLockBatchOptimistic)

Or

Link all necessary tables of SQL server and run an append query like Golom's post.