Corruption problem of ADO 2.5 against Jet 4 ??
Unless I've overlooked something (very possible), I've run into what seems to be a
serious bug using ADO 2.5 (final) against jet 4 sp3 in Access 2000 and Windows 2000
(final). I haven't tested it widely yet--it may very well be a peculiarity of some
hardware/software combination that only I have (sigh). I was wondering if anyone has
run into this or something like it before, or had any comments.
It wasn't easy to reproduce the problem and discover the cause! This is the easiest
way I know how to show it, i.e. the code is not my original code but it's designed to
make the bug standout as easily as possible. The FIRST sign of trouble is erratic
behavior of Seek (i.e. Seek fails to find a record that's there). The LAST sign of
trouble, after a few days, is "unrecognized database format" when trying to access the
backend database.
Obviously don't do this without backing up. I haven't had unrepairable damage yet,
but obviously it's a concern. The test involves two jet 4 mdbs, one is the back end,
referenced as gcnnBack. The code is running on the frontend. They are on two
separate computers connected on an Ethernet LAN workgroup. In my testing, the
primary key field that I am seeking on is a long integer (not autonumber).
Public gcnnBack as ADODB.Connection
'i.e. the backend--put this in the module declaration section.
Public Function TestAdoStep1() As Boolean
'Run this manually first. Then see the directions for TestAdoStep2.
Const ksDbsPath As String = "\\compname\sharename\subdir\backend.mdb"
'Replace with pathname of the backend mdb.
Set gcnnBack = New ADODB.Connection
With gcnnBack
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& ksDbsPath
.Open
End With 'cnn
End Function
Public Function TestAdoStep2() As Boolean
'Have the backend add a new last (i.e., according to primary key
'order) record to its table ksTblA (rstBack) periodically,
'say every 10 mins. Then run this a few mins after you add
'each record. The *second* or *third* time through this
'cycle (it varies), Seek will not find the new record. Worse,
'an immediately repeated Seek sometimes
'will find it, sometimes not. Eventually, the mdb will not be readable
'and will require a repair but apparently no lasting corruption.
'Workaround (apparently) is to reset the cnn (gcnnBack)
'every time after you do on operation on a linked table to the open cnn.
Dim rstBack As ADODB.Recordset, iCount As Long
Dim vLast As Variant, vNew As Variant
If gcnnBack Is Nothing Then TestAdoStep1
'Just in case the project code was reset
'or TestAdoStep1 wasn't run.
Const ksTblA As String = "BackendTableToSeek"
'The name of the linked tbl in the current mdb that
'points to the backend tbl on which we are seeking.
Const ksPkeyField As String = "fldWeSeekOn"
'The name of the field that is the primary key in ksTblA.
Const ksTblB As String = "SecondBackendTbl"
'ksTblB: The name of another linked table in the
'current mdb that points to another tbl in the backend.
Set rstBack = New ADODB.Recordset
With rstBack
.Index = "PrimaryKey"
.Open ksTblA, gcnnBack, adOpenKeyset, adLockOptimistic, adCmdTableDirect
End With
Debug.Print "Testing adostep2 at " & Now
vNew = DMax(ksPkeyField, ksTblA) 'Gets the value of the truly last record.
'The next line is necessary to cause the problem!
iCount = DCount("*", ksTblB) 'do any op on tbl B, a linked tbl whose home is
gcnnBack
With rstBack
.MoveLast
vLast = rstBack(ksPkeyField) 'what it thinks the last record is.
.Seek vNew, adSeekFirstEQ
If .EOF Then
Debug.Print "Can't find the new rec with key: "; vNew; _
" but thinks the last key value is: "; vLast
End If
End With
rstBack.Close
Set rstBack = Nothing
End Function
