Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.m db", , 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    You can create an append query in SQL view:
    <code>
    INSERT INTO T_ImportDetail SELECT * FROM T_ImportDetail IN 'R:CompengMasterDataCE_ExternalData.mdb'
    </code>
    Save the query, and run it.

    If you prefer to use code:
    <code>
    Dim strSQL As String
    strSQL = "INSERT INTO T_ImportDetail SELECT * FROM T_ImportDetail IN 'R:CompengMasterDataCE_ExternalData.mdb'"
    CurrentDb.Execute strSQL, dbFailOnError
    </code>
    The IN clause imports directly from the external database, without the need for a linked table.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Works perfect and is easier than I had expected.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •