Pass-thru query ODBC error

Question

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!

Answer 1

The real easy way?
Have access automatic createa DSN-LESS connection for you. That way, you can deploy the application to any workstation etc., and ZERO setup of the odbc (including pass-though) queries will occur!

The REALLY super cool way to do this - without efforts on your part?

Create the connection for the PT query but ALWAYS use a FILE dsn! - that's it!!!

And this trick ALSO works for when linking tables.

If you use a FILE DSN when you link the table (or the connection for the PT query)?

then access automatic converts that to DSN-LESS for you!!!

Now, you could even delete the DSN, or as noted, deploy the access application to a different computer.

So with a FILE dsn?

Access ONLY uses it ONE time during the table link (or connection setup). After that, the connection string is "self contained" and does NOT use or require or need the original DSN used (in fact it is ignore - and does not even have to exist anymore!!!).

Due to windows "security"?

You MIGHT have to launch Access as an administrator to get this to work in some cases.

Just tap windows key, type in Access (it searches) and you get this:

Now when you link tables, or create the connection for the PT query?

Do this:

So now when the standard connection builder launches? - this one?

So JUST ALWAYS but ALWAYS use a FILE dsn as per above.

As a result, then all your connections become DSN-less AUTO matic for you.

And if for some reason that you want to change/set/create/have/do a connection string at run time (which is ugly and makes for lots of messy VBA?).

Then create a connection using the above, and then steal that connection string format for use in your code or your "on the fly" created connection string.

It will be of this format: Navtive 17 driver:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=.\SQLEXPRESS;DATABASE=Invoicing;UID=Albert;PWD=AlbertPassWord;WSID=Invoice;
Network=DBMSSOCN;MultiSubnetFailover=Yes;ConnectRetryCount=15;ConnectRetryInterval=5;

Or legacy ODBC - built in driver (pre-installed on all workstations). You can use this format:

ODBC;DRIVER=SQL Server;SERVER=DEVELOPER1\SQLEXPRESS;
DATABASE=Invoicing;UID=Albert;PWD=AlbertPass;APP=Invoice;Network=DBMSSOCN

Notice how there is NO DSN in these connection strings. So be it code (VBA) or letting access create the connection for you?

They can and should be DSN-less. You save un-told headaches using this approach.

Using DSN's is difficult, since they can be pre-loaded, and file location and rights to the DSN also crop up many a time.

So, just go DSN-less.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

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!