Fixing Microsoft Office 64-bit ODBC system DSN issues

The problem

When creating a Microsoft Office 64-bit ODBC System DSN, the data source appears in the ODBC Data Source Manager initially, but then mysteriously disappears. This frustrating issue occurs specifically when you have Microsoft Office Click-To-Run installed on your system.

Why this happens

The root cause lies in where Click-To-Run applications store their ODBC data sources. Instead of placing them in the standard Windows registry location that other applications expect, Click-To-Run uses its own isolated registry path.

Standard ODBC registry location:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\

Click-To-Run registry location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\

Note While this isolated location works perfectly for modern Microsoft Office applications, it prevents older Microsoft applications and third-party software from accessing these data sources.

The solution

To resolve this issue, we need to manually move the ODBC data source from the Click-To-Run registry location to the standard Windows ODBC registry location. This process involves exporting, editing, and re-importing registry entries.

Step-by-step instructions

Warning This procedure involves editing the Windows registry. Always create a backup of your registry before making changes, and proceed with caution.

1. Export the registry
   • Press Win + R, type regedit, and press Enter
   • Navigate to the following location:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\

   • Expand this entry to locate your System DSN
   • Right-click on your DSN entry and select "Export"
   • Choose a location and filename for the exported .reg file
2. Edit the Registry file

   Open the exported .reg file in Notepad or your preferred text editor. You'll have content similar to this example (for a DSN named "MDBF"):

   Windows Registry Editor Version 5.00
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\MDBF]
   @=hex(40000):
   "Driver"="C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE16\\ACEODBC.DLL"
   "DBQ"="C:\\l\\faculty.mdb"
   "DriverId"=dword:00000019
   "FIL"="MS Access;"
   "SafeTransactions"=dword:00000000
   "UID"=""
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\MDBF\Engines]
   @=hex(40000):
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\ODBC\ODBC.INI\MDBF\Engines\Jet]
   @=hex(40000):
   "ImplicitCommitSync"=""
   "MaxBufferSize"=dword:00000800
   "PageTimeout"=dword:00000005
   "Threads"=dword:00000003
   "UserCommitSync"="Yes"

3. Modify registry paths

   Use Find & Replace to change all registry paths:

   • Find HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\
   • Replace with HKEY_LOCAL_MACHINE\SOFTWARE\
4. Add ODBC data sources entry

   At the end of the file, add the following section (replace "MDBF" with your DSN name):

   [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
   "MDBF"="Microsoft Access Driver (*.mdb, *.accdb)&Quot;

5. Rename your DSN (optional but recommended)

   To distinguish the new DSN from the original, consider renaming it (for example, change "MDBF" to "RSMDBF" throughout the file).

6. Final registry file

   Your completed .reg file should look like this:

   Windows Registry Editor Version 5.00
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\RSMDBF]
   @=hex(40000):
   "Driver"="C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE16\\ACEODBC.DLL"
   "DBQ"="C:\\l\\faculty.mdb"
   "DriverId"=dword:00000019
   "FIL"="MS Access;"
   "SafeTransactions"=dword:00000000
   "UID"=""
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\RSMDBF\Engines]
   @=hex(40000):
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\RSMDBF\Engines\Jet]
   @=hex(40000):
   "ImplicitCommitSync"=""
   "MaxBufferSize"=dword:00000800
   "PageTimeout"=dword:00000005
   "Threads"=dword:00000003
   "UserCommitSync"="Yes"
   
   [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
   "RSMDBF"="Microsoft Access Driver (*.mdb, *.accdb)"
               

7. Import the registry file
   • Save the edited .reg file
   • In Windows File Explorer, double-click the .reg file
   • Confirm that you want to add the information to the registry
8. Verify the fix
   • Open the 64-bit ODBC Data Source Manager
   • Navigate to the System DSN tab
   • Your new ODBC data source should now be visible and accessible to all applications

Success!

Your ODBC System DSN is now properly registered in the standard Windows location and will be accessible to all applications, not just Microsoft Office Click-To-Run programs.