Row Cannot be Located for Updating - VB6 and ADODC 1

[dwmtractor]

Trying to do an efficient delete of multiple records in an ADO recordset bound to a DataGrid control, and populated from an MS Access 2000 database. A row is deleted using the following code:
With dbMain
.Recordset.MoveFirst
Do While .Recordset.EOF <> True
.Recordset.Delete
.Recordset.MoveNext
.Refresh
Loop
End With

At the point of &quot;MoveNext,&quot; VB crashes with the error:

&quot;Run-time error '-2147217864 (80040e38):

Row cannot be located for updating. Some values may have been changed since it was last read.&quot;

This is a lot like the behavior described in MS bulletin Q300586, except that it doesn't seem to matter whether there's a default value in a numeric index field or not.

The only workaround I've found so far is to use the following:

With dbMain
.Recordset.MoveFirst
Do While .Recordset.EOF <> True
.Recordset.Delete
.Recordset.Update
.Recordset.Requery
Loop
End With

Problem is, this requires a write and then reopen of the table every time and is horribly slow for a large number of records.

Anybody got a clue what's wrong here, or have another suggestion of how I could select multiple records based upon a list of doc numbers, then delete all records with the appropriate doc number?

Thanks

Dan

 

[MarcLara]

have you tried using DO UNTIL instead of DO WHILE?

 

[dwmtractor]

Hadn't tried DO UNTIL, so I just did, and it makes no difference. Nor would I have expected it to; we're not getting to EOF on this routine; it's hanging at the MOVE NEXT point.

 

[Cocheez]

My suggestion would be to dump the data control and bind the grid manually to a recordset. You can set the DataSource property to do this. This way you can grab the list of records you wish to delete from the grid, place those indexes (or whatever you decide) in a Delete statement and then requery and refresh the grid after you are done. This will solve the problem of having to refresh on every delete, you will only have to do it after every user request. Sorry I dont have any sample code, I did something similar to this in my last position and dont have it handy.

-Matt

 

[dwmtractor]

Matt,

You may be onto something here. . .although then my question would be how to structure the Delete statement. I found in some VB documentation that you could run a SQL delete statement (&quot;DELETE FROM

WHERE. . .etc.&quot, but I could find no instructions on how to execute such a statement from within a VB routine. Ideally, I'd love to execute a single statement which says &quot;DELETE FROM [my data table] WHERE Document IN [a datagrid of doc numbers I already queried]&quot; but I can't find enough documentation to tell me how to do this.

Alternatively, if I could cycle thru my delete list and simply say &quot;DELETE FROM [data table] WHERE Document = Doc&quot; where Doc is a string variable read from my delete list, this would also be great. So far, the only way I've managed to execute SQL from within my VB is using a &quot;Datasource = strQuery&quot; command. This works for SELECT, but not DELETE.

TIA,

Dan

 

[Cocheez]

You will need to create a connection and a recordset through ADO first. Make sure you reference the latest version of 'Microsoft ActiveX Data Objects' in your project. Next use code similar to mine below to create the objects.

'
' 1. This will open and set everything
'
Dim conObj As New ADODB.Connection
Dim rsObj As New ADODB.Recordset

conObj.Open &quot;Provider=SQLOLEDB;Server=S1;Database=myDB&quot;,&quot;sa&quot;

rsObj.Open &quot;Select * From tbl&quot;, conObj, adOpenStatic, adLockReadOnly

Set datagrid.DataSource = rsObj
datagrid.refresh

'''''''''''''''''''''''
' 2. Get your list however
'''''''''''''''''''''''

'
' 3. This will delete your selected rows
'
conObj.Execute &quot;Delete From tbl Where id In (listOfIds)&quot;

rsObj.requery
datagrid.refresh




And that should do it. Manipulate code as needed.
Also if this is helpful, please mark the post as so.

-Matt

 

[Cocheez]

You will need to create a connection and a recordset through ADO first. Make sure you reference the latest version of 'Microsoft ActiveX Data Objects' in your project. Next use code similar to mine below to create the objects.

'
' 1. This will open and set everything
'
Dim conObj As New ADODB.Connection
Dim rsObj As New ADODB.Recordset

conObj.Open &quot;Provider=SQLOLEDB;Server=S1;Database=myDB&quot;,&quot;sa&quot;

rsObj.Open &quot;Select * From tbl&quot;, conObj, adOpenStatic, adLockReadOnly

Set datagrid.DataSource = rsObj
datagrid.refresh

'''''''''''''''''''''''
' 2. Get your list however
'''''''''''''''''''''''

'
' 3. This will delete your selected rows
'
conObj.Execute &quot;Delete From tbl Where id In (listOfIds)&quot;

rsObj.requery
datagrid.refresh




And that should do it. Manipulate code as needed.
Also if this is helpful, please mark the post as so.

-Matt

 

[bdavis96]

Going back to your original code:

With dbMain
.Recordset.MoveFirst
Do While .Recordset.EOF <> True
.Recordset.Delete
.Recordset.MoveNext
.Refresh
Loop
End With

The reason why you are getting the error is because on the last record which isn't end of file, you are deleting it, then trying to move to the next record which can't be done because it is now end of file. This updated code should hopefully help out:

With dbMain
.Recordset.MoveFirst
Do While .Recordset.EOF <> True
.Recordset.Delete

If .Recordset.EOF <> True Then
.Recordset.MoveNext
End If

.Refresh
Loop
End With

Hope this helps.

 

[dwmtractor]

I'm gonna try Matt's suggestion in a minute, but bdavis96's suggestion is not correct. I get this error after delete of *every* line in the list, not just the last one. So at the time I get the error EOF is definitely *not* true; there can be as many as 15-20 records left in the recordset, and I can confirm it's the first one that's being deleted.

I know this because I put an &quot;On Error Resume Next&quot; command in the loop, and when I did that I get a DataGrid error w/o number (but not the numbered error referenced above) for each line, which if I click &quot;OK&quot; it deletes the line and moves on to the next one where it errors again. This is exactly as described in MS Bulletin Q300586, BTW. So anyhow, I'm *not* EOF at the error point, it's for *every* record that gets deleted.

 

[dwmtractor]

Matt,

I tried your suggestion AND IT WORKED!!! Thanks a million! I tried to give your post the star it deserved, but the system bombed on post, so I'll try to acknowledge you properly later.

Let's follow up; there are two things I should do to &quot;clean house&quot; before I close this adventure:

1) I'd like to reindex the database now that its records have been cut substantially. . .it has an index field named &quot;Index&quot; (I never said I was creative <g>) that is sequentially numbered. I'd like to sort on two other fields, DateTime and Document, which taken together form unique records, and then renumber the Index field.

2) Then I'd like to do the equivalent of Access' &quot;Compact and repair database&quot; tool. Is there a VB add-in to do this?

Again thanks,

Dan

 

[Cocheez]

Well, for the compacting you can use JRO (Microsoft Jet And Replication Objects). This can be used to compact your Access databases.

Dim oJRO As New JRO.JetEngine

oJRO.CompactDatabase _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & sourceDb, _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & destinationDb & &quot;;Jet OLEDB:Engine Type=5&quot;



Also manually setting the indexes for your tables can easily be done using the 'Alter Table' Sql Command. An example would be something like this.

Alter Table MyTbl
Add Constraint [MyTbl_sdtDateTime_iDocument] Unique
Clustered (sdtDateTime,iDocumentType)


That is off the top of my head, it should be very close to that. Just run that Sql Statement as the source argument in your connection objects execute method.

-Matt

 

[dwmtractor]

Matt,

Thanks again for your help. I'm waiting to try the compacting databases part till I get the index working properly, and I didn't make myself clear on that (no surprise there!<g>).

Neither the DateTime nor Document fields in my database are unique in and of themselves, and since Access requires a primary key for a table, I have created one by sequentially numbering the records and putting the record number in a field called &quot;Index.&quot; When a new record is added, it's just given an Index value that's incremented +1 above the last record. This works fine except that eventually, the Index number will grow too large without resetting. So what I want to do is simply renumber the records after deleting the old ones.

Here's what I've tried:

'Reindex [Log]

Dim dbcount As Integer

conObj1.Open (conStr1)
rsObj1.Open strQuery1, conObj1, adOpenStatic, adLockReadOnly

dbcount = rsObj1.RecordCount

rsObj1.MoveFirst

For x = 1 To dbcount
rsObj1!Index = x
rsObj1.Update
Next x

Unfortunately, it appears that either you can't use this method to write to the recordset, or my syntax is wrong.

Trying to do the same thing with the data-bound control produces the same error on update that deleting a record did. How do I sequentially write the numbers to the fields?

Thanks again,

Dan

 

[Cocheez]

You are opening the recordset as readonly. Change your locktype to either Optimistic or Pessimistic, whatever is better for your solution, in the case of such a tight loop I don't believe it will matter.

Also, using the autoincrement feature is a wonderful solution. As long as the field is defined as an index your searches will zip right along.

 

[dwmtractor]

Matt-

You da man! It worked on my first run. However when I tried it again with my monster database, the renumbering loop crashed part way through with the error:

Run-time error '-2147217887 (80040e21)':

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

I tried going into regedit and increasing that key (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile) from the default of 9500 to 15000 (a random number, I'll admit) and it appears to have no effect.

Ideas?

Dan

 

[dwmtractor]

As a follow-up for others reading this thread, regarding the post on compacting databases above--Matt's code for the oJRO.CompactDatabase command works flawlessly--but *only* if you have first closed all other connections to the database! It's not enough to do a conObj1.Close command or the like either, if you have any data-bound controls to that database on your form; you have to unload the form.

Apparently there's a timing issue here, too. I placed the CompactDatabase code on a separate form, in its &quot;Load&quot; code, and tried to call it in the unload code of a form with a databound grid (the one where I did all the deletions has a grid to display and/or edit the records as well). The file-lock crash happens before the calling form gets fully unloaded. The only way around this that I've found is to create a menu form which calls either the Maintenance form (where the record deletion code lives) or a Compact Database form (where the Compaction code goes). In order to ensure that no one deletes records without compacting the database, I then put a command in the unload code of frmMaint which disables the &quot;cancel&quot; button on the menu, so that the only way back to the main menu was to go thru the compaction process.

As a second point, note that the CompactDatabase code requires that you specify a target file for the compacted database--this target can't be an existing file so it can't be the source database. If you want the finished product to have the same name as the input product, you'll need to delete the old one and then move the temporary (compacted) file to the original filename, like this:

 'TracDoc.mdb - Main Database
 oJRO.CompactDatabase _
    &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<my path>myfile.mdb&quot;, _
    &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<mypath>\temp.mdb; Jet OLEDB:Engine type = 5&quot;
    
    'Replace original database with compacted version
    fso.DeleteFile (&quot;<mypath>\myfile.mdb&quot;)
    fso.MoveFile &quot;<mypath>\temp.mdb&quot;, &quot;<mypath>\myfile.mdb&quot;

Dan

 

[dwmtractor]

Just a closure post on this thread to thank all who responded, especially Matt (cocheez) who bailed me out big time here. Your help is *greatly* appreciated, and I now have a working program.

Thanks again

Dan