Backend DB ip address changed

[emilybartholomew]

I have a MySQL database, located on a remote server, with an Access 2000 front end, located on my local desktop. The server has a new ip address from when I first started. I changed the address in the ODBC Administrator for my machine, but every time Access opens it still tries to use the old address. Is there a way to make Access recognize the new address? I'm getting sick of having to type in the new one everytime I open it.

Thanks-
Emily

 

[VicM]

Emily:

I've had a similar problem with working locally on a front end and backend, and then when delivering to client needed to change links to BE on their server.

Now I've only done this with an Access BE, so not quite sure if my solution will work under ODBC. But it's worth a shot.

Access stores the Connect property in each table, it's just a matter of resetting those links in code. If you use the Linked Table Manager, Access tends to use the mapped path and not the absolute path. When working over a network, this doesn't work since different users may have servers/drives mapped with different letters.

So the code I've used is the following:

Sub ChngLink()
Dim lnk As String, tbl As DAO.TableDef

lnk = ";DATABASE=Here is where you need to have the fully qualified path to the server/backend DB"

For Each tbl In CurrentDb.TableDefs

'Msys is the prefix used by Microsoft for system tables. Never change Connect string for system tables.

If InStr(1, tbl.Name, "msys&quot = 0 Then
tbl.Properties("Connect&quot.Value = lnk
tbl.RefreshLink
End If
Next
End Sub

But before you run off and do this, check out how the Connect string's syntax is by getting to the Immediate window and typing the following:

?currentdb.TableDef("name of one of the linked tables&quot.Properties("Connect&quot.Value

The string you get back is the correct syntax, you just need to use the new server address/path/BE database name.

Hope this helps,

Vic

 

[emilybartholomew]

Vic-

I'm actually using ADODB, not DAO, so this code didn't work out for me. However, I did eventually find a solution. I went to Linked Table Manager and checked the "always prompt for new location" check box. Then I updated all the tables. So far this seems to be working. Thanks for your help.

-emily