data base unlimited

data base unlimited

Access 2

Access

Access 2 to SQL7 or 2000

Have you tried it yourself? Have you had a frustrating time?

Most of this is hard to find, even at Microsoft PSS -- yet the answer is quite simple. To use SQL Server databases from Access 2 you need to do the following:

Change the SQLSRVR.DLL

When Access 2 is installed to include ODBC drivers, it will put a SQLSRVR.DLL in the \winnt\system directory on NT4. This is a 16-bit DLL which was last upgraded for SQL Server 6.5 SP3.

The default version installed is version 1.02.3231. The latest version is version 2.56.0252 (do NOT trust the dates!).

Overwrite the old version with this new version which available on the SQL7 disks under the \SQL65\SP4 directory.

Change the ODBC.DLL

Access 2 installs version 1.05.0916. The last 16 bit version was 2.10.2401 (available in Visual Studio disk2, \sql\clients\). You need to install this version which allows access to 32-bit defined ODBC sources (DSN's -- the only thing Access 2 understands when attaching tables.)

For a fuller understanding of 16-bit applications with 32-bit drivers see "Using 16-bit Applications with 32-bit drivers" in the Microsoft MSDN Library.

If you install ODBC.DLL and SQLSRVR.DLL in the stated versions, you will be able to attach SQL7 tables for read and update. However:

SQL7 (as compared with SQL 6.5) functionality is not supported

1. user defined types give problems
2. nvarchar is not supported
3. some SQL7 schema (special views) are not supported
4. bit fields translate as -1/0, not 0/1
5. varchar fields > 255 can be read but not written
6. table and field names must be carefully chosen
7. fields must have primary keys to be updateable.

If you have the older DLL's you will have to edit manually the ODBC.INI file

You cannot edit the ODBC.INI file through the ODBC32 control panel applet, so you have to do it by hand. You will find a section called [ODBC 32 bit Data Sources]. Disregard it. We are back in the world of 16-bit. You will however find a section like this:

ODBC Data Sources] - 1
MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb) - 2

[MS Access 2.0 Databases] - 3
Driver=E:\WINNT\SYSTEM\ODBCJT16.DLL
DefaultDir=E:\ACCESS2
JetIniPath=MSACC20.INI
UID=Admin

Notice that the syntax of section 1 includes no mention of SQL Server. Line 2 contains the generic description; line 3 gives the detail for the generic description (description and header must match exactly -- case sensitive too)

To add a SQL or MSDE data source you need to amend this area:

[ODBC Data Sources]
MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb)
SQL7DBUS2=SQL7 via ODBC - 1


[MS Access 2.0 Databases] Driver=E:\WINNT\SYSTEM\ODBCJT16.DLL
DefaultDir=E:\ACCESS2
JetIniPath=MSACC20.INI
UID=Admin


[SQL7DBUS2] - 2
Driver=E:\WINNT\SYSTEM\SQLSRVR.DLL
Server=DBUS2
Database=pubs
UID=dbutest
PW=dbutest

You can put anything you like in this area as long as the label matches the entry below (2).

You will now be able to access the database in SQL7, and you will be able to read tables that contain no user defined SQL types. BUT:

If they contain numbers you will not be able to update them (ODBC returns "ODBC driver does not support this function").

Copyright DBU 2000.
Last updated 24 May 2004

Data Base Unlimited
The Old School
Winchfield
Hook RG27 8DB
England

Telephone: +44 (0)1256 393050
Telefax: +44 (0)1256 393051

Information: info-nospam@dbu.co.uk
Jobs information
Jobs mail: dbuapplicants-nospam@dbu.co.uk
Webmaster: webmaster-nospam@dbu.co.uk