Troubleshoot Access projects (ADP)

The Transact-SQL Help and SQL Server error message help topics are no longer available from the Access Help Contents Tab. For more information on Transact-SQL and SQL Server error messages, see the SQL Server Documentation.

The Database Security and Replication menus previously available from the Tools menu of Microsoft Data Engine (MSDE) are no longer available on Microsoft SQL Server 2000 Desktop Engine. You can use SQL Server 2000 Enterprise Manager to replicate and help protect your database. You can also use the GRANT, REVOKE, and DENY Transact-SQL statements to help protect your database. For more information on SQL Server Enterprise Manage, SQL Server replication, and security, see the SQL Server Documentation.

If you use the shortcut menu on the Windows Desktop (by right-clicking the desktop, pointing to New, and then clicking Microsoft Acess Application) to try to create a new Access project (.adp) , you won't be able to. You can only create an Access database.

To create a shortcut to an Access project, locate the Access project in Windows Explorer, right click on the Acces project filename, Click Create Shortcut, and then drag the shortcut to the Windows Desktop.

Tip

Access cancels a backup, restore, or drop database operation if you don't have adequate permissions. Access cancels a backup operation if you don't have enough disk space to create the backup file. Make sure you have the correct permissions in SQL Server and adequate disk space before doing any of these operations.

You cannot backup, restore, or drop a SQL Server database running on a remote computer using an Access project. You can backup, restore, and drop a SQL Server 7.0 or later database or SQL Server 2000 Desktop Edition database on a local computer, or use SQL Server Enterprise Manager for these operations on a remote computer.

When you repair an Access project file (.adp) (Point to Database Utilities on the Tools menu, and then click Compact and Repair Project), Access must open the file in Exclusive mode to perform the repair operation.

Make sure that all other users have closed the Access project before attempting to repair it.

Note  The Compact and Repair Project command compacts and repairs an Access project, not a SQL Server database. To compact and repair a SQL Server database, use SQL Server Enterprise Manager. For more information on SQL Server Enterprise Manager, see the SQL Server documentation.

• Your data may be stored in OEM format and the conversion setting is wrong. To determine whether data was stored in OEM format, you can use a query to display the contents of the tables you are working with. If extended characters appear incorrectly, the OEM conversion setting is probably wrong.

  Close the Access project, change the setting in the SQL Server Client Network Utility program (under the DB Library Options tab), and then re-open the Access project.

• You may see unexpected behavior if the system code page of client machines doesn't match the character set of the SQL Server. On the client, the system code page is the character set that is used by the language specified in the operating system; if you are running Windows 2000 or later, this is the Default Locale. On the server, the character set is specified during SQL Server installation.

  In an Access project (*.adp), query results containing ANSI text fields are not displayed correctly if the system code page of the client computer does not match the character set of the SQL Server to which the client is connected. This problem does not occur for data that is stored as Unicode on the server.

Valid database object names begin with a character or an underscore (_) and contain a combination of characters, numbers, and underscores. The maximum length for an object name stored in the database (queries, database diagrams, and tables) is 128 for SQL Server version 7.0 or later, and 30 for SQL Server version 6.5.

• The first character must be an underscore, or a letter as defined by Unicode Standard 2.0. The Unicode definition of letters includes Latin characters a-z and A-Z, in addition to letter characters from other languages.

  In general, object names should not begin with an at sign (@), or pound sign (#). SQL Server uses a leading pound sign in an object name to indicate that the object is a temporary object. An identifier beginning with an at sign (@) character denotes a local variable or parameter. Some Transact-SQL functions have names that start with double at sign (@@) characters.

• Subsequent characters in an object name can be:
  • Letters as defined in the Unicode Standard 2.0.
  • Decimal numbers from either Basic Latin or other national scripts.
  • The at sign (@), dollar sign ($), pound sign (#), or underscore (_) characters.

The object name should not contain Transact-SQL reserved keywords. Transact-SQL reserves both the uppercase and lowercase versions of reserved keywords. For a list of reserved keywords, see the SQL Server documention.

Special characters, including the double-quote character ("), are not allowed.

In general, avoid names that contain periods.

In an Access project, you cannot change the database owner of an object using the database, table, or query designers. If you need to change the database owner of an object, use SQL Server Enterprise Manager. For more information on database object ownership and SQL Server Enterprise Manager, see the SQL Server documentation.

• You might not have permission to perform this operation. Contact the owner of the object or the database administrator to get permission to modify this object.
• The object might no longer exist in the database. Refresh the database window to find out if this object still exists in the database.
• You are not logged in as the database owner or as a user that is a member of the db_owner role.

  Because you are not logged on as the system administrator, database owner, or a user that is a member of the db_owner role, you have limited privileges to the database. The privileges you have are determined by the permissions granted to your logon ID and the privileges granted to the roles that your logon ID is a member of.

• The view, stored procedure, or user-defined function is encrypted and can't be opened again.

  Encryption helps prevent all users (regardless of database privileges), including yourself, from ever seeing the view, stored procedure, or user-defined function.

  In Access 2000, you could encrypt a view from an Access project through the Properties dialog box of the View window. In Access 2002 or later, you can only encrypt views, stored procedures, or user-defined functions using Transact-SQL (by adding the WITH ENCRYPTION clause in the SQL pane) or by using SQL Server. For more information on encryption, see the SQL Server documentation.

  Important  Once you encrypt a view, stored procedure, or user-defined function, you can't change it. If you anticipate the need to revise it, save the SQL code in a text file in a controlled location before you encrypt it. When you need to revise it, you can do the following:

  1. Delete the old encrypted view, stored procedure, or user-defined function.
  2. Create a new view, stored procedure, or user-defined function with the same name.
  3. Cut and paste the SQL from the saved text file into the SQL pane of the new view, stored procedure, or user-defined function.
  4. Revise the new view, stored procedure, or user-defined function as desired.
  5. Save the revised SQL in a new text file in a controlled location.
  6. In the SQL pane, encrypt the new view, stored procedure, or user-defined function using the WITH ENCRYPTION clause.

If your Access project is connected to Microsoft SQL Server or Microsoft 2000 Personal SQL Server, the sort order is set to "Dictionary order, case-insensitive," and Unicode collation case sensitivity is turned on, you can see unexpected behavior with database objects that have the same name but are spelled with all or some letters in a different case. For example you can accidentally delete the wrong table if you have three tables named "categories," Categories," and CATEGORIES." Access finds the first occurrence of the object name that matches the spelling but does not try to match the case because it is using a dictionary sort order that is case-insensitive. Therefore, the wrong object could be deleted.

Access uses the sort order to determine which object to delete or open. The default sort order of Microsoft SQL Server and Microsoft 2000 Personal SQL Server is "Dictionary order, case-insensitive," meaning they ignore case when acting upon objects. Also, the default setting for Unicode Collation is case-insensitive. However, if during the setup of Microsoft SQL Server or Microsoft 2000 Personal SQL Server, the Unicode Collation case sensitivity is turned on, but the Sort Order is left at the default of "Dictionary order, case-insensitive," it allows objects with the same spelling but with different case to exist on the server.

When you install SQL Server or Microsoft 2000 Personal SQL Server make sure you select Custom Setup. In the Character Set/Sort Order/Unicode Collation dialog box, set the Sort Order to Dictionary order, case-insensitive and under Unicode Collation, make sure the Case-insensitive option is not selected.

Note  This behavior does not occur if the server was set up with case-sensitive sort order as well as case-sensitive Unicode collation.

If you uninstall Microsoft Data Engine (MSDE) 1.0, and then install SQL Server 2000 Desktop Engine, you can connect your Access project to the database file (.mdf) created by MSDE. However, when you connect this database file, Access converts it from SQL Server 7.0 to SQL Server 2000. Therefore, you will not be able to either convert them back to SQL Server 7.0 or use them with MSDE.

It’s possible to have MSDE and SQL Server 2000 Desktop running on the same computer. You can do this by providing an instance name (MSOFFICE, for example) when you do a custom installation of SQL Server 2000 Desktop Engine and ensuring your Access project client computer has Microsoft Data Access Components (MDAC) version 2.6 installed. In this case, you can transfer a SQL Server 7.0 database (Point to Database Utilities on the Tools menu and then click Transfer Database) to a SQL Server 2000 database, but you cannot transfer a SQL Server 2000 database to a SQL Server 7.0 database.