ADO ACCESS TO SQL

Question

ADO ACCESS TO SQL

Matt Miskinnis

SSC-Addicted

Points: 461
More actions
February 10, 2004 at 11:27 am

#65030

I want to create a passthru query in access that uses tables in access and sql server. I have done this using a linked table in access but I need to do this via ADO and not using a linked table. I'm not sure how the syntax would work in the query for the tables in access and sql server

Thanks,

-Matt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

cwaddle SSC Veteran Points: 289 More actions · Answer 1

I think if you created a linked server in SQL server (I have just learned that this is possible but I havn't done it) - pointing back to your access database. Then create a stored procedure in sql that did what you wanted then your could execute a pass through query form access that would mix the access and sql server table. I have never done this but I think it would work.

robinhc SSCommitted Points: 1617 More actions · Answer 2

Why so you need to use ADO. The pass-through query is sent to SQL Server has is. Just get the SQL Syntax working in Query Analyzer and then paste into your pass-through query. Go to the properties of the query and tell it what DSN to use and whether the pass-through query will return results. I do not think you even need the link table. Then you link the results of the pass-though query to the access table in second query. I haven't tested this, but it should work

MikeAtTheMill Old Hand Points: 398 More actions · Answer 3

I agree. I believe the right approach is to create a linked server (on sql server) that points back to the Access database. Then in your ADO code - you obviously connect to the sql server and pass a command object or rs object with appropriate SQL. You can develop the SQL in Query Analyzer (if you have the sql server client tools). Given that the ADO connection is to the sql server - this is not really a pass thru query. The sql dialect MUST be T-SQL.

Rock on.

Mike at the Mill

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 4

Thanks for the feedback.

I believe the linkserver is the only way to include the access tables in a query via ADO. Unfortunately the database that is being used cant be guaranteed that it wont be moved around in different folders and the linkserver requires the path to the database.

I was able to pull a recordset from sql server then loop through the recordset and filter it according to other data in access. It just would have been a lot easier if I could have included the access tables at the time of querying SQL Server the first time.