Troubleshoot updating data (ADP)

Possible reasons are:

• The constraint expression is blank.
• The text of the Constraint Expression property does not conform to the constraint syntax recognized by SQL Server. Some common reasons are:
  • The expression does not resolve to a value of TRUE or FALSE.
  • The expression misspells a column name, or refers to a column in another table.
  • The expression misspells the name of a function or database constant.

You must enter a constraint in the Constraints Expression dialog box before continuing to define additional properties for your table. For more information on the syntax of constraint expressions, see the SQL Server documentation.

• The default data type you selected in the Tables/Queries tab of the Options command under the Tools menu may not be supported in all versions of Microsoft SQL Server. For example, The bigint and sql_variant data types are only supported in SQL Server 2000. If an earlier verison of SQL server doesn't support the data type you have chosen, Access replaces it as the following table shows.

  SQL Server 2000	SQL Server 7.0	SQL Server 6.5
  bigint	int	int
  nchar	nchar	varchar
  ntext	ntext	text
  nvarchar	nvarchar	varchar
  sql_variant	varchar	varchar
  uniqueidentifier	uniqueidentifier	varchar

  Note  You will be required to change the default data type if you set the default data type to a supported data type while connected to a SQL Server 2000 database (bigint for example), then change your Access project connection to an earlier version of SQL Server.

• You tried to change a named default to a default constraint. Changing from a named default to a default constraint would permanently unbind the named default and is not allowed on Microsoft SQL Server.
• You tried to set a default value on a computed column. Computed columns cannot have default values.

You may have set the maximum record limit too low for the Access project. This limit is designed to help improve performance when a user is viewing or browsing data in a datasheet or form and the query returns many records. If you are retrieving data from a select query, importing data from another Access project, or running an append or make-table query to copy data, you may want to set the maximum record limit to 0, which means no limit, to retrieve all records.

The refresh interval is not intended for use with data from an OLE DB data source. In an Access database, the refresh interval (Point to the Tools menu, click the Options command, click the Advanced Tab, and select the Refresh interval (sec) option) is used to specify how often (in seconds) data is automatically updated in a multiuser environment. For example, if you type 120, the data is updated every 2 minutes. The underlying technology for this setting is Dynamic Data Exchange (DDE). However, Access projects use OLE DB to connect to a Microsoft SQL Server database. To refresh data manually in an Access project, use the Refresh command on the Records menu, or requery the records to retrieve up-to-date data.

You may have modified the column's data type or length and made it too small for the current data. For example, you changed the data type from nchar(100) to nchar(80).

Change the data type or length back to the former value and restore the data from your backup file.