You receive an error message when you try to create or to modify the design of an SQL object in Office Access 2003 or in Access 2002 (917418)

SYMPTOMS

You receive an error message when you try to create or to modify the design of an SQL object in Microsoft Office Access 2003 or in Microsoft Access 2002. Consider the following scenario:

You create an Access project (.adp) file that is connected to a Microsoft SQL Server 2005 database.
The Access project contains SQL objects such as tables, views, stored procedures, functions, or database diagrams.

In this scenario, you receive the following error message when you try to create a new design or when you try to make a design change to an existing SQL object:

This version of Microsoft Office Access doesn't support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft Office Update Web site for the latest information and downloads (on the Help menu, click Office on the Web). Your design changes will not be saved.

When you click OK in the message box, you may receive one of the following error messages:

Message 1

You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected. You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools.

Message 2

Catastrophic failure.

WORKAROUND

To work around this behavior, use one of the following methods, depending on your situation.

Method 1: Use Microsoft Visual Basic for Applications code to create or to modify a SQL object design

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

If you do not have to use a graphical user interface, you can use Transact-SQL statements in Visual Basic for Applications code to create or to modify a design for SQL objects. The following is a sample code fragment to demonstrate how to change an existing view in the current project by using Transact-SQL statements.

Dim strSQL As String

strSQL = "ALTER VIEW [dbo].[vwView1] AS " & _
  "SELECT EmployeeID, LastName, FirstName, Region " & _
  "FROM dbo.Employees " & _
  "WHERE (Region IS NULL)"

CurrentProject.AccessConnection.Execute strSQL

For more information about how to create and how to use Transact-SQL statements, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms189826(SQL.90).aspx

Method 2: Use the SQL Server Management Studio to create or to modify a SQL object design

If you want to use a graphical user interface, you can use the SQL Server Management Studio to create or to modify a design for existing SQL objects. For more information about how to use the SQL Server Management Studio, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms174173.aspx

Note Microsoft SQL Server 2005 Express edition does not include the SQL Server Management Studio. However, you can download a community technology preview of Microsoft SQL Server Management Studio Express. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

907716 How to obtain SQL Server Management Studio Express (SSMSE) community technology preview (CTP)

MORE INFORMATION

SQL Server 2005 contains many improvements, and significant changes are required to make the Access graphical design tools function. We currently have no plans to release an update that lets you use the Access graphical design tools with SQL objects that are in a SQL Server 2005 database. However, future Access product releases may offer limited support for using graphical design tools when connected to a SQL Server 2005 database. We will update this article with information as we get closer to the release dates for the next version of Access.

For more information, click the following article number to view the article in the Microsoft Knowledge Base: