ImplicitCommitSync property not working for Access DSN
My name is Archana CM from Microsoft SQL Developer Support team, we support SQL Connectivity issue along with SSIS.
We had a scenario where one of my customers was facing an issue with saving some of the property values in DSN. Here is the blog which I have written to share my experience along with the solution.
Before I talk about the actual issue and its solution let me talk briefly about the terms and technologies that I would refer to in this blog.
ODBC Administrator
The Microsoft® ODBC Data Source Administrator manages database drivers and data sources. This application is located in the Windows Control Panel under Administrative Tools.
o The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
o The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
DSN
Data sources are the databases or files accessed by a driver and are identified by a data source name (DSN). Use the ODBC Data Source Administrator to add, configure, and delete data sources from your system.
Type of DSN
The types of data sources that can be used are described in the following table.
User |
User DSNs are local to a computer and can be used only by the current user. They are registered in the HKEY_CURRENT_USER registry subtree. |
System |
System DSNs are local to a computer rather than dedicated to a user. The system or any user with privileges can use a data source set up with a system DSN. System DSNs are registered in the HKEY_LOCAL_MACHINE registry subtree. |
File |
File DSNs are file-based sources that can be shared among all users who have the same drivers installed and therefore have access to the database. These data sources need not be dedicated to a user nor be local to a computer. File data source names are not identified by dedicated registry entries; instead, they are identified by a file name with a .dsn extension. |
User and system data sources are collectively known as machine data sources because they are local to a computer.
Above questions are just to give little background of DSN(Data Sources). As said above about the scenario, my customer was facing here are the details
My customer wanted to create the DSN for the Access data base. As default he had used Microsoft Access driver to create the DSN. DSN was created successfully. He also wanted to set the property "ImplicitCommitSync" to true (This property could be found under the advanced tab of the DSN), but though he had set the value to true in ODBC Administrator UI, the value set was not persistent and he was not able to see the result of ImplicitCommitSync functionality.
For those who are not familiar with ImplicitCommitSync, According to the Microsoft Jet Database Engine Programmer's Guide, the UserCommitSync and ImplicitCommitSync keys can have the following values:
"Yes signifies that Microsoft Jet will wait for commits to finish. Any other value means that Microsoft Jet will perform commits asynchronously."
Jet 3.0 interprets the value "yes" as asynchronous and "no" as synchronous for these two keys.
After spending some time researching on the issue , I found out that the cause of this issue was the value set was not reflected in registry and hence value was not persistent.
Whenever we create a DSN irrespective of which data source DSN it is created for, it will always create the registry entry.
For 32 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432node\ODBC\ODBC.INI
For 64 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Once the DSN is created for the Access data base, the below path should be created in registry as below (Assume DSN Name: MS Access Database)
For 32 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432node\ODBC\ODBC.INI\MS Access Database\Engines\Jet
For 64 bit machine
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MS Access Database\Engines\Jet
Under Jet Node we should find all the other properties related to Microsoft Access Database driver like ImplicitCommitSync , MaxBufferSize, PageTimeout, Threads, UserCommitSync etc.
So here in the scenario that my customer was facing was he could find the path till "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MS Access Database" and "Engines\Jet" Keys were missing. We manually created the "Engines\Jet" and then added the string/ Dword respectively for properties ImplicitCommitSync , MaxBufferSize, PageTimeout, Threads, UserCommitSync and added appropriate and default values.
Thus for Microsoft Access data source of the 64 bit DSN,Path in registry should look like below with the values.
Author : Archana(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft