Access 2010 and Server Name on DSN in ODBC Data Source Administer

[adefries]

First Thread:
[[I have a User DSN set up through the ODBC Data Source Administrator, which points to a default database on SQL server. All my tables in Access are linked to this database, and I use the same Access application whether I'm running against Dev or Stage or Prod, by simply changing the SERVER reference in my User DSN. My question is this... is there a way for Access to know which environment my ODBC connection is pointing to, and display the server name on reports?]]

Second Thread:
[[I have a User DSN set up through the ODBC Data Source Administrator, which points to a default database on SQL server. All my tables in Access are linked to this database, and I use the same Access application whether I'm running against Dev or Stage or Prod, by simply changing the SERVER reference in my User DSN. My question is this... is there a way for Access to know which environment my ODBC connection is pointing to, and display the server name on reports?]]

[jimatqsi]

The DSN simply serves as a reference point when you link tables. The name of the DSN is not stored in the connection string; the server, database and other connection properties are stored. So, yes, you can pull the server name from each table's connection string.

You'll use something like

dim tbl as table
set tbl = CurrentDB.Tabledefs(tablename)
msgbox tbl.Connect ' show connect string
 

Jim

[Seth Schrock]

Actually the DSN name is stored, but not the server name.

From looking around the Internet, there doesn't seem to be an easy way to read the DSN information. What you could do is create a DSN-less connection and just store the server name that you have chosen in a tempvar to be able to pull later and put in the report. DSN-Less Connection

[zmbd]

adefries:
Please do not double post your question.

This is against the site policy.

Please keep in mind that moderators, experts, and others are un-paid volunteers that attempt to provide some help and guidance in-between our obligations to work and family.

Please be patient – especially on holidays and busy posting days.

In the future, both threads may simply be deleted.

[jimatqsi]

Seth, tell me about that. I'm using a Access 2007 front-end now, with ODBC connection to a SQL back-end. If I get the connection string I see ODBC;DriverSQL Server;SERVER=servername;UID= ...etc..."

Under what conditions do you get the DSN?

Jim

[Seth Schrock]

This is what I get in Access 2010 connecting to SQL server through DSN (string broken up for readability):

?currentdb.TableDefs("emp_tblRoles").Connect
ODBC;DSN=EmployeeDatabase;
   Description=Employee Database;
   Trusted_Connection=Yes;
   APP=Microsoft Office 2010;
   DATABASE=EmployeeDatabase;
   Network=DBMSSOCN

This is also what I found everyone else online to report finding.

I did find a link for how to create the DSN through VBA (involves working directly with the registry), but I haven't had time to reverse engineer it to be able to pull the registry values.

[adefries]

I am finding the same thing as Seth. I do not see the server name on the connection string, only the DSN and database name. I don't know how, or if, it's possible for Access to 'see' the server that the DSN is currently pointed to.

[jimatqsi]

Do you get the same result if you are not using a Trusted Connection? I'm not able to test a Trusted Connection at the moment.

Jim

[jimatqsi]

It might be a function of network security policies. I can't think of any other reason for it.

[zmbd]

If you have connected/linked tables try the following:
Start a new query
Do not add tables
Switch to SQL view
C&P

SELECT MSysObjects.Connect
   , MSysObjects.Database
   , MSysObjects.ForeignName
   , MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=4 
        Or (MSysObjects.Type)=6));
 

In my case, SERVER=10.255.255.05 because that is how I set the DSN up... via IP address. 01 is our test, 05 is our production, 10 is our shadow server for 05. So, I split on ";" push the array for "server=" split that cell on "." look at the last cell of the array for the server.

My curiosity is why do you need the Server name?

You can of course do an API call to the ODBC DLL and parse the objects therein; however, I'm not that versed in the API calls.

[jimatqsi]

I use file DSNs when I set them up through the Administrator. I think the result is the same when I set them up through the Access ODBC wizard. I wonder if you see any difference using the wizard as opposed to the Windows Administrator.

Jim

[Seth Schrock]

I use System DSNs and that may be the difference.

[adefries]

I will try some of the things suggested.

[adefries]

If I create a new DSN from within Access, I see the server name on the connection string. Thanks for everyone's input.

[adefries]

Well I'm still working this problem.... When I created the DSN from within Access, it created a FILE DSN. The File DSN does show the server name in the connection string. However, the File DSN doesn't refresh the link if the server is subsequently changed. With the User DSN, as the Server is changed through the ODBC Data Source Administrator, it automatically points all the tables in Access to the new server. No so with a file DSN. This is all kind of new to me, so I'm confused. The User DSN works in every way, except I can't see the server name on the connection string. I need the reports in access to be able to dynamically refresh the server name whenever it's changed. It's a requirement that the reports indicate whether the data came from the DEV server, or the STAGE server or the Production Server. We're constantly switching between servers, so I don't think the File DSN will work for me. Someone recommended an API call to the ODBC DLL, but I am not familiar with API calls and don't know how to do that. Any more suggestions?

[Seth Schrock]

My recommendation would be to use a DSN-less connection. I have only used it once, but I think that in your case of having to change servers often, it would be best. The other option would be to create multiple User or System DSNs; one for each server. Put the server name in the description field. When you want to change servers, just change which DSN you are attached to. When you need to know which one you are attached to, then you can just get the description from the connection string. **NOTE** This is an untried idea.

If you go the DSN-less connection way, the server name is stored in the connection string. You could even create a table to store the information for each string and then have a combo box so that you can select which one to you want to connect to and then use the After_Update event to trigger the server change. The link on how to set this up is in post #4.