How to Copy a table in code?

[cityboy3]

I have an Access 2002 app spilt into front and back-end MDBs. I'm trying to write a function that will archive data on the back end once a year. My first though was to use the CopyObject function to create a new copy of my table, give it a new name, and then delete records to prepare for a new year of data entry.

However, because I'm running from the front end, the CopyObject only sees my link to the table, not the table itself, so it ends up copying a link of my table to itself in the back-end mdb. Any way to copy the table itself without resorting to SQL code that will append all the records?

thanks

 

[HitechUser]

cityboy3,

If all of the table attributes are not important then just use a make table query to create your copy.

Detail here.

http://support.microsoft.com/default.aspx?scid=kb;en-us;199138

Where are you trying to create the copy too?

 

[cityboy3]

Thanks for responding. The source table is by back end MDB, which is not open during this process. The user will have the front end MDB open with a link to the table on the back-end MDB

 

[HitechUser]

cityboy3,

I got that. Where are you trying to place your copy? In the back-end database?

 

[cityboy3]

yes

 

[dRahme]

Hi, having a little trouble along these lines too. I would like to copy a table out of the linked back end into an external db for archival purposes and am currently copying the link.

Table structure is somewhat important and I would prefer to do this without use of the make-table query.

A solution, if there is one, would be most appreciated.

Thanks, dRahme

 

[Edski]

Have you tried the TransferDatabase method?

DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

 

[dRahme]

Well, I am about there. Here is the string:

DoCmd.TransferDatabase acExport, "microsoft access", "c:\OriginalDb\Data\OriginalData.MDB", acTable, "tblTestOrig", "C:\StoredDb\testdb.mdb", False

I wish to transfer 'tblTestOrig' from 'OriginalData.mdb' to
'testdb.mdb'

The statement bombs on the database I wish to transfer the table to - "C:\StoredDb\testdb.mdb" . The error message says this is not a valid name. The path is valid. The db name is valid, so I must have a syntax error.

Any suggestions?

Thanks, dRahme

 

[cityboy3]

yes, it does the same thing -- just copies a link

 

[dRahme]

Ok, got the syntax corrected. It still copies the link. Maybe have to go the make table route.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


Dim XferPath As String
XferPath = ("c:\temp\new\db3.mdb")

MsgBox XferPath

DoCmd.TransferDatabase acExport, "microsoft access", XferPath, acTable, "tblWBS", "tblWBSnew", False
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[dRahme]

Ok, the workaround. This involves creating a table to keep the structure, appending files to the table, copying the table out and deleting the temp table:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim StrSql As String 'Table
Dim wbDateStr As Date 'Unique Date
Dim NewTblName As String 'New Temp Table Name Name

wbDateStr = Format(Date, "mm-dd-yyyy") & " " & Format(Time, mm)

MsgBox wbDateStr

'Create the Table

StrSql = "Create Table tblWbsTemp (WBSid Counter Not Null Constraint WBSid primary key, " & _
" WBSno VarChar (20), WbsDescr VarChar (50))"

DoCmd.RunSQL StrSql

'Append Records

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblWbsTemp ( WBSno, WbsDescr )" & _
"SELECT tblWbs.WbsNo, tblWbs.WbsDescr FROM tblWbs; "

DoCmd.SetWarnings True

'Rename the Table

NewTblName = "tblWbsTemp" & " " & wbDateStr


DoCmd.Rename NewTblName, acTable, "tblWbsTemp"


'Copy the Table

DoCmd.CopyObject "C:\Temp\New\db3.mdb", NewTblName, acTable, NewTblName

'Delete the Temp Table

DoCmd.DeleteObject acTable, NewTblName

MsgBox "Success!"

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Hope that helps someone.

dRahme