[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy table data from one DB to another

Posted on 2006-05-18
21
Medium Priority
?
327 Views
Last Modified: 2008-02-01
I have a problem, and am wondering if any advice.

I have a lot of data on several tables in one DB. I need to copy the date (including AutoNumbers) to a different data base with similar styles but not identical. When I am trying this now, the AutoNumber field doesn't stay the way it was on the original table; as expected it continues in numerical order in the new table. I need to keep the data because other tables contain relationed data to all of the auto's. Please help!

0
Comment
Question by:cozmo_troll
  • 7
  • 7
  • 6
  • +1
21 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16712836
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16712844
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16712855
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16712878
Please note .mdb was missing from:

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

Author Comment

by:cozmo_troll
ID: 16713002
where do I do or run any of these things?
0
 

Author Comment

by:cozmo_troll
ID: 16713004
also, what if the tables are NOT identical, but the field names that I want to import are...?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16713014

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]
0
 

Author Comment

by:cozmo_troll
ID: 16713023
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16713030

> 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
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 16713067
Re:> where do I do or run any of these things?

You can add

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

to OnClick event of a button.

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

TargetTable, SorceTable could be the same or different.
Flield names could be differnt also as long as the have same property ( you cann't copy a text field to date field, for example).

Copy one field first to make sure the code works.  Then add the other fields.  There are many different situation you may come accross and the have to be dealt later.

mike
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16713077
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
0
 

Author Comment

by:cozmo_troll
ID: 16713082
I need the autonumber to keep function.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16713090
Re Autonumber:

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

Expert Comment

by:jjafferr
ID: 16713129
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)
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16713138
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16713139
but if you have any relation set for the TO Table in the TO mdb, then this won't work
0
 

Author Comment

by:cozmo_troll
ID: 16713155
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16713159
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16713200
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
0
 

Author Comment

by:cozmo_troll
ID: 16713214
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16713901
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°)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question