ODBC connection standardization

[Drayton T.]

Good Day!
I develop Access dbs for a large Mortage firm. As a rule, most of the larger
tables are housed on a MS-SQL server. ODBC connections are used to connect.
The Front-end access db could be launched from any number of desktops in our
support area and I am encountering issues with differences in the system dsn
entries in each of the machines. I can successfully execute a P-T query
using the system dsn on my primary machine, but the other users have to
replace the connection string in the property sheet of the P-T query by
pointing to THEIR system dsn entry within the MS ODBC Administrator on their
machine.My question is:

is there an easy way to deploy a standard connection string that will work
on any of these machines?

You guys have yet to fail me on this kind of stuff so Im looking forward to
the usual quick concise responses.


Thanks! D.

 

[Arvin Meyer [MVP]]

You can try a named DSN, similar to the way it's done on IIS, but you can
avoid the problem altogether by using a DSN-less connection:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

or :

http://www.carlprothman.net/Default.aspx?tabid=81

 

[Drayton T.]

Arvin, first of all, thanks for the prompt response.

Your solution appears to concern linked tables. I might be missing
something, but I am referring to Pass-thru queries which do not use the
linked tables, but send the SQL String to the SQL server and returns the
recordset. This is where I run into the aforementioned issue. In the design
view for the pass-thru, in the property sheet for the ODBC connect string I
can use the "..." to launch the ODBC connection builder which then uses the
SYSTEM DSN entry that is local to the machine. What I need is a way to
standardize these SYSTEM DSN entries in several machines or to do away with
them entirely and set the connection string within the PASSTHROUGH queries in
such a way that the local SYSTEM DSN entries are not in play.

Thanks again! D.

 

[Douglas J. Steele]

Pass-through queries are even easier than linked tables.

Look at your ODBC Connect Str property.

It's likely something like:

ODBC;DSN=MyDSN

You simply replace that with an appropriate DSN-less connection string:

ODBC;DRIVER={sql
server};DATABASE=MyDB;SERVER=MyServer;Trusted_Connection=Yes;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Drayton T.]

THAT'S what I'm talking about.

Thanks Doug! Short sweet and simple and exactly what I was looking for...

just so that I have this straight in my mind...

When I use the ODBC admin tool to create a connection string, I am basically
using aliased entrys with the ODBC admin tool cross-referencing to the
database, driver, server and credentials. In the example that you give..I am
explicitly naming each of these. Am I correct?

Thanks again! D.

 

[Douglas J. Steele]

I think that summarizes it accurately.

 

[Drayton T.]

If I want to pass credentials instead of the trusted connection, is it as
easy as replacing - Trusted_Connection=Yes; with this -
UID=******;PWD=******;??

thanks!

 

[Douglas J. Steele]

Yup. For nuances, check Carl Prothman's site at
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
(You must use ODBC drivers with the Connect property in Access. Remember to
put the ODBC; in front of whatever Carl's suggesting)