Troubleshoot queries (ADP)

The following are common problems you may encounter when creating a query:

• The syntax you entered is valid but is not supported visually by the Query Designer. Be sure to verify your syntax before saving by using the Verify SQL Syntax button .
• Expressions with different data types can generate errors. To work with different data types in a single expression, you need to convert to the same data type using either the CONVERT or CAST function. For more information on these functions, see the SQL Server documentation.
• Make sure the number of items in the Column list in the UPDATE and INSERT INTO statements matches the number of items in the Values list. Verify your SQL statement using the Verify SQL Syntax button to make sure the number of items match.
• A function in your query has the wrong number of arguments. Review your query syntax to locate the function and provide the correct number of arguments. In a user-defined function, you must always enter parameter arguments if there are arguments, and you must always enter parentheses, even if there are no arguments.
• When you use user-defined functions, SQL Server does not automatically promote other integer data types (such as tinyint, smallint, and int) to bigint; SQL Server only returns bigint if the parameter expression is a bigint data type.
• You may be trying to pass an expression as a parameter value. Parameter values must be scalar values or simple text.
• When specifying a criteria for the EXISTS statement, you must use either True for EXISTS or False for NOT EXISTS.
• You cannot use The NOT keyword in a column cell. When entering a NOT EXIST subquery into the Column cell, enter just the EXIST clause. Then go to the Criteria cell and enter False for it.
• An alias you supplied contains embedded blanks or other white space. Remove the white space or put double-quotation marks around the alias.

The operation you are attempting requires an updateable user-defined function (an in-line function), but the function you are using is not updateable. You can replace the function with an updateable one, or you can change the query to a Select query.

You cannot use the HAVING clause in the view definition of an indexed view. Either remove the indexes or remove the HAVING clause.

You cannot use the asterisk (*) All Columns check box to select all columns in an indexed view. Because an indexed view is stored on disk, the view definition must explicitly refer to the columns to be included in the view. Replace the asterisk (*) with the columns you want to include in the view.

An indexed view must contain a clustered index because it is stored on disk. Modify the index to make it a clustered index.

If you remove schema binding, the indexes will be dropped. Some operations, such as renaming a base table column or deleting a base table, automatically remove schema binding from an indexed view. When schema binding is removed, the indexes will be dropped.

Note  Indexed views are supported when your Microsoft Access project is connected to Microsoft SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition, but they are not supported in Microsoft SQL Server 2000 Desktop Edition. Indexed views are best used when data is read-only (such as a decision support system), queries of the indexed view do not involve aggregates or joins, and the base table schema definitions of the indexed view are not likely to change. For more information on indexed views, see the SQL Server documentation.

You might see unexpected results when you use a SQL statement whose output contains two or more fields with the same name. For example, you might see unexpected behavior when you work with a view that is based on two tables and that contains two fields that have the same name but are located in different tables. To prevent unexpected behavior, specify an alias for one of the fields to ensure that every field in the output has a unique name.

Access adds a CONVERT Transact-SQL function to convert any date expressions in your SQL statements to an explicit four-digit year format as the following example shows:

CONVERT(DATETIME, '1997-01-01 00:00:00', 102)
				

For more information on the CONVERT Transact-SQL function, see the SQL Server documentation.

A Make-Table query uses the INSERT FROM SQL Statement to copy rows from one table to another. This situation might occur when you upsize your Access database from a Microsoft SQL Server version 7.0 or 6.5 database, create your own make-table stored procedure in the SQL Text Editor, or create a Make-Table query using the SQL Server Enterprise Manager in SQL Server.

To run a Make-Table query in an Access project connected to a Microsoft SQL Server version 7.0 or 6.5 database, you must set the select into/bulkcopy database option to true (the default value is false) using the system stored procedure sp_dboption as the following example shows.

Exec sp_dboption 'select into/bulkcopy'
				

You can run the system stored procedure in SQL Server Enterprise Manager or create a stored procedure in Access using the SQL Text Editor. To set this option, you must be a member of the db_owner or sysadmin roles. For more information on the select into/bulkcopy database option, see the SQL Server documentation.

You cannot use nondeterministic Transact-SQL functions, nondeterministic global variables, or extended stored procedures in a CREATE FUNCTION SQL statement. Nondeterministic functions, global variables, and extended stored procedures can return different result values each time they are called with the same set of input values, and therefore have possible side-effects in the database, such as table updates.

The following functions and global variables are not allowed in a user-defined function:

@@CONNECTIONS
@@CPU_BUSY
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE
GETDATE
GETUTCDATE
NEWID
RAND
TEXTPTR

You may be using words in the parameter prompt that are not supported. You can't use Visual Basic for Application (VBA) language keywords that are part of the Access object model as parameter names. To see a list of these language keywords, use the Object Browser in the Visual Basic Editor to display the members of the Access Application object.

You cannot use the following domain aggregate functions in a Microsoft Access project (.adp).

DAvg
DCount
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DSum
DVar, DVarP

In an Access project, your aggregate functions cannot use references to controls in objects such as forms or reports. For example, you cannot use the following expression:

=DLookUp("CategoryName";"Categories";"CategoryID = Reports!Report1!CategoryID")