[AccessD] Friday's Puzzler - Table Def "Connection" Info (Database Name)

Ok, going to throw my two cents in here.  First, and idea of why you
aren't getting database info with the SQL server account.

With an NT account, you have access to the entire SQL database, and you
can assign/remove permissions.  However, SQL accounts can be set to a
specific schema/database in SQL Server, so it may not be listing DB info
because it's only connecting to one database.  Not sure if that makes
sense.

The other thing I wanted to toss out there, is that typically I create a
class (used to be a plain module) for a connection object.  So instead
of having individual processes creating their connection, with their own
code, all connection goes through the same code contained within the
connection class.  There are many advantages to this.  First, just
manually switching the data source is changing one line of code.  You
can also setup dual environments, like what you are talking about, and
allow the class to handle which database it is using, and also allow any
reference to which one you are using.  Another use is keeping
connections open.  One large system I have connects to the database
quite frequently.  Instead of opening and closing the connection over
and over, in separate processes throughout the application, the class
opens the connection, and then maintains the connection for 15
seconds...if no other process needs the connection, the connection is
closed...and then reopened when it is needed.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, May 28, 2010 10:54 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Friday's Puzzler - Table Def "Connection" Info
(Database Name)

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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.