[dba-SQLServer]FYI -- ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL Server 2000 (Microsoft Fix)

mmmtbig at bellsouth.net mmmtbig at bellsouth.net
Tue Mar 18 14:14:58 CST 2003


ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL
Server 2000

The information in this article applies to: 

*	Microsoft Access 2000

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 oint to the correct path. 

	


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. 

RESOLUTION

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

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/default.aspx?scid=kb;EN-US;245025>
OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release
1/1a (SR-1/SR-1a) 


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: 

1.	Install the SQL Server 2000 Client Tools on a computer other
than the development computer. 

2.	On that second computer, browse to the following folder: 


	Program Files\Common Files\Microsoft Shared\MSDesigners98

3.	Select all the files in the folder, and then on the Edit menu,
click Copy. 

4.	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 

5.	Paste the contents that you copied in step 3 into the folder by
clicking Paste on the Edit menu.


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. 

MORE INFORMATION


Steps to Reproduce the Behavior


1.	Install Microsoft SQL Server 2000 on a Microsoft Windows NT 4.0
Server or a Microsoft Windows 2000 Server. 

2.	Install Access 2000 on another computer that has a clean
installation of Microsoft Windows 98. 

3.	In Access 2000, click New on the File menu. 

4.	On the General Tab of the New dialog box, click Project
(Existing Database). 

5.	Name the file Test, and then click Create. 

6.	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. 

7.	In the new Access project, click Stored Procedures. Note that
names are displayed with a ";1" at the end. 

8.	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). 

9.	Try to run an existing stored procedure. Note that you receive
the following error:


	Could not find stored procedure <Name>;1. 
10.	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. 

11.	Try to create a new table. Note that there is a brief flash on
the screen, but that the table designer does not appear. 

12.	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. 


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/default.aspx?scid=kb;EN-US;266277>
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/default.aspx?scid=kb;EN-US;272384>
ACC2000: "Overflow" Error Message When You Try to Upsize to SQL Server
2000 

 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030318/3767458f/attachment.html>


More information about the dba-SQLServer mailing list