Referential Integrity and ADO (1 Viewer)

[Dennisk]

greeting,

I am doing some investigation into converting a single user booking system into a multiuser database (Access 2000). I am developing a module to use a Session Lock Table where a date will be entered into the session lock table and from then on no one else can insert another record for that date. For my first test I have set the date field to unique and wish to trap the referential integrity error which results if another user tries to insert the same key. As part of this investigation I am using ADO. But the ADO help system is either out of date or I am going up the wrong path.
the ADO help system says it a provider returns -2147467259
however all I get back is -2147217887

Is it possible to trap RI errors using the errorObject.NativeError method. Or should I revert back to DAO.

 

[MarkK]

I'm not expert here, but I just discovered that the 'Execute' method of the DAO.Database object allows you to specify a 'dbFailOnError' option. If you specify this option and a query fails the operation is rolledback and a trappable error occurs.
In brief testing here, it appears the error messages are pretty descriptive.
Here's my test code...

Private Sub aksdjf()
On Error GoTo handler
   CurrentDb.Execute _
      "INSERT INTO table2 ( table1ID, data ) " & _
      "SELECT 1, 'Test Data'", dbFailOnError
   Exit Sub
handler:
   Debug.Print Err & " " & Err.Description
   
End Sub

My tables were,
Table1
table1ID (PK)
data

Table2
table2ID (PK)
table1ID (FK)
data

I'd enforced RI on the join at 'table1ID', Table1 contained no records, and I trapped this error...

3201 You cannot add or change a record because a related record is required in table 'Table1'.

 

[Dennisk]

I'll give that a go, when I get back from a site visit. But I primarily wish to use ADO over DAO, as MS advise developers to move to ADO, however I have just noticed a problem with a Autonumber function that I developed in DAO but does not work correctly in ADO because there is no method to lock a table in ADO, so my code is failing.

 

[MarkK]

I'm not sure that MS is continuing with that advice. That was the case circa 2002 where the default reference in a new db was ADO, but DAO is optimized for use with the Jet dbEngine, as I understand it.
Search this site for more on this since it has been discussed here.
Cheers,

 

[Dennisk]

Lagbolt,
I've tested that and it works fine. Coming from the old school of programming I tend to work with recordsets but I'm slowly being won over to SQL.