CopyObject table link? (1 Viewer)

[jsic1210]

Hello, I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes. Can someone help?

Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database

' error handle
On Error GoTo F_Error
' warnings
DoCmd.SetWarnings False
' set variables
    ' strfile
strFile = "N:\Price Increases\Fee Notice Import Master.xlsx"
    'temp
temp = "tempNotices"
    'tbl
tbl = "tblNotices"
    ' db
Set db = CurrentDb()
' confirm
If MsgBox("Would you like to import Fee Notices from the master file?", vbYesNo, "Import") = vbYes Then
    ' create temp table
    DoCmd.CopyObject , temp, acTable, tbl
    ' delete all from temp table
    DoCmd.RunSQL "DELETE * FROM " & temp

 

[Trevor G]

I've just replicated your code and it works fine. can you post a slimed down version of the database and spreadsheet (remove all sensitive data)

 

[jsic1210]

Actually, I copied the table into a new database, and it worked fine. I'm not sure why that would happen. I will have to look a little more into it.

 

[jsic1210]

What really doesn't make sense is that if I make a copy of the source table, calling it tblNotices1 (and set tbl = "tblNotices1"), nothing deletes. Is it possibly because tblNotices is linked from another database? This is a front end database, so all the tables are.

 

[revlisj]

Greetings,

I'm reviving this post. I'm using Access 2010 and have run into the same issue. I simply want to copy the structure of one table (source) to another (target). Access seems to link the tables together. Whatever I do to one table effects the other table. Delete a record in Target_Table, it's gone in Source_Table_TEMPLATE and visa versa. The tables are in a back end database. The code I'm running is in a front end database. I've tried compacting both databases but nothing works.

Here is the code I'm running:
DoCmd.CopyObject , "Target_Table", acTable, "Source_Table_TEMPLATE"

I also tried the TransferDatabase function as follows. Same results.

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "T_Rate_Data_Compare_TEMPLATE", TargetTableName, StructureOnly:=True


Cheers

 

[Cronk]

If you are trying to copy a linked table, that's exactly what you get, another link to the same back end table.

 

[revlisj]

Thanks for the prompt response. Do you know of a way to copy a back end table into a local table (in the front end) via VBA? If I simply use the Copy and Paste function (table structure only) in the Object browser, it works fine. I get a new/empty local table with the same structure as the copied back end table.

Cheers

 

[arnelgp]

to save to local table (new table) the structure:

"SELECT * INTO NewTableName FROM SourceTable(link or no linked) WHERE TRUE=FALSE;"

to save to local table (new table) with data:

"SELECT * INTO NewTableName FROM SourceTable(link or no linked);"

 

[revlisj]

Re: SOLVED CopyObject table link?

Awesome, it worked!
"SELECT * INTO " & TargetTableName & " FROM T_Rate_Data_Compare_TEMPLATE WHERE TRUE=FALSE;"

Thanks so much.