In Access, how to merge quickly two linked tables (originally views from SQL Server) in a local Access table

Question

Closed. This question needs details or clarity. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65877855/in-access-how-to-merge-quickly-two-linked-tables-originally-views-from-sql-ser

Answer 1

Well, then simply link all 12 tables.

Then assuming you have a local table of the same structure? then you can run a append query like this:

Dim strFromTable  As String
Dim strToTable    As String
Dim i             As Integer
Dim strSQL        As String

strFromTable = "dbo_tblData"   ' our 1 - 12 tables
strToTable = "tblLocalTable"

For i = 1 To 12
   strSQL = "INSERT INTO " & strToTable & _
           " SELECT * FROM " & strFromTable & i
   Debug.Print "appending table " & strFromTable
   CurrentDb.Execute strSQL, dbFailOnError
Next i

Now above assumes that the 12 linked tables are named dbo_tblData1, then dbo_tblData2 etc.

If you don't have such a naming convention, then you could change above code to use a different table name that you supply.

You also could consider writing code to create the "linked" table on the fly, but its probably just as easy to link the 12 tables one time, and then above will work.

the above of course pulls all 12 tables into ONE table in Access. You could certainly modify the above to pull each table "from" into a local table.

eg this:

   strSQL = "INSERT INTO " & strToTable & i & _
           " SELECT * FROM " & strFromTable & i