AKA accessing SQL Server 4.21 via Access 2016
I’ve been pretty dismayed for the longest while that newer versions of Windows bundle newer versions of ODBC that refuse to talk to any version of SQL Server prior to 2000 (at the moment). Of course if I were ‘professional‘ Id be upgraded to the latest version, maybe even running on Linux. But I’m not, and you didn’t come here for how to upgrade/update but rather how can you use tools from the 90’s in the 10’s.
If you try to use the current SQL Server driver, you’ll get this message. Don’t be fooled, SQL Server 7.0 isn’t supported either (probably because of the SUN vs Microsoft Java debacle), it will only work with SQL Server 2000 or later versions.
It’s been this way since Windows 7, and I’ve just given up and gotten used to having to have a VM to access older databases. That is, until today.
While I was dumping data and moving stuff for my blog (something I need to write about another BCP adventure), I wanted to do a simple Access database to make sure it’s looking sane. And I figured I’d jump down to Windows 95, and load up Office 95. Well sure enough there was no proper SQL Server ODBC driver. Popping in the SQL Server 6.5 CD, naturally there is no ODBC drivers for Windows 95, but rather a 16bit driver for Windows 3.1, and 32bit drivers for Windows NT. I installed the NT version, and was still unable to connect until I installed the SQL client which then let me run WINDBVER.EXE so I could configure the appropriate transport DLL (TCP/IP) and then I could connect.
And this got me thinking, is it possible to just take those DLL’s and move them onto Windows 10? Well naturally that won’t work as the driver sqlsrv32.dll is a system protected file, and you can’t overwrite it without a lot of pain. However this got me to thinking that it should be possible to just tell the system it’s a new driver with a different name.
Looking through the registry I notice that HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC is where the 32bit ODBC stuff is stored, so this is where I’ll make my ‘OLD SQL’ driver. I simply used the existing one and renamed the driver/setup DLL’s to sql65-sqlsrv32.dll so that way the 2 DLL’s can co-exist without freaking out.
With that done, the key Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers will need a new SZ string saying the ‘OLD SQL’ driver is installed.
I also copied the following files from the SQL 6.5 client into a subdirectory of SysWOW64 so I could configure the transport:
Now if you are going to use named pipes it’ll just work out of the box. Running WINDBVER you’ll see that it’s set that way by default.
to change it to TCP/IP I coped the files DBMSSOC3.DLL & DBMSSOCN.DLL into the SysWOW64 directory and re-ran WINDBVER, and then selected TCP/IP.
Now we can go ahead and add a SQL DSN. Remember to use the 32-bit ODBC Data Source tool
If everything is setup correctly you should see the ‘OLD SQL’ Database driver. Scrolling to the right you’ll see that it’s version 2.65.02.01 dated 7/7/1997
Now we just need to configured the driver. Be sure to hit the Options button and type in the database name, otherwise it’ll just go to the default database. Since I’m just using the ‘sa’ user that would be the master database, but I want Access to instead use the ‘pubs’ database.
Sadly these old drivers don’t have any test functionality. So there is no real way to know if it is working at this point, however I would imagine it should as we have added a regsitery key for the driver, added it to the available driver list, selected (if needed) a transport DLL, and populated the needed fields. The next thing to do is to try to use it.
Start Access, and select a blank database. Go to the External Data tab, and then choose the New Data Source, and the From Other Sources option. This will bring up the ODBC database wizard.
I’m going with linked, as I want the data to be used from the SQL Server all of the time. You will be then prompted to choose the Data Source
You can select the DSN we setup earlier, and then it’ll prompt you to login.
Again I’m using SA because… why not. And now for the moment of truth, if everything is correct you’ll be presented with the list of tables! This means that it’s working!
You can choose to save the password, and if needed select unique fields, or just leave it as it is. Now you should be able to access your SQL 4.21 database!
And there we have it.
If anyone is brave/crazy enough I extracted the driver here:
sql65odbc-files.7z SQL Server 6.5 should not only be able to access 4.21, and 6.0 but I think it ought to be able to access SQL Server 7.0, although I haven’t tested it out yet.