Copy table data from one DB to another

Assuming you have identical tables in both databases:

CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb].[TargetTable](Field1, Field2) Select Field1, Field2 From SorceTable"

Try with one filed first and then add more fields later.

mike

Dim sql As String
  Dim sPath As String
  Dim sFldNamesTo As String
  Dim sFldNamesFr As String
     
  sPath = "\\Server01\templates\YourServerBataBase.mdb"
  sFldNamesTo = "[PatID],[Last Name],[First Name],[DOB]"
  sFldNamesFr = "[PatID],[Last Name],[First Name],[DOB]"
  sql = "INSERT INTO [cacheName] (" & sFldNamesTo & ") SELECT "
  sql = sql & sFldNamesFr & " FROM [tblPatientInfo] IN '" & sPath & "'"

'  MsgBox sql
  CurrentDb.Execute sql

You can do the same from a third database by:

CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb].[TargetTable](Field1, Field2) Select Field1, Field2 From [C:\SourceFolder\SourceMdb].[SorceTable]"

mike

Please note .mdb was missing from:

CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb.mdb].[TargetTable](Field1, Field2) Select Field1, Field2 From SorceTable"

where do I do or run any of these things?

also, what if the tables are NOT identical, but the field names that I want to import are...?

You cannot keep the Auto number field the same, you should change it in the TO Table to Number/Integer, this way it will work.
You can have this code on a click of a command button, or any other [Event Procedure]

so once I change it to a Number/Integer can I change it back to an AutoNumber? It needs to remain an auto number in the new database for everything to keep functioning properly.

Thank you.

> also, what if the tables are NOT identical, but the field names that
> I want to import are...?

It will work, provided you write the field names in the same sequence in the line of code

Nope you can't

This is the limitation of the autonumber,

When you delete the last record or records in a table or in a form that has an AutoNumber field, and then you compact the database, the AutoNumber starts from zero

I need the autonumber to keep function.

Re Autonumber:

In your target table, change autonumber to Number/Long to take the value of Autonumbers from a SourceTable.

If you insist on the Autonumber field, then the best way to do it is:
1. Delete the TO Table from the mdb,
2. Export the Table from the FROM mdb to the TO mdb.

I don't know the code for this, but I am sure Mike have something in his sleeves ;o)

If you will copy data on many occasions where identical autonumber may be present, then in your target table have two fields called:

1. OldAutoNoValue   (Number/Long with duplicate option set to yes) to store AutoNumber values from source table
2. NewAutoNumber (Autonumber) to generate its own autonumbers as you add records to it.

but if you have any relation set for the TO Table in the TO mdb, then this won't work

The problem is that the new DB has the relationships, design, code, etc. that I need. The old DB has the data that I need to use otherwise reentering a million records.

I need the auto numbers because other tables have records related to the autonumber in each table.

Mike,
I have the following code in my archives, so maybe you can help cosmoz, as my time is up :)


Replace the field and table names with your own. Now delete the record that has been added to the PO Number table and your next record will begin at 1200.
CurrentDb.Execute "INSERT INTO MyTable (MyAutonumberField) VALUES(1199)"


OR


Set AutoNumbers to start from ...
Resetting an AutoNumber to 1 is easy: delete the records, and compact the database.

But how do you force an AutoNumber to start from a specified value? The trick is to import a record with one less than the desired number, and then delete it. The following sub performs that operation. For example, to force table "tblClient" to begin numbering from 7500, enter:

    Call SetAutoNumber("tblClient", 7500)

--------------------------------------------------------------------------------

Sub SetAutoNumber(sTable As String, ByVal lNum As Long)
On Error GoTo Err_SetAutoNumber
    ' Purpose:      set the AutoNumber field in sTable to begin at lNum.
    ' Arguments:    sTable = name of table to modify.
    '               lNum = the number you wish to begin from.
    ' Sample use:   Call SetAutoNumber("tblInvoice", 1000)
    Dim db As DAO.Database      ' Current db.
    Dim tdf As DAO.TableDef     ' TableDef of sTable.
    Dim i As Integer            ' Loop counter
    Dim fld As DAO.Field        ' Field of sTable.
    Dim sFieldName As String    ' Name of the AutoNumber field.
    Dim vMaxID As Variant       ' Current Maximum AutoNumber value.
    Dim sSQL As String          ' Append/Delete query string.
    Dim sMsg As String          ' MsgBox string.

    lNum = lNum - 1             ' Assign to 1 less than desired value.

    ' Locate the auto-incrementing field for this table.
    Set db = CurrentDb()
    Set tdf = db.TableDefs(sTable)
    For i = 0 To tdf.Fields.Count - 1
        Set fld = tdf.Fields(i)
        If fld.Attributes And dbAutoIncrField Then
            sFieldName = fld.name
            Exit For
        End If
    Next

    If Len(sFieldName) = 0 Then
        sMsg = "No AutoNumber field found in table """ & sTable & """."
        MsgBox sMsg, vbInformation, "Cannot set AutoNumber"
    Else
        vMaxID = DMax(sFieldName, sTable)
        If IsNull(vMaxID) Then vMaxID = 0
        If vMaxID >= lNum Then
            sMsg = "Supply a larger number. """ & sTable & "." & _
                sFieldName & """ already contains the value " & vMaxID
            MsgBox sMsg, vbInformation, "Too low."
        Else
            ' Insert and delete the record.
            sSQL = "INSERT INTO " & sTable & " ([" & sFieldName & "]) SELECT " & lNum & " AS lNum;"
            db.Execute sSQL, dbFailOnError
            sSQL = "DELETE FROM " & sTable & " WHERE " & sFieldName & " = " & lNum & ";"
            db.Execute sSQL, dbFailOnError
        End If
    End If
Exit_SetAutoNumber:
    Exit Sub

Err_SetAutoNumber:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , "SetAutoNumber()"
    Resume Exit_SetAutoNumber
End Sub

There are many side issues you have to deal with them (find solution as you decide and determine do).  The scope of this question was:  Copy table data from one DB to another

And the solution provided does that.

Mike

Yes, but note my referal several times to the AutoNumber... that is my question, I'm fully aware of how to copy a table the normal way.

Hello cozmo_troll

Copy from TableA to TableB in another database:
* "file / get external data / link tables" to get TableB
* new query, based on TableA
* menu "query / append query", choose TableB
* double-click header of TableA, drag all fields to grid
* adjust the "append to" row
* run

Append queries allow the transfer of an autonum to another autonum field.
Did you try it?

(°v°)