I am trying to use one Access 97 mdb file to attach to two different SQL servers depending on the situation, one is production the other is a backup server. (The application requires an Access97 file format so I can't change that) I have a DNS Alias for the server name and I put that in the ODBC under Server Name. When I have the Alias changed from Production to Backup I want the Access file to see the data on the backup box.
It appears that MSAccess stores the Server name when the link is made in the MSysObject.Connect field and it does not update that even, if the ODBC is changed. I have one test connection that seems to working because I created it using the ODBC after the Alias was added. The string it puts in the connect field has no reference to the server name or address similar to what is below.
DSN=A_C;Description=A Control;UID=username;APP=Microsoft® Access;WSID=PC22361;DATABASE=DACSP001;Trusted_Connection=Yes
The MSysObject.Connect in a file that did not use the Alias looks like this...
DSN=A_C;Description=A ;APP=Microsoft® Access;WSID=PC27368;DATABASE=DACSP001;Network=DBMSSOCN;Address=pmnt9511.sce.corp.com,1433
So I think I need to recreate a new file using the Alias in the ODBC so the specific address is not in the connection string. What do you think?
Has anyone used an Alias name like I am trying to switch between like databases on two different servers? It seems weird that in the first example above there is not reference to the alias name so maybe this forces it to look it up.
Thanks in advance...
It appears that MSAccess stores the Server name when the link is made in the MSysObject.Connect field and it does not update that even, if the ODBC is changed. I have one test connection that seems to working because I created it using the ODBC after the Alias was added. The string it puts in the connect field has no reference to the server name or address similar to what is below.
DSN=A_C;Description=A Control;UID=username;APP=Microsoft® Access;WSID=PC22361;DATABASE=DACSP001;Trusted_Connection=Yes
The MSysObject.Connect in a file that did not use the Alias looks like this...
DSN=A_C;Description=A ;APP=Microsoft® Access;WSID=PC27368;DATABASE=DACSP001;Network=DBMSSOCN;Address=pmnt9511.sce.corp.com,1433
So I think I need to recreate a new file using the Alias in the ODBC so the specific address is not in the connection string. What do you think?
Has anyone used an Alias name like I am trying to switch between like databases on two different servers? It seems weird that in the first example above there is not reference to the alias name so maybe this forces it to look it up.
Thanks in advance...
