Lisa Hooper - GUI Computing
With the deployment of Windows 95 advancing within the corporate world, much confusion is caused by the issue of ODBC. The majority of the confusion seems to be based around the concept of which applications can talk to which types of ODBC drivers.
On Windows 95 a 32 bit multithreaded driver will not work when a 16 bit application tries to use it. This is because Windows 95 does not support multiple threads within a 16 bit process space. Although this seems quite reasonable it does not mean a 16 bit app will never be able to make use of a 32 bit ODBC driver. A 32 bit ODBC driver could be multithreaded when running in a 32 bit process space, but single threaded when running in a 16 bit process space, or it could be single threaded only. In the case of most well known ODBC drivers, 16 bit applications can transparently use both 16 and 32 bit drivers.
For a 16 bit application, such as MSAccess 2.0, to make use of 32 bit ODBC drivers (within a 32 bit environment) there are a few 16 bit ODBC components that must be installed:
Although it is quite relieving to know that our old applications can transparently make use of the latest and greatest ODBC drivers, the other side of the story is not quite as accommodating. Contrary to popular belief, 32 bit applications can not use 16 bit ODBC drivers. Unfortunately, any ODBC driver vendor who has no plans to release a 32 bit version in the near future (often the case with Mainframe ODBC drivers) is hampering your move to 32 bit only development.
On a 32 bit operating system which has both 16 and 32 bit drivers installed, two ODBC Administrators will be required to perform ODBC administration tasks. The 32 bit ODBC Administrator (ODBCAD32.EXE) will display only data source names relating to 32 bit drivers. Whereas the 16 bit ODBC Administrator (ODBCADM.EXE) will display both 16 and 32 bit data source names, with '(32 bit)' next to the driver name to distinguish 32 bit data sources. Although the 16 bit ODBC Administrator allows you to see all installed ODBC drivers, you will only be able to set up 16 bit data sources.
INI Files v Registry Entries
When installing a 32 bit driver or data source name, entries are written to the 32 bit registry. These registry entries eliminate the need for ODBC INI files by 32 bit applications. When a 32 bit driver is installed on to the system, entries are written to the registry sub-key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI. When a 32 bit data source name is installed on to the system, entries are written to the registry sub-key HKEY_CURRENT_USER\Software\ODBC\ODBC.INI or the sub-key HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI for system data source names (data sources which can be used by any user of the machine).
The 32 bit ODBC Administrator will add registry entries to the above mentioned sub-keys when a 32 bit driver, or data source name, is installed. The 32 bit ODBC Administrator will also write entries to ODBC.INI and ODBCINST.INI specifically to enable 16 bit applications to access the new data source.
32 bit applications will make full use of these registry entries and will ignore any INI file entries. As 16 bit applications cannot read from these values in the 32 bit registry, they will only use the INI file entries (it would not matter if the registry entries were deleted).
Installing 32 bit Data Sources
On the odd occasion when an installation system (such as WISE) is unavailable, and the installation of a 32 bit ODBC data source is essential to the ingeniousness of your Visual Basic program, the RegisterDatabase method can be considered as an option. But Beware.
I used the following code to install a SQL Server 32 bit data source from VB4 32 bit (I am assuming the SQL Server 32 bit driver has been previously installed on the user's machine):
Dim MyDatabase As Database Dim Attribs As String ' Build keywords string. Attribs = "Description=SQL Server 32 bit driver from 32 bit VB4" & Chr$(13) Attribs = Attribs & "Database=Pubs" & Chr$(13) Attribs = Attribs & "Driver=c:\windows\system\sqlsrv32.dll" & Chr$(13) Attribs = Attribs & "OEMTOANSI=No" & Chr$(13) Attribs = Attribs & "Server=Local" & Chr$(13) Attribs = Attribs & "UseProcForPrepare=Yes" DBEngine.RegisterDatabase "Driver32FromVB432", "Sql Server", True, Attribs
After running this the correct entries appeared in the registry and the following entries appeared in the ODBC .INI file:
This combination of registry and INI entries will allow both 16 and 32 bit applications to make use of this data source.
Next I ran the same code from a VB4 16 bit program:
Dim MyDatabase As Database Dim Attribs As String ' Build keywords string. Attribs = "Description=SQL Server 32 bit driver from 16 bit VB4" & Chr$(13) Attribs = Attribs & "Database=Pubs" & Chr$(13) Attribs = Attribs & "Driver=c:\windows\system\sqlsrv32.dll" & Chr$(13) Attribs = Attribs & "OEMTOANSI=No" & Chr$(13) Attribs = Attribs & "Server=Local" & Chr$(13) Attribs = Attribs & "UseProcForPrepare=Yes" DBEngine.RegisterDatabase "Driver32FromVB416", "Sql Server", True, Attribs
Note that the driver name is sqlsrv32.dll, as it was in the 32 bit example. This code produced no entries in the registry (as 16 bit programs cannot make use of the 32 bit registry, this is to be expected), and the following entries in the ODBC.INI file:
[Driver32FromVB416] Driver=C:\WINDOWS\SYSTEM\sqlsrvr.dll Description=SQL Server 32 bit driver from 16 bit VB4 Server=Local UseProcForPrepare=Yes Database=Pubs OEMTOANSI=No
The RegisterDatabase method has made use of the fact that I have stated that it is a SQL Server data source name that I am registering. 16 bit ODBC only knows sqlsrvr.dll to be the Sql Server driver, and amends my driver= statement accordingly. Hence if I delete the 16 bit SQL Server driver from my machine, this code will cause an error and fail to register the data source.
The point I am making here is that even though 16 bit VB applications can make use of 32 ODBC drivers, they cannot actually install 32 bit data sources. It is advisable in these situations to use a third party installation program, or to directly use ODBC API calls such as SqlConfigDriver and SqlConfigDataSource, to install 32 bit drivers and data source names.