Ccnt 2 = "Server=MyServer;Database=MyDatabase;Uid=MyId;Pwd=MyPassword;"
Ccnt = Ccnt 1 & Ccnt 2
CurrentDb.Execute "insert into DestTble select * from [" & Cnct & "].SrceTble"
End Sub
This creates a connection string as before, except that it uses ODBC instead of OLEDB
and utilizes a different provider and driver parameters. It then uses a standard SQL Insert
query to transfer the records from the external database table (SrceTble) to the Access
destination table.
This assumes that the structure of the destination table and the source table are the same.
As shown previously, other connection strings can be used to link to another Access
database or to use an existing DSN.
Chapter 19: Working with External Databases 251