<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1141" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#000080 size=2>
<H1 class=title><FONT size=4>ACC2000: Incompatibility Issues Between Access 2000
Projects and SQL Server 2000</FONT></H1>
<DIV class=appliesto>The information in this article applies to:
<UL>
<LI>Microsoft Access 2000</LI></UL></DIV>
<DIV class=notice>This article was previously published under Q269824 </DIV>
<DIV class=notice>Advanced: Requires expert coding, interoperability, and
multiuser skills. <BR><BR>This article applies only to a Microsoft Access
project (.adp). <BR><BR></DIV>
<DIV class=symptoms>
<H2>SYMPTOMS</H2>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:
<UL>
<LI>All existing stored procedure names are displayed with ";1" at the end.
For example "CustOrderHist" is displayed as "CustOrderHist;1".
<LI>You cannot run existing stored procedures. Instead, you receive the
following error: <BR>
<DIV class=errormsg>Could not find stored procedure <Name>;1. </DIV>
<LI>You cannot manage security against a Microsoft SQL Server 2000 server. If
you try to open the <STRONG>SQL Server Security</STRONG> dialog box, you
receive the following error: <BR>
<DIV class=errormsg>The Database administrative components failed to load or
initialize. Verify that the components are installed and registered locally.
</DIV>
<LI>You cannot create or design tables, database diagrams, or stored
procedures. Attempting to create any of these objects, such as clicking
<STRONG>New</STRONG> in the Database window or clicking a command on the
<STRONG>Insert</STRONG> menu, results in one of the following behaviors:
<P class=indent>
<H4>New Table</H4>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.
<H4>New Database Diagram</H4>You receive the following error: <BR>
<DIV class=errormsg>Cannot create objects of type 'Diagram' against current
SQL backend. Please check your permissions and server setup. </DIV>
<H4>New Stored Procedure</H4>You receive the following error: <BR>
<DIV class=errormsg>Microsoft Access can't find the object 'Microsoft Access
can't find the object 'StoredProcedure1.'.'<BR><BR>*You misspelled the object
name. Check for missing underscores(_) or other punctuation, and make sure you
didn't enter leading spaces.<BR>*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. </DIV>
<P></P></LI></UL></DIV>
<DIV class=cause>
<H2>CAUSE</H2>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. </DIV>
<DIV class=resolution>
<H2>RESOLUTION</H2>Update the designer tools. The following two updates are
available.
<H3>Microsoft Office 2000 Service Release 1/1a</H3>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.<BR><BR><STRONG>IMPORTANT NOTE</STRONG>: 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:
<P class=indent>You do not have exclusive access to the database at this time.
Your design changes will not be saved. </P><STRONG>NOTE:</STRONG> 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.
<H4>Security Management</H4>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 <STRONG>Security</STRONG> dialog box, you receive the second
error message described in the "Symptoms" section of this article, and in
addition, the following error message: <BR>
<DIV class=errormsg>[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. </DIV>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).
<BR><BR>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:
<P class=indent><A class=KBlink
href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;245025">245025</A>
OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a
(SR-1/SR-1a) </P>
<H3>SQL Server 2000 Client Tools/Files</H3>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.<BR><BR><STRONG>NOTE</STRONG>: 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.<BR><BR>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.<BR><BR>
<H4>Goals of the Updated Files Included with the Client Tools</H4>
<UL>
<LI>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.
<LI>Assures that designing a database object that takes advantage of SQL
Server 2000-specific functionality will not cause data loss to occur.
<LI>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.</LI></UL><STRONG>Updating Client Tools
Files Without Installing the Client Tools</STRONG><BR><BR>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:
<OL class=dec>
<LI>Install the SQL Server 2000 Client Tools on a computer other than the
development computer.
<LI>On that second computer, browse to the following folder:
<P class=indent>Program Files\Common Files\Microsoft Shared\MSDesigners98</P>
<LI>Select all the files in the folder, and then on the <STRONG>Edit</STRONG>
menu, click <STRONG>Copy</STRONG>.
<LI>While still at the second computer, browse to the development computer,
and then browse to the following folder:
<P class=indent>Program Files\Common Files\Microsoft Shared\MSDesigners98 </P>
<LI>Paste the contents that you copied in step 3 into the folder by clicking
<STRONG>Paste</STRONG> on the <STRONG>Edit</STRONG> menu.</LI></OL></DIV>
<DIV class=status>
<H2>STATUS</H2>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. </DIV>
<DIV class=moreinformation>
<H2>MORE INFORMATION</H2>
<H3>Steps to Reproduce the Behavior</H3>
<OL class=dec>
<LI>Install Microsoft SQL Server 2000 on a Microsoft Windows NT 4.0 Server or
a Microsoft Windows 2000 Server.
<LI>Install Access 2000 on another computer that has a clean installation of
Microsoft Windows 98.
<LI>In Access 2000, click <STRONG>New</STRONG> on the <STRONG>File</STRONG>
menu.
<LI>On the <STRONG>General Tab</STRONG> of the <STRONG>New</STRONG> dialog
box, click <STRONG>Project (Existing Database)</STRONG>.
<LI>Name the file Test, and then click <STRONG>Create</STRONG>.
<LI>In the <STRONG>Data Link Properties</STRONG> dialog box, for step 1, type
the name of the SQL 2000 server. In the Database field, type
<KBD>Northwind</KBD>. (This is the sample Northwind database included with SQL
Server 2000.) Click <STRONG>OK</STRONG>.
<LI>In the new Access project, click <STRONG>Stored Procedures</STRONG>. Note
that names are displayed with a ";1" at the end.
<LI>On the <STRONG>Tools</STRONG> menu, point to <STRONG>Security</STRONG>,
and then click <STRONG>Database Security</STRONG>. 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).
<LI>Try to run an existing stored procedure. Note that you receive the
following error:<BR>
<DIV class=errormsg>Could not find stored procedure <Name>;1. </DIV>
<LI>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.
<LI>Try to create a new table. Note that there is a brief flash on the screen,
but that the table designer does not appear.
<LI>Try to create a new database diagram. Note that you receive the following
error message:<BR>
<DIV class=errormsg>Cannot create objects of type 'Diagram' against current
SQL backend. Please check your permissions and server setup.
</DIV></LI></OL></DIV>
<DIV class=references>
<H2>REFERENCES</H2>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:
<P class=indent><A class=KBlink
href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;266277">266277</A>
PRB: Using the Visual Studio 6.0 and Access 2000 Visual Database Tools with SQL
Server 2000 </P>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:
<P class=indent><A class=KBlink
href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;272384">272384</A>
ACC2000: "Overflow" Error Message When You Try to Upsize to SQL Server 2000
</P></DIV><!--******************* PUBLIC INFO SECTION ***********************--></FONT></DIV>
<DIV><FONT face=Arial color=#000080 size=2></FONT> </DIV></BODY></HTML>