There are circumstances when you may get errors trying to add or edit a record in your database. 1 Click DB automatically returns validation errors in a friendly manner at run time, and these can only be resolved by entering correct data. Getting an error when no value is entered in a required field is normal operating behavior and should not be considered a problem. Here are explanations and resolutions to the most common problems related to add/edit/delete operations in 1 Click DB:
MS Access and SQL Server Tables with primary keys defined without a date field are always updateable if OLEDB provider is selected. Other data is only updateable when it meets the other requirements detailed below.
An Access Autonumber field or SQL Server Identity column must be present for add and editing functions to work most efficiently. This field does not have to be the primary key, but should be indexed for best performance. For Oracle and MySQL and non-ODBC connections the presence of a primary key field will also enable editing. In many (but not all) circumstances, if a table does not have an Identity field already defined, adding one will not negatively effect other aspects of your database including pre-existing scripts and queries.
SQL Server tables must have an primary key defined to enable automatic editing of a record after it has been added. The primary key does not have to be your Identity column. This requirement does not apply to Access tables, but as a matter of good design, all your tables should have primary keys.
ADO does not resolve datetime types to the millisecond !! Applications requiring greater precision are advised not to use ADO (at least not without special care !). For this reason tables with a date field in its primary key are only editable by 1 Click DB when an separate autonumber/identity can be used to identify the record. Any application using ADO may experience truncation of data beyond the second and this mismatch is the cause of a number of subtle issues associated with using SQL queries using ADO. Also note that SQL Server actually implements date time precision to the one three hundredth of a second and rounds the difference. See Microsoft Knowledge Base Article - 246438 for more info.
1 Click DB cannot edit GUIDs !! GUID fields are not editable in 1 Click DB nor can they be used in any part of a primary used for editing. For this reason tables with a GUID in its primary key are only editable by 1 Click DB when an separate autonumber/identity can be used to identify the record.
You do not have permissions to edit the current data or the current view is not updateable. Ask your database administrator for guidance.
Your Access table may be locked. This may happen when a user has the table open in Access Design view or when a piece of code does not release a lock. Sometimes it may be necessary to delete the Access .ldb lock file to resolve this error.
Your Access table may be read only in your file system. Be sure to check file permissions for the Internet user and make sure at least read and write are enabled.
Memo fields require special consideration as they are "BLOBs" (Binary Large Objects) and both SQL Server and Access sometimes have problems with long handling from ADO. Here are some tips on handling these in custom code :
Select BLOB fields LAST in your SELECT statements and in the same order they are physically sotred in the table. You may only be able to >reference it once before the cursor loses its value.
Always use native OLE DB provider instead of an ODBC provider.
Use Server-side cursors when appropriate
Do not use * and be sure to select the Primary Key column(s) in addition to any other columns.
Access all non-BLOB columns first (store them if necessary).
When editing a BLOB column using the AppendChunk method, you may have to edit at least one non-BLOB column as well.
BLOBs are typically not updateable with Static or Forward-only cursors on ODBC datasources or when you use ODBC JET recordsets returned by a parameter query.