Insert into Passthrough Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Prince said:
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?
Click to expand...

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.
 
Thanks for help!

Rick Brandt said:
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.
Click to expand...
 
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.
 
Back
Top