checking current ODBC Connection

[AlexTeslin]

Hi,

I am trying to connect via ADO to either local SQL instance or remote depending on what ODBC connection my Access is connected to. So for example if my Access application is connected to local instance through ODBC driver, then I would make ADO connection to a Local instance. If it's remote on ODBC then I would use remote connection string for ADO as well.

I am struggling with getting information on current ODBC connection. Can anyone suggest please on how can I check this?

Thank you

 

[boblarson]

Check out the CONNECT field in MSysObjects (a system table).

 

[AlexTeslin]

Thank you Bob,

I did that, but out of say 700 rows only 10 will have a result for Connect field. The rest are Nulls. And those 10 are dated few months back.

 

[boblarson]

Thank you Bob,

I did that, but out of say 700 rows only 10 will have a result for Connect field. The rest are Nulls. And those 10 are dated few months back.

And those 10 would be the 10 tables which are linked using ODBC. The other rows have to do with other things. The table stores data about tables, queries, forms, reports, modules, macros, etc as well as information about internal tables and linked tables.

 

[AlexTeslin]

I see, I was confused because the next few columns are create_date and modified_date and the dates corresponding to the Column field with values were not recent. Does this means that every time I open my Access Column field displays information for the current connection? I suppose I was confused with modified_date and something like current_access_connection_date.

Thanks again

 

[AlexTeslin]

I just can't understand. I have created 2 System Data Sources. dbTest and dbLive. One should be referencing to my local SQL instance and dbLive to remote live SQL instance.

What I found is that when I change connection through ODBC Data Source Administrator to dbTest, it doesn't points to my local SQL instance. I have to change dbLive in order to point both to local or remote SQL instances. In other words, when I configure dbTest and whatever I choose in "Which SQL Server do you want to connect to?" local or live SQL instance has no effect on my actual Access application. But if configure dbLive, then it works fine.

I need to be able to configure and use dbTest Data Source so that when I check for the current connection in 'msysobjects' system table I can differentiate which connection I am on. Because no matter to which SQL instance Access points to it shows as dbLive in Connect field of system table.

Any help please

 

[boblarson]

I just can't understand. I have created 2 System Data Sources. dbTest and dbLive. One should be referencing to my local SQL instance and dbLive to remote live SQL instance.

What I found is that when I change connection through ODBC Data Source Administrator to dbTest, it doesn't points to my local SQL instance. I have to change dbLive in order to point both to local or remote SQL instances. In other words, when I configure dbTest and whatever I choose in "Which SQL Server do you want to connect to?" local or live SQL instance has no effect on my actual Access application. But if configure dbLive, then it works fine.

I need to be able to configure and use dbTest Data Source so that when I check for the current connection in 'msysobjects' system table I can differentiate which connection I am on. Because no matter to which SQL instance Access points to it shows as dbLive in Connect field of system table.

Any help please

You need to relink to the one you want using Linked Table Manager or by using code.

 

[AlexTeslin]

When I relink the tables Connect column of the 'msysobjects' system table still shows blank on the row that has been generated.

 

[boblarson]

When I relink the tables Connect column of the 'msysobjects' system table still shows blank on the row that has been generated.

What row? And why would it generate a new one for relinking? It should just change the existing one.

 

[AlexTeslin]

Ok, these are the steps I am going through.

Checking first the system table. Say there is a row for today. I am connected to remote SQL Server.
Then closing Access, going to ODBC connection administrator, changing manually a connection to reference now to local SQL Server.
Then open Access again, and when I check the systable, there is a new row, but nothing in Connect column.

In fact, the Connect column shows the values for different machine that has been used long time ago. I just don't understand, if systable creates a new row after changing ODBC connection described above, doesn't then update atleast those rows where there are values in Column column.

What I am after is to be able to somehow check if my Access connected to remote or local SQL Server instance. And if it's remote then I will use remote string for ADO connection, and if it's local then local connection string.

 

[boblarson]

I don't think we're connecting here for some reason. You keep talking about going to the ODBC Manager and changing the database the DSN is pointing to and that is NOT the place you should be doing this.

You can set up two DSN's and then can switch between them but you have to use the LINKED TABLE MANAGER within Access to make the change (or you have to do it with code). Even if you had changed the source in the DSN in the Windows ODBC Manager, it will not be effective until you refresh the links either by using Linked Table Manager or by using code to refresh the links.

 

[AlexTeslin]

I see, is this means that if I change the source in the DSN in the Windows ODBC Manager from 1 to 2, then I have to refresh the linked tables as well.

If this is the case then it explains why I was still pointing to the same SQL instance after changing from one DSN name to another. Because so far the way I was able to link my Access tables to either local or remote SQL instances was through the same DSN configuration in Windows Manager.

Can I ask what would be the most efficient way to my problem?
To write say a Macro to refresh linked tables at startup?

Or to somehow (which I don't know yet how) retrieve the name of the SQL instance say ODBC driver? Which I have struggled to do it.

Thanks again

 

[boblarson]

Perhaps this will help:

http://support.microsoft.com/kb/208295

it says for Access 2000 but it should work for any version.

 

[AlexTeslin]

Thank you Bob,

I will try that first.