ODBC connection standardization

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Drayton T. - 29 Mar 2007 13:50 GMT

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.

Reply to this Message

Arvin Meyer [MVP] - 29 Mar 2007 14:10 GMT

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

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Good Day!
> I develop Access dbs for a large Mortage firm. As a rule, most of the
[quoted text clipped - 20 lines]
>
> Thanks! D.

Reply to this Message

Drayton T. - 29 Mar 2007 15:12 GMT

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.

> 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:
[quoted text clipped - 28 lines]
> >
> > Thanks! D.

Reply to this Message

Douglas J. Steele - 29 Mar 2007 15:32 GMT

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;

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Arvin, first of all, thanks for the prompt response.
>
[quoted text clipped - 53 lines]
>> >
>> > Thanks! D.

Reply to this Message

Drayton T. - 29 Mar 2007 17:26 GMT

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.

> Pass-through queries are even easier than linked tables.
>
[quoted text clipped - 66 lines]
> >> >
> >> > Thanks! D.

Reply to this Message

Douglas J. Steele - 29 Mar 2007 18:05 GMT

I think that summarizes it accurately.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> THAT'S what I'm talking about.
>
[quoted text clipped - 91 lines]
>> >> >
>> >> > Thanks! D.

Reply to this Message

Drayton T. - 29 Mar 2007 18:14 GMT

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!

> I think that summarizes it accurately.
>
[quoted text clipped - 93 lines]
> >> >> >
> >> >> > Thanks! D.

Reply to this Message

Douglas J. Steele - 29 Mar 2007 18:42 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> If I want to pass credentials instead of the trusted connection, is it as
> easy as replacing - Trusted_Connection=Yes; with this -
[quoted text clipped - 3 lines]
>
>> I think that summarizes it accurately.

Reply to this Message