Copy a ODBC-linked SQL-Table to an Access table with VBA

Question

I have some ODBC-linked SQL-Server tables in my Access DB, which are the production environment. For testing I want to copy all the data from the SQL-Server into structure-identical Access tables so that I have an identical set of tables in a dev or test-environment. To make it difficult: All of these tables have autoincrement IDs and I want the copies to have the same values and of course the copied ID field also as autoincrement long.

So, a set of these tables:
- dbo_tbl_Abcd
- dbo_tbl_Efgh etc.

should be copied to:
- Dev_Abcd
- Dev_Efgh etc.

or to:
- Test_Abcd
- Test_Efgh etc.

When I do a manual copy and paste for each single table this will work without problems. A dialog "Paste Table As" appears where you have the options:

Linked Table
Structure Only
Structure and Data
Append Data to Existing Table

When you set the name correctly and choose Structure and Data, you will have a proper copy as Access table with the same values in the Auto-ID field. I just want to do this by code and for all ODBC-Tables at once (in a loop). When Access provides this manual copying, there must be a way to do this by code.

I have already tried this:

DoCmd.CopyObject , "Dev_Abcd", acTable, "dbo_tbl_Abcd"

but this only will create more ODBC-links to the same SQL-Server tables. I also tried this:

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "dbo_tbl_Abcd", "Dev_Abcd"

This led to the following error:
The Microsoft Access database engine could not find the object . Make sure the object exists and that you spell its name and the path name correctly. (Error 3011)

I experimented a lot with DoCmd.TransferDatabase, but cound't find a working setting.

I did not test any "SELECT INTO"-Statements because of the autoincrement field.

Answer 1

What you are asking can be done like

CurrentDb.Execute "select * into localTable from dbo_serverTable" , dbFailOnError

And to do this to all the tables use this sub

   Sub importSrverTables()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs       
            If Left(LCase(tdf.Name), 4) = "dbo_" Then
                'CurrentDb.Execute "select * into localTable from  dbo_serverTable", dbFailOnError
                db.Execute "select * into " & Mid(tdf.Name, 5) & " from  " & tdf.Name, dbFailOnError
       ' the next if is to make the loop wait until the transfer finish.
        If db.RecordsAffected > 0 Then
        ' do nothing
        End If
            End If         
    Next
    Set tdf = Nothing
    Set db = Nothing
   End Sub