Access: changing the IP of the SQL backend?

[jakoz]

Sorry if this has been repeated... Ive looked and didnt find anything, and Im tearing my hair out at what should be such a simple problem...

We are running Access with an SQL backend. The Access database apparently starts with a dsn file in the same directory. All I need to do is change the IP that points to the server.

This should be so simple... bit Ive tried changing the file, adding and removing system DSNs, searching all files and the registry, etc. Its driving me insane... one small stinking change of IP and I cant find where to do it. The connection also doesnt turn up in the ODBC administrator.

Thanks in advance for any help.

 

[jfischer]

Assuming that your Access database is using a File DSN located in the database directory, you can change the IP address in a couple of ways. The quick and dirty way is to use Notepad. Use your Windows Explorer to navigate to the database directory and find the File DSN (.dsn extension) used by your database. Open it with Notepad and look for the line:

SERVER=nnn.nnn.nnn.nnn

or

SERVER=nnn.nnn.nnn.nnn\INSTANCE_NAME

Change the old IP address specified by "nnn.nnn.nnn.nnn" to the new IP address, and save the file.

The other way is to use the ODBC Data Source Administrator. Start it from Control Panel (possibly under Administrative Tools depending on your Windows version), and click the File DSN tab. Change the Look In dropdown list to browse to your Access database folder. You should see the File DSN listed. Select it and click the Configure button. At the bottom of the first configuration panel is the Server name. It should show:

nnn.nnn.nnn.nnn

or

nnn.nnn.nnn.nnn\INSTANCE_NAME

Again, "nnn.nnn.nnn.nnn" represents the IP address of the SQL Server. Change the IP address as needed, and finish out the configuration wizard. Do a Test Data Source to make sure you have a good connection.

I don't know if your database has automated table relinking programmed into it. If not, you should manually refresh all your table links in the database. In Access, use Tools, Database Utilities, Linked Table Manager to accomplish that task.

 

[jakoz]

Thanks jfischer

I did try this, and the DSN file had been changed... and if I edit that file using the ODBC administrator, the IP I want to change the connection to is there...

But whats driving me nuts is that its stgill connecting to the old IP. If only I could find out where it stores the info, I could change it there, since nothing else seems to work.

 

[psemianonymous]

I don't know this for sure, but try checking the .Connect property of your tables ( CurrentDB.Tabledefs("TABLENAME&quot.Connect ) and see if that has the info you're looking for.

It also can't hurt to add new tables on top of your existing tables (i.e. YOURTABLENAME1 and YOURTABLENAME2) and ensure you added the SQL server correctly...

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[jfischer]

Did you relink your tables after making the IP address change? The links are stored in each table's definition. For example:

Dim tdf As TableDef

Set tdf = CurrentDb.TableDefs("tblMyTable")
MsgBox "tblMyTable.Connect = " & tdf.Connect
Set tdf = Nothing

You should see a "DSN=" parameter in the connection string for the DSN that you are using. You may also see a "SERVER=" parameter that overrides the DSN value and carries the old IP address. Relinking your tables will refresh the table definitions.

 

[jakoz]

Thanks jfischer... that last reply of yours finally set me on the right path.

In the end I got so frustrated with it, I learnt enough VB to hack out a DSN-less solution, without the need to relink tables or anything else apart from running the code. Not pretty code (didnt bother with error checking or messages), but it works.

Heres the function if anyone else needs it:


Function changeip()

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=xxx.xxx.xxx.xxx" _
& ";DATABASE=databasename" _
& ";UID=userid" _
& ";PWD=password"

' Refresh Access Linked Tables
For Each tdf In CurrentDb.TableDefs
' Only attempt to refresh link on tables that already
' have a connect string (linked tables only)
If Len(tdf.Connect) > 0 Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next

MsgBox "Tables updated."

End Function