Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Description of incompatibility issues between Access 2000 projects (*.adp) and SQL Server 2000
Article ID: 269824 - 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).
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:
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.
Update the designer tools. The following two updates are available.
Microsoft Office 2000 Service Release 1/1aMicrosoft 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 ManagementMicrosoft 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:
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).
[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.
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:
(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)
SQL Server 2000 Client Tools/FilesA 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
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:
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.
Steps to Reproduce the Behavior
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:
266277For 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:
(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
(http://support.microsoft.com/kb/272384/EN-US/ )ACC2000: "Overflow" Error Message When You Try to Upsize to SQL Server 2000