Description of incompatibility issues between Access 2000 projects (*.adp) and SQL Server 2000

View products that this article applies to.

This article was previously published under Q269824

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

If you have an Access project (*.adp) that was created with Microsoft Access 2000 with no service releases applied, and the project is based on a Microsoft SQL Server 2000 database, you may notice several incompatibility issues, the most obvious being the following:

All existing stored procedure names are displayed with ";1" at the end. For example "CustOrderHist" is displayed as "CustOrderHist;1".
You cannot run existing stored procedures. Instead, you receive the following error:
Could not find stored procedure <Name>;1.
You cannot manage security against a Microsoft SQL Server 2000 server. If you try to open the SQL Server Security dialog box, you receive the following error:
The Database administrative components failed to load or initialize. Verify that the components are installed and registered locally.
You cannot create or design tables, database diagrams, or stored procedures. Attempting to create any of these objects, such as clicking New in the Database window or clicking a command on the Insert menu, results in one of the following behaviors:
New Table
The Access 2000 Table designer does not load. Briefly, a dimmed window appears and then closes with no error message as to why the designer failed to load.
New Database Diagram
You receive the following error:
Cannot create objects of type 'Diagram' against current SQL backend. Please check your permissions and server setup.
New Stored Procedure
You receive the following error:
Microsoft Access can't find the object 'Microsoft Access can't find the object 'StoredProcedure1.'.'

*You misspelled the object name. Check for missing underscores(_) or other punctuation, and make sure you didn't enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't on the path you specified. Use the Linked Table Manager to update the link and point to the correct path.

Back to the top

CAUSE

SQL Server 2000 was released over a year after Access 2000; consequently, Access 2000 does not support all of the new features of SQL Server 2000, and incompatibilities exist.

Back to the top

RESOLUTION

Update the designer tools. The following two updates are available.

Back to the top

Microsoft Office 2000 Service Release 1/1a

Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a) includes updated Visual Database Tools for Access projects. Office 2000 SR-1/SR-1a addresses most of the issues described in the "Symptoms" section.

IMPORTANT NOTE: Office 2000 SR-1/SR-1a also sets the table, view, and database diagram designers to read-only. These designers are read-only to prevent the possibility of losing data and meta-data in tables and views. You can, however, create stored procedures. In Access 2000 SR-1/SR-1a, if you try to create a table, view, or database diagram, you see the following alert:

You do not have exclusive access to the database at this time. Your design changes will not be saved.

NOTE: If you want to install Office 2000 SR-1/SR-1a, you should do so before installing the Client Tools. If you install Office 2000 SR-1/SR-1a after installing the Client Tools, you should reinstall the Client Tools.

Security Management

Microsoft Office SR-1/SR-1a does not address the inability to manage SQL Server 2000 Security from within an Access project. If you try to open the Security dialog box, you receive the second error message described in the "Symptoms" section of this article, and in addition, the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to connect to this server.

In order to manage SQL Server 2000 security from an Access project, you must install the SQL Server Client Tools (See the "SQL Server 2000 Client Tools" section later in this article).

For additional information about obtaining Office 2000 SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 (http://support.microsoft.com/kb/245025/EN-US/ ) OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

Back to the top

SQL Server 2000 Client Tools/Files

A more comprehensive update is the SQL Server 2000 Client Tools. The SQL Server 2000 Client Tools update addresses all of the issues in the "Symptoms" section and the "Microsoft Access 2000 SR-1/SR-1a" topic of the "Resolution" section of this article. Installing the tools updates a number of files essential to Access project design. These updated files give you the ability to design SQL Server 2000 databases in an Access project at a SQL Server 7.0 level of functionality.

NOTE: The client tools must be installed on each development computer; in other words, on any computer on which someone may make design changes in an Access project to a SQL Server 2000 database.

If you want to use all of the new features in SQL Server 2000, it is best, if possible, to design them in SQL Server Enterprise Manager, which is included in the SQL Server 2000 Client Tools.

Goals of the Updated Files Included with the Client Tools

Assures that users of SQL Server 2000 databases that have not implemented any of the new features of SQL Server 2000 do not experience any degradation of functionality when designing SQL Server 2000 databases in an Access 2000 project.
Assures that designing a database object that takes advantage of SQL Server 2000-specific functionality will not cause data loss to occur.
Assures, in most cases, that designing a database object that takes advantage of SQL Server 2000-specific functionality does not result in the loss of meta-data. In the few cases that might result in meta-data loss, you are warned by means of a dialog box.

Updating Client Tools Files Without Installing the Client Tools

If you do not want to install the SQL Server 2000 Client Tools on your development computer, you can follow these steps to just update the files that address incompatibility issues:

Install the SQL Server 2000 Client Tools on a computer other than the development computer.
On that second computer, browse to the following folder:
Program Files\Common Files\Microsoft Shared\MSDesigners98
Select all the files in the folder, and then on the Edit menu, click Copy.
While still at the second computer, browse to the development computer, and then browse to the following folder:
Program Files\Common Files\Microsoft Shared\MSDesigners98
Paste the contents that you copied in step 3 into the folder by clicking Paste on the Edit menu.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected by Microsoft Office 2000 SR-1/SR-1a and the SQL Server 2000 Client Tools.

Back to the top

MORE INFORMATION

Steps to Reproduce the Behavior

Install Microsoft SQL Server 2000 on a Microsoft Windows NT 4.0 Server or a Microsoft Windows 2000 Server.
Install Access 2000 on another computer that has a clean installation of Microsoft Windows 98.
In Access 2000, click New on the File menu.
On the General Tab of the New dialog box, click Project (Existing Database).
Name the file Test, and then click Create.
In the Data Link Properties dialog box, for step 1, type the name of the SQL 2000 server. In the Database field, type Northwind. (This is the sample Northwind database included with SQL Server 2000.) Click OK.
In the new Access project, click Stored Procedures. Note that names are displayed with a ";1" at the end.
On the Tools menu, point to Security, and then click Database Security. Note that you receive an error stating that components failed to load or initialize (see the full error in the "Symptoms" section of this article).
Try to run an existing stored procedure. Note that you receive the following error:
Could not find stored procedure <Name>;1.
Try to create a new stored procedure. Note that you receive the error message with the repeated phrase "Microsoft Access can't find the object 'Microsoft Access can't find the object" as described in the "Symptoms" section of this article.
Try to create a new table. Note that there is a brief flash on the screen, but that the table designer does not appear.
Try to create a new database diagram. Note that you receive the following error message:
Cannot create objects of type 'Diagram' against current SQL backend. Please check your permissions and server setup.

Back to the top

REFERENCES

For additional information on how the new features of SQL Server 2000 affect Access Projects, click the article number below to view the article in the Microsoft Knowledge Base:

266277 (http://support.microsoft.com/kb/266277/EN-US/ ) PRB: Using the Visual Studio 6.0 and Access 2000 Visual Database Tools with SQL Server 2000

For additional information on an error occurring during upsizing to SQL Server 2000, click the article number below to view the article in the Microsoft Knowledge Base:

272384 (http://support.microsoft.com/kb/272384/EN-US/ ) ACC2000: "Overflow" Error Message When You Try to Upsize to SQL Server 2000

Back to the top