Pass-Through Queries in MS ACCESS

[tyleri]

How do I set up pass-through queries in MS ACCESS?

I am having trouble getting anything to work with my pass-through queries... the SQL is right I think.. but I think my Connection String (starts ODBC is wrong.

I know the names of the servers and databases within the servers, i just keep getting that error message "odbc call failed".

 

[kathryn]

OK, a few questions.

Where is the query? In an SQL database? Have you set up a Data Source using Control Panel for the database?

Let me know. I'm sure we can get it to work.

Kathryn

 

[tyleri]

OK - my queries are in Access. The tables are in the SQL Server - I don't have access to the SQL server - they gave me read-only access for viewing purposes only - but what we are interested in takes almost 2 minutes to filter out with regular jet-engine queries.

Does this help?

 

[kathryn]

Yes, it does. I don't know if this is going to work, but let's give it a shot.

Do you know how to set up a Data Source in Control Panel? Have you done that yet for the SQL database?

Kathryn

 

[tyleri]

Yeah - before we linked to the tables, so we did set up odbc datasources, I have like 5 of them loaded cause we are pulling from 5 different databases on the same server.

I just found something out that i was not aware of - I think my SQL is wrong.

here is what I have - can you tell me if you think it is causing the problem?

SELECT DISTINCT dbo.DPS_USER.id, dbo.DPS_USER.login_id, dbo.DPS_USER.user_last_nm, dbo.DPS_USER.user_frst_nm, dbo.DPS_USER.mi_tx, dbo.DPS_USER.user_addr_1_tx, dbo.DPS_USER.user_addr_2_tx, dbo.DPS_USER.user_addr_3_tx, FROM dbo.Business_Services_Provider_Attributes INNER JOIN dbo.DPS_USER ON dbo.Business_Services_Provider_Attributes.login_id = dbo_DPS_USER.login_id
WHERE (((dbo.Business_Services_Provider_Attributes.Service_type) Like "trust&quot);


Thanks!

 

[tyleri]

I figured it out!!! thanks for the help I really appreciate it!

FYI it was my SQL - I was using access-based SQL which is not valid with SQL Servers. Whoops!