Where are ODBC Machine DSN settings stored in Windows?

Question

In my Access database, I reference the DSN, "mydatasourcename" to connect to an online MySQL database. It is a machine DSN. Somehow through the course of editing my config files, a phantom DSN was created. This DSN is now out of date and I need to update it with the new hostname (after having migrated the MySQL Server). But the config file is nowhere to be found. It does not show up in either 32-bit or 64-bit "ODBC Data Sources" forms. I have searched "mydatasourcename" in the Registry Editor and it is not there either. Mysteriously, when I open an Access linked table referencing "mydatasourcename" it opens a MySQL ODBC Connector dialogue with the old connection information in it. How is it doing this? Where is it getting the connection string information? To answer this question, I am requesting a list of the places the ODBC driver looks for configuration files and how to access them so I can delete the old configuration file. I am using MySQL ODBC Connector 8.0.16. Thank you.

EDIT: The connection string found in my linked table is definitely looking outside of Access for connection info based on the fact that is referencing a DSN. The connection string in one of my linked tables is the following: "ODBC;DSN=mydatasourcename;;TABLE=qrychemigationapplications_materialsrequired1"

Arsenal

Machine data sources are saved in the registry – Pulchritudinous

I searched the registry extensively using Registry Editor and could not find the data source config file. That's the problem. – Arsenal

In this case, check it in Ms access, see duplicate question. – Pulchritudinous

Possible duplicate of Linked table ms access 2010 change connection string – Pulchritudinous

See my edit for why this is not a duplicate – Arsenal

@Pulchritudinous please remove the duplicate message from my post – Arsenal

@ComputerVersteher I'm not sure how Bug92748 relates. – Arsenal

Sorry wrong link! Should be: You are aware of Bug 92947? All connectors after 5.3.10 are affected, – Abbatial

@Abbatial I have been having the #deleted problem so this would be worth checking into. But as to how it relates to this post? – Arsenal

If that happens with with text-length <=1024 it's not the bug. All tables hava a Primary Key and a timestamp field (with Default: Current-Timestamp and OnUpdate: Current-Timestamp) and ODBC-OptionReturn matching Rows instaed of affectedis on? See connector docs-> app specific tipps. – Abbatial

@Abbatial yeah, I have the timestampts and the "affected rows" setting on. And fairly certain text-length less than 1024. And yet I still get #deleted... – Arsenal

PK is int autoincrement and named like an auto recognized PK-Field (e.g. ID), timestamps have the defailt and onupdate values? But you can just test by switching to ODBC/Connector 5.3.10. If issue perisist show affected tables create statement. – Abbatial

@Pulchritudinous please remove the duplicate message from my post – Arsenal

Answer 1

On Windows, ODBC DSN information is stored in the Windows Registry. System DSNs can be found in the registry keys

DSNs for 64-bit drivers: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
DSNs for 32-bit drivers: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

and User DSNs can be found in

HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI

If a DSN does not appear in the ODBC Administrator (odbcad32.exe) it might be because the DSN name is not included in the list of DSNs in the corresponding subkey

...\ODBC.INI\ODBC Data Sources

Paths to odbcad32.exe:

x86:

%windir%\syswow64\odbcad32.exe

x64:

%windir%\system32\odbcad32.exe

Extravasation

I was too late;( but you should add the odbcad32.exe paths, cause they are confusing (system32 for x64 / syswow64 for x86) – Abbatial

So my ODBC software is 32-bit but I did not see ODBC.INI in location: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC. Only the folder ODBCINST.INI was there. Is it possible that the keys would be invisible to me at this location because of lack of privileges? – Arsenal

Do you not get a UAC prompt when you launch regedit.exe ? – Extravasation

I do get the UAC prompt. Actually, it makes sense that nothing is there because it was intended to be a User DSN. So when I go to HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\, I find my my data source...but it shows the updated connection info. When I go into Access and try to click on a linked table which references this DSN, it shows the old (incorrect) connection info...so the datasource is in the correct place, but Access or the ODBC driver is still looking at a phantom data source instead of this one. – Arsenal

I fixed the problem but I still don't know what the issue was or what Access is referencing as DSN. I recreated the machine data source in Access rather than in the ODBC Administrator. This seems to have overwritten the phantom datasource. But I still don't know what the hell it was referencing before and possibly still referencing...so strange – Arsenal

In fact, I just realized Access is still referencing the phantom datasource, its just that the phantom datasource is correct now. I know this because I made a note in the description of the datasource and that note does not show up in the ODBC administrator datasource. Maybe Access created its own machine data source somewhere else? – Arsenal

Access may have written extra information into the .Connect property of the TableDef object for the linked table. It can do that when "Save password" is selected, for example. – Extravasation

This is what I got when I ran one of the linked tables in the Immediate Window: "?CurrentDb.TableDefs(30).Connect" "ODBC;DSN=mydatasourcename;" Still referencing some other data source... – Arsenal

Answer 2

I was able to find the "Machine Data Sources" that Microsoft Access creates in the Windows Registry Here:

• Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources

The "Machine Data Sources" that are in this part of the registry, seem to be accessible only via MS Access. If you look for them instead using the standard Windows ODBC Data Source Administrator "widgets" (32 or 64 bit), you just don't see the "Access Created" Machine Data Sources for some reason. If anyone can tell us why, that would be great!

What's more frustrating, is that at least with the latest Microsoft Access Office 365 version, you can only create NEW Machine Data Sources. You can't delete or edit existing "Machine Data Sources".

That being said, if you avoid creating the "Machine Data Sources" using Microsoft Access itself, and instead use the standard 64 Bit Windows ODBC Data Source Administrator "widget", then for whatever reason Access will see those that you create in this way.

So having the issue that you're describing, just seems to be the result of some weird design that Microsoft has implemented specifically in regard to Microsoft Access ODBC connections, for a reason that escapes me.

Hope this helps!

Succentor

my registry stops at "Office". I have no "ClickToRun" folder. Any idea what is going on there? – Arsenal

No real clue...but don't you just love Microsoft? I guess I shouldn't complain because I don't like Google either. Maybe it's a version issue? I am using Microsoft Office Access Office 365 Edition. How did I find this place in the registry? I just searched my registry for the name of an ODBC connection that I know I made explicitly using Microsoft Access. So maybe you can find yours (if they exist in the registry), by creating an obviously unique SQL ODBC data source using Access, then search the registry for it. It would be much more fun if Microsoft just documented things better. – Succentor

Yeah still couldn't find it... I'm using Microsoft Access for Office 365 MSO. I wonder if that's a different version than yours. Thanks. – Arsenal

Answer 3

Below is an alternative to accessing the Windows Registry:

1. Control Panel
2. Administrative Tools
3. ODBC Data Sources (32-bit or 64-bit)
4. Click the tab for: User DSN, System DSN, or File DSN
5. Click the name of the Data Source
6. Click Configure... if you would like to view or modify the details of the data source

Primary