Problems with MS Access and ODBC

A common problem is ‘#deleted” fields.

• Use the following recommended best practices:

1. You should have a primary key in the table, could be an auto-increment integer PK NN UQ UN AI
2. You should have a timestamp in all tables you want to be able to update. Use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
3. Access can’t always handle DATE columns. If you have problems change to DATETIME.
4. Only use double float fields
5. Use the “Return matching rows” option in the DSN. This can be done by editing:
   .cxoffice/<bottlename>/drive_c/Program Files/Common Files/ODBC/Data Sources/xxx.dsn
   and adding the line:
   OPTION=2

• Good summary produced by MySql: docs.oracle.com/…c-errors.html
• Extremely useful explanation of ‘#deleted’ fields produced by Microsoft: support.microsoft.com/…/128809
• extract:

The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows:

• Having an update or insert trigger on the table, modifying the key value.
• Basing the unique index on a float value.
• Using a fixed-length text field that may be padded on the server with the correct amount of spaces.
• Having a linked ODBC table containing Null values in any of the fields making up the unique index.

These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted.

• There is a problem when cutting and pasting fields, because the record will be identical (initially) and so Access will not be able to identify it uniquely.
• One solution is to make one of the fields in the form have an “after update” event that modifies it to make it unique. e.g. a date field is modified to make it today

Private Sub ItemDay_AfterUpdate()
  If IsNull(Me!timestamp) Then 'no timestamp means it is a newly created field
    ItemDay = Int(Now) 'or change some other field to make it different from the pasted source
  End If
End Sub

Problem with crashes when left unattended

• Get error “microsoft access has encountered a problem”
• This article suggests increasing the ODBC Refresh Interval: support.microsoft.com/…/282349
• This article says lower it to 5 seconds: grokbase.com/…ct-problem-fixed
• Another reference: www.postgresql.org/…versal.com
• Change the registry key, as recommended: www.postgresql.org/…versal.com

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC]
ConnectionTimeout = 0 (was 600. 0 means never disconnect)

Problems with “this action will reset the current code in break mode”