changing the sql server for link tables in access

[princeshri]

Hi,

I have an sql server backend with an access front end. access connects to sql using link tables and during development it works fine.

However, the problems appear on installation of the sql database on the production server and clients. Im connecting via odbc and although the odbc connection is to the new database, the access front end still tries to connect to the old server (which is not accessible anymore) and I have to recreate the links for it to work again.

This is not possible since I would like to just do development and copy the database over to the production client (which has an odbc connection to the production sql server)

Ive already tried updating the table.connect to just the odbc system source with the username and password and then table.refreshlink. This refresh link however takes too long for all the link tables and it renders them read-only.

Any and all help appreciated.

Thanks,



Shri

 

[demoman]

you may HAVE a stale DSN?

 

[princeshri]

Hi,

Firstly, how would I rectify the stale DNS situation.

Secondly, the production client did not have this particular dns entry to start off with - I created it and still had to drop and re-create the link tables.

Any thoughts,


Thanks


Shri

 

[allanon]

We have the same setup here. The only way that I have found do do this is to break all of the links and relink BUT if you make sure that you check the 'remember password' check box then you only have to do it one. I wrote a routine to do it to save me time and I do it before we roll out the production version. It does of course require that someone who knows the production password be available at that time to enter it.

Here is the code I use for setting the connections:

Function CreateODBCLinkedTables(strTblName As String, strDataBase As String, _
strDSN As String, strUID As String, strPWD As String)
Dim strConn As String
Dim tbl As TableDef
Dim db As Database

On Error GoTo CreateODBCLinkedTables_Err
Set db = CurrentDb()
strConn = "ODBC;"
strConn = strConn & "DSN=" & strDSN & ";"
strConn = strConn & "APP=Microsoft® Access;"
strConn = strConn & "DATABASE=" & strDataBase & ";"
strConn = strConn & "UID=" & strUID & ";"
strConn = strConn & "PWD=" & strPWD & ";"
strConn = strConn & "QueryLog_On=No;StatsLog_On=No;Regional=Yes"
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
CreateODBCLinkedTables_End:
CreateODBCLinkedTables = 0
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.description, vbCritical, "CMA Connection"
Resume CreateODBCLinkedTables_End
End Function