DoCmd.CopyObject and Retain Primary Key

ca1358
ca1358 used Ask the Experts™
on
Is there a way to use "DoCmd.CopyObject " and the table retain the Primary Key.
DoCmd.CopyObject "\\xxxxx\xxxxx\xxxxx\xxxx\xxxxx", "Backup_Backup" & sNow, acTable, "Backup_Backup"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you moving this in from another database? If so, then use the TransferDatabase method instead:

DoCmd.TransferDatabase acImport, ,"C:\MyFolder\MyDatabase.mdb", acTable,  "SourceTableName", "Destination TableName"

TransferDatabase method:
http://msdn.microsoft.com/en-us/library/aa220761%28v=office.11%29.aspx

Author

Commented:
I thank you kindly for your time.

I am Exporting the table to another database.
I am getting a runtime error 2507.
The type isn’t installed database type or doesn’t support the operation you chose.

Also I need to include the date and time as part of the Destination TableName.

I have included more of the code.  

Thank you again.
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 60000
End Sub

Private Sub Form_Timer()
DoCmd.RunMacro "macro1"
Me.Requery
Me.TimeRecord.Requery
Forms!form2!Form3.Requery

Dim ILSPBackup As String
Dim sNow As Variant
sNow = Format(Date, " yyyymmdd") & "_" & Format(Time, "hhnn")

Dim strDestinationMDB As String
Dim dbsData As DAO.Database
strDestinationMDB = XXXXXXXX\XXXXXXX\XXXXXX\MyDatabase.mdb"
Set dbsData = DBEngine.OpenDatabase(strDestinationMDB, _
False, False, ";pwd=XXXXXXX")
DoCmd.SetWarnings False
DoCmd.TransferDatabase acExport, ,"C:\MyFolder\MyDatabase.mdb", acTable,  "SourceTableName", "Destination TableName"
End Sub

Open in new window

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You really don't need to open the destination database. All you really need is the last line you have in that code block above.

You also obviously need to change the items in that method to match the names of the objects in your project, and to point to the correct destination database. The Destination db is the part where i wrote "C:\MyFolder\MYDatabase.mdb". Obviously, if you don't have that path and database, you'll get an error.

You can modify the Destination tablename as needed.

Note also that if your destination db is password protected, you can't use this method.

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial