Trying to insert data from a table in another database

[Baesucks]

I'm trying to insert data into a table in my current database from a similar table in another database. From inside my MS-Access ADP file I'm trying to do something like...

strSQL = "INSERT INTO [myTable] SELECT [theOtherTable].* FROM [TheOtherDatabase].[theOtherTable]"

DoCmd.RunSQL (strSQL)

Obviously, I will need to create a connection to the other database, but then how do I specify that in the SQL statement?

 

[Baesucks]

Never Mind. I found out it can't really be done. Had to create an MDB file and then link the tables from both databases to append records from one to the other.

 

[PWise]

strSQL = "INSERT INTO [myTable] SELECT [theOtherTable].* FROM [theOtherTable] IN 'Drive\path\myotherdb.mdb'"

or
sqlstr ="INSERT INTO [myTable] SELECT [theOtherTable].* FROM [theOtherTable] 
IN '' [ODBC;DRIVER=SQL Server;SERVER=Servername;DATABASE=dbname]"

 

[lameid]

You could create a Linked Server in SQL Server in order to run a query across servers... Most likely what you are doing is a one off and is perhaps more easily accomplished as you did linking both tables.

Any way the FQN for a table in another SQL server once you have linked it looks like...

[Server/Instance_Name].Database_Name.Schema_Name.Table_Name