Where else can MS Access get a DSN?

Question

I have an access 2000 application to manage. The DSN is pointing to the production Postgresql database and I'd like to point it to the one running on my machine for testing.

The problem is, I can't figure out how to change it. I tried running odbcad32.exe, and I did see the DSN and was able to change it to what I wanted (and it did pass the "test connection" test), but it still seems to be connecting to the old production dsn. I confirmed this by checking the connection logs that get dumped to C:\. I searched the registry for the production server's name; I could not find it (and I verified that my changes were getting posted to the registry. I searched the entire project's forms' sources for the server name, I could not find it there either.

Where else could Access be getting the dsn from?

Answer 1

Don't use DSNs. I much prefer DSN-Less connections as it is one less thing for someone to have to configure on each PC and one less thing for the users to screw up.

Using DSN-Less Connections

ODBC DSN-Less Connection Tutorial Part I

HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO

Carl Prothman's Connection String Home Page

Relink ODBC tables from code

Also it's easier to have a setup that uses a development connection string for you and a production connection string for everyone else. For example, assuming your users only get MDEs/ACCDEs, you could use the following function to determine which connection string to use

Public Function tt_IsThisAnMDE()
On Error GoTo tagError

  Dim dbs As Database
  Set dbs = CurrentDb
  Dim strMDE As String
  On Error Resume Next
  strMDE = dbs.Properties("MDE")
  If Err = 0 And strMDE = "T" Then
    tt_IsThisAnMDE = True
  Else
    tt_IsThisAnMDE = False
  End If

    Exit Function

tagError:
    Call LogError(Application.CurrentObjectName, "")
    Exit Function

End Function

Answer 2

Apparently MS Access stores the whole connection string, not just DSN name. Here is what I see opening .mdb file in Notepad:

DSN=SQL-Northwind;APP=Microsoft Office 2003;WSID=DELLNOTEBOOK;DATABASE=Northwind;Network=DBMSSOCN;Address=LOCALHOST;Trusted_Connection=Yes

MS Access comes with Linked Table Manager however it won't update DSN once it's changed (at least not for me).

I guess your only choice is to delete a link and create a new one.