Problem with TransferDatabase function

[tad0075]

I have an Access 2007 specifically for report-building using link tables. There's very little data in local tables, except for small tables used to filter reports, populate list boxes, etc. Right now, I'm trying to make a local copy of this db, including the reports, queries, forms, etc. from the link table db, but with local tables for opening reports on computers where the ODBC connection isn't available.

I've set it up to be able to make a copy of the link table db, then use the TransferDatabase to bring in local tables to replace the link tables. At first, the copies were all showing up as firsttable1, secondtable1, etc., which still left my reports to run from the link tables. I modified the code to delete the link table before making the copy. This code is below.

Public Function CopyToLocal()

Dim TableID As Integer
Dim CountTables As Integer
Dim Table As String
Dim AZTECA As String

'Reset counters for loop
TableID = "1"

CountTables = DMax("[ID]", "[Tables to Copy]")

'Start loop
For TableID = 1 To CountTables

'Pull table name based on ID
Table = DLookup("[TableName]", "[Tables To Copy]", "[ID] = " & TableID)
AZTECA = Left(Table, 6)

'Test AZTECA for link table
If AZTECA = "AZTECA" Then
'Copy Link Table
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC location;", acTable, Table, Table, False
Else
'Copy table
DoCmd.TransferDatabase acExport, "Microsoft Access", "old db location", acTable, Table, Table, False
End If

'Recurse loop
Next

End Function

Tables To Copy has a list of all table names referenced in reports, and AZTECA is the first 6 characters of all link tables (flagged so I can pull from ODBC instead of the access link table).



The problem here is that the queries changed some of my field references to "table.field AS expr1" whenever the link table is deleted. I'm trying to make this a 1- or 2-step process so I can repeat it every couple of weeks when a new version of the local db is needed, so rebuilding the queries every week isn't really a feasible option. I could copy in the queries as a last resort, but I would rather not have to worry about that either.

Any suggestions on how to approach this?

 

[tad0075]

Just noticed the delete command before transfer wasn't saved in this version... Here's the current code.

Public Function CopyToLocal()

Dim TableID As Integer
Dim CountTables As Integer
Dim Table As String
Dim AZTECA As String

'Reset counters for loop
TableID = "1"

CountTables = DMax("[ID]", "[Tables to Copy]")

'Start loop
For TableID = 1 To CountTables

'Pull table name based on ID
Table = DLookup("[TableName]", "[Tables To Copy]", "[ID] = " & TableID)
AZTECA = Left(Table, 6)

'Test AZTECA for link table
If AZTECA = "AZTECA" Then
'Copy Link Table
DoCmd.DeleteObject acTable, Table
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC file path;", acTable, Table, Table, False
Else
'Copy table
DoCmd.DeleteObject acTable, Table
DoCmd.TransferDatabase acImport, "Microsoft Access", "original db file path", acTable, Table, Table, False
End If

'Recurse loop
Next

End Function