Move data from one table to another (different DB) (Access 2003)

Topic

New Reply

I have two separate databases. One on our network drive and the other on my local drive. The network drive includes tables that have large amounts of data in each and the local drive contains the same table structures but no data. At certain times, I want to be able to load data on the local drive with what is on the network drive and would like to accomplish this without having to use the ‘Link Tables…” option under the database window.

So far I have the following, however I’m not sure that this is the most efficient way to handle this. I could loop through and copy all records in rst over to the local recordset (not yet setup), but I was thinking that it would be more efficient to do some type of SQL??? Does anyone pointers on how I could do this the most efficient way possible?

Sub Refresh_TRaw_Agile_MPN()
On Error GoTo ErrHandler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = OpenDatabase(“R:CompengMasterDataCE_ExternalData.mdb”, , True)
Set rst = dbs.OpenRecordset(“T_ImportDetail”, dbOpenSnapshot)

Do While Not rst.EOF

rst.MoveNext
Loop

ExitHandler:
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbCrLf & “Error Number: ” & Err.Number
Resume ExitHandler
End Sub

Thanks in advance for any help,
Drew

Reply | Quote

Replies

You can create an append query in SQL view:

INSERT INTO T_ImportDetail SELECT * FROM T_ImportDetail IN 'R:CompengMasterDataCE_ExternalData.mdb'

Save the query, and run it.

If you prefer to use code:

Dim strSQL As String
strSQL = "INSERT INTO T_ImportDetail SELECT * FROM T_ImportDetail IN 'R:CompengMasterDataCE_ExternalData.mdb'"
CurrentDb.Execute strSQL, dbFailOnError

The IN clause imports directly from the external database, without the need for a linked table.

Reply | Quote

Works perfect and is easier than I had expected.
Thanks

Reply | Quote