Insert into Passthrough Query

[Guest]

Hi,

I am using below Passthrough query which is workig for SQL:

Set qdf = db.CreateQueryDef("")
qdf.ReturnsRecords = False
qdf.Connect = "ODBC;DRIVER={sql
server};DATABASE=CadcMasterDataBase;SERVER=EL042343;Trusted_Connection=Yes;"
sqltxt = "insert into history.historylog select * from SQLaa"
qdf.SQL = sqltxt
qdf.Execute 'error message "ODBC – call failed. (Error 3146)", "

Question:
In below line I need to use Local access table and wants to update SQL table
from Access table

AA= Access Table
history.historylog = SQL Table
sqltxt = "insert into history.historylog select * from Accessaa"

Which string should I use?

Thanks

AA

 

[Rick Brandt]

Hi,

I am using below Passthrough query which is workig for SQL:

Set qdf = db.CreateQueryDef("")
qdf.ReturnsRecords = False
qdf.Connect = "ODBC;DRIVER={sql
server};DATABASE=CadcMasterDataBase;SERVER=EL042343;Trusted_Connection=Yes;"
sqltxt = "insert into history.historylog select * from SQLaa"
qdf.SQL = sqltxt
qdf.Execute 'error message "ODBC - call failed. (Error 3146)", "

Question:
In below line I need to use Local access table and wants to update
SQL table from Access table

AA= Access Table
history.historylog = SQL Table
sqltxt = "insert into history.historylog select * from Accessaa"

Which string should I use?

Can't do it with a passthrough. By definition a passthrough is a SQL statement
"passed to and executed on the server". Once that SQL arrives at the server the
server has only its local objects to work with. It cannot see nor reference
your local Access table at all.

You can link to the SQL Server table and then use a standard Access query to do
the insert into the link.

 

[Guest]

Thanks for help!

Can't do it with a passthrough. By definition a passthrough is a SQL statement
"passed to and executed on the server". Once that SQL arrives at the server the
server has only its local objects to work with. It cannot see nor reference
your local Access table at all.

You can link to the SQL Server table and then use a standard Access query to do
the insert into the link.

 

[Guest]

Just a thought, but better ask in SQL descussion group.

I never tried it, but you can create a link server from the SQL server to
the Access MDB, and then create a view that list the records from that table
using OPENROWSET.

That way the table from Access will be accesable from the SQL server.