Table Def “Connection†info (Database Name)

[Brad]

We have just started to explore the information contained in Access Table Def
“.connectâ€

We would specifically like to see the Database Name because we are building
a system that will have a TEST database and a PROD database and we plan to
dynamically switch between the two.

For a test, we linked to one SQL Server table via a User-ID (Windows
Authentication)

We then linked to a second SQL Server table via a SQL Server “Login†(SQL
Server Authentication). We want to use this approach for the long term.

Here is the part that we don’t understand.

When we look at the table def connect info for the first table, we can see
the database name.

When we look at the table def connect info for the second table (SQL Server
Authentication) we CANNOT see the database name. (This is the piece of data
that we really want to see).

Any ideas on how we can fix this?

Thanks,
Brad

 

[Douglas J. Steele]

I suspect the Connect string in the second case is pointing to a DSN, and
the database information is contained as part of that DSN.

 

[Brad]

Doug,

Thanks for the help.

Yes, you are right (DSN is in the connect string)

We have things working nicely to switch between TEST and PROD. It was just
a bit confusing to not see the database name in the .connect info.

Between SQL Server security, ODBC settings, and the table Def info, things
can get a bit complicated.

Thanks again for your assistance. Things are starting to make more sense now.

Brad

 

[david]

Access pools SQL Server ODBC connections, so
if you try to use one connection with Windows Authentication
and one connection with SQL Server Authentication,
both connections will be pooled, and could cause you
problems.

I've not tried this personally, but from memory, that's
not supposed to work.

(david)

 

[Brad]

David,

Thanks for the tip. I did not know this.

Even on a long Memorial Day Weekend, a person can learn something new.





Brad