Pass-thru query ODBC error

Question

Pass-thru query ODBC error

Darrell Burns 121

I have a DSN that connects my Access database to a SQL Server database called "Hotlist". I then created a pass-through query that works fine when I apply the DSN interactively, but throws an ODBC-Connection Failed error when I run the same query with the DSN embedded in a connection string.
Any ideas why?
Thanx in advance!

0 comments

2 answers

Your answer

Answer 1

Darrell Burns 121

Thanks Albert. You're the best.

Funny thing is, I use DSN-less links on all my Access apps but somehow I got in the (bad) habit of using DSNs in Pass-Thru queries. Through trial & error I did find the solution after I posted the question, which was to use my standard connection string to that database without the DSN. So that corroborates your recommendation.

Thanx!

Albert Kallal 5,521 Reputation points

2021-02-15T18:18:17.227+00:00
Good stuff!!

You could also have VBA table-relink code now include PT queries to that code.

So now in code, I don't have to mess with connection strings.

eg:

With CurrentDB.QueryDefs("PTSetInvoices") .SQL = "EXEC PTSetInvoice " & format(date(),"YYYY", "MM","DD" .EXECUTE End With

So code is clean and without connection strings.

Regards,
Albert
Darrell Burns 121 Reputation points

2021-02-15T20:36:07.65+00:00

I'm gonna try that. Thanx for the tip!

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more