cozmo_troll
asked on
Copy table data from one DB to another
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!
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!
Dim sql As String
Dim sPath As String
Dim sFldNamesTo As String
Dim sFldNamesFr As String
sPath = "\\Server01\templates\Your ServerBata Base.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
Dim sPath As String
Dim sFldNamesTo As String
Dim sFldNamesFr As String
sPath = "\\Server01\templates\Your
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 ].[TargetT able](Fiel d1, Field2) Select Field1, Field2 From [C:\SourceFolder\SourceMdb ].[SorceTa ble]"
mike
CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb
mike
Please note .mdb was missing from:
CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb .mdb].[Tar getTable]( Field1, Field2) Select Field1, Field2 From SorceTable"
CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb
ASKER
where do I do or run any of these things?
ASKER
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]
ASKER
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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)
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.
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
ASKER
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.
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
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",
--------------------------
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"
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
And the solution provided does that.
Mike
ASKER
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°)
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°)
CurrentDB.Execute "Insert Into [C:\TargetFolder\TargetMdb
Try with one filed first and then add more fields later.
mike