Program throwing bookmark invalid error{Resolved}

[David RH]

Hello

I have an app that loads data into an access DB for searching. It's been working fine for years now but all of a sudden it starts crashing with:

Run-time error -2147217906
Bookmark is invalid.

It throws the error when it tries to execute a RS.movenext command on the recordsets.

I have no code in my app for setting, checking or referencing bookmarks of any kind and I can't figure out what BM could be invalid since none should be set or even be referenced.

Any Ideas?

Thanks

David

[si_the_geek]

Have you got any controls that refer to the recordset (or database)?

Some data-bound controls (such as various grids, etc) automatically use bookmarks.

[David RH]

Have you got any controls that refer to the recordset (or database)?

Some data-bound controls (such as various grids, etc) automatically use bookmarks.

No controls are data-bound. No grids etc. I do have a couple of list boxes that I put info into manually but that doesn't happen till later in the game. I checked those and they are not data bound as far as I can tell.

I do it all with ADO.

This is how I mount the DB:

Set rs = New ADODB.Recordset
DE.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & DBName
rs.Open "Lines", DE, , adLockOptimistic

I've searched the code and there are no references to bookmarks in the code.

If I throw ?rs.bookmarks into the immediate window I get the message:Run-time error 3251
Current Recordset does not support bookmarks.

The weird thing is that this app has worked flawlessly for years and I haven't made any changes recently to the code so I don't understand why it just started to do this.

Another thing that doesn't make sense is that after the app opens the DB it clears it by walking thru the records and deleting each one before it starts to fill it back up.

        If rs.EOF <> True And rs.BOF <> True Then
            rs.MoveFirst
            Do While rs.EOF = False
                rs.Delete                'clear the lines DB.
                rs.Update
                rs.MoveNext
            Loop
        End If

and that goes just fine.

It's on that first movenext after filling the first record that the error occurs.

Thanks

[Doogle]

Something must have recently changed - new version of Access perhaps?, Operating System update ?
Have you tried installing on another machine and see if it still exhibits the same failure?
What is the Operating System it's running under?

EDIT: Googling around for the error I see quite a few hits mentioning 'Database Corruption', dosen't Access have a 'Repair' facility? It may be worth considering that, or DROP and Re-Create the Table.

[David RH]

Something must have recently changed - new version of Access perhaps?, Operating System update ?
Have you tried installing on another machine and see if it still exhibits the same failure?
What is the Operating System it's running under?

W7 64b. a couple of patch Tuesdays and AV updates. No updates to Access that I know of. I haven't tried another machine if I can't find a cure I may try that next.

EDIT: Googling around for the error I see quite a few hits mentioning 'Database Corruption', dosen't Access have a 'Repair' facility? It may be worth considering that, or DROP and Re-Create the Table.

I will try the repair.

It could be just this table is fried as it has no trouble loading records in 4 other tables from the same DB just b4 it tries to load this table.

Thanks

[David RH]

No luck with the repair. I used the the MS jetcomp.exe repair tool and it didn't fix the problem.

I tried a couple of backup DBs from b4 the problems started and got the same error.

I don't want to rebuild the table if I can avoid it as it has 98 columns with varying rules, data types and parameters.

[Doogle]

Desperation stakes - System Restore back to before Tuesday ? (or to before the problem started to hapen) - that might eliminate the Operating Environment.

[David RH]

It's been 2 patch Tuesdays since this started, that's a lot of patching to undo, so I think I'll try the install on another machine first.

This just doesn't make sense to me. The app has no problem moving thru the DB and deleting the records of this table and then creating a whole new set of records. The problem doesn't manifest until after one of the records is filled and you try to move to the next. I've got a lot of columns in this table, I'm wondering if there is some new patch that restricts the number of columns or some thing else of that sort.

I used to use MDAC 2.7 with XP but all of that is native in W7. I wonder if that got changed by one of he recent patches.

[Zvoni]

I'm trying to understand two things:

If rs.EOF <> True And rs.BOF <> True Then
            rs.MoveFirst
            Do While rs.EOF = False
                rs.Delete                'clear the lines DB.
                rs.Update
                rs.MoveNext
            Loop
        End If

If you just want to delete all records from a Table why don't you just use an Execute-Statement like "DELETE * FROM Lines" (i take it "Lines" is a Table in your DB)?

Second: If you've deleted all records from a table, why do you expect a recordset after the one you just added? There is none! With MoveNext you're telling RS to move into empty territory.

[David RH]

If you just want to delete all records from a Table why don't you just use an Execute-Statement like "DELETE * FROM Lines" (i take it "Lines" is a Table in your DB)?

It was years ago when I wrote this but I seem to remember that there was some issue with that method that made it unreliable. And this worked so I just never went back to it.

Second: If you've deleted all records from a table, why do you expect a recordset after the one you just added? There is none! With MoveNext you're telling RS to move into empty territory.

Yeah I didn't complete the scenario in that post. I did however finally get around to it in the post above this one. I delete all of the records because each time I have a whole new set of data for each record (most importantly the title/ID of the record) and a varying number of records required. After deleting all the records the app creates a whole new set of records in the correct number and with the appropriate title/ID. So there are records (typically about 100) and I'm on the first one trying to move to the next. I have confirmed this by opening the DB up and looking at the table with this error posted and there are records in the table. Now these records are empty except for the title/ID column and no errors were thrown either when it created the records or filled the title/ID column and moved to the next record. Only on the second pass on filling in the rest of the columns does it have an issue.

And to top it off there are 4 other tables that get filled b4 this one and they have no issues. I'm going to go back and look but I'm pretty sure that I used the same process to fill of the others, because I use the same delete process on all the other tables.

This app is my toolbox. It's main function is the take text files and convert them to DB files for the end user app to do it's thing with.

[Zvoni]

After deleting your records, have you tried closing and reopening your table?
I'm still trying to get through your description.

[DataMiser]

Well you do not need to call rs.update after rs.delete for sure, I don't know that will cause the error but it possibly could as there is no current record at that point. i,e, it has been deleted.

Using a delete from statement would be the correct choice. If you tried it in the past and had issues with it then it is likely that you were not doing it correctly. I have been using them for years in both DAO and ADO on several different types of databases and have never ran into an issue.

[Zvoni]

Using a delete from statement would be the correct choice. If you tried it in the past and had issues with it then it is likely that you were not doing it correctly. I have been using them for years in both DAO and ADO on several different types of databases and have never ran into an issue.

Especially if you use a generic statement like "DELETE * From MyTable".
The only thing that can crash this statement is if you write your tablename wrong, or if you have dependencies (e.g. Foreign Keys) in this table.

[DataMiser]

Another issue one might encounter is if they want to delete a record but omit the where clause which would cause the delete statement to delete all the records from the table rather than the target record, or they want to delete a single record but do not have a key field to id the record for the delete statement.

In most cases Delete From is faster than RS.Delete and when doing more than one record is a lot faster clearing a table of 1 million records is almost instant where with a loop like shown in the posts above it would take a while.

[Zvoni]

Exactly what i've been talking about.
Clear the Table with the DELETE FROM-Statement, then if you're populating the Table anew with new records there is no need for a MoveNext-Statement

[Doogle]

Whilst all the discussion regarding 'what should be done or what should not be done' is quite valid, the question still remains - Why has it 'suddenly' started to fail? I guess that there are Zillions of Applications out there that might have been written in a style like: 'it works so leave it alone', and may not be following "The Rules" (which can change at Microsoft's whim - and unless you're on top of all the changes you may not realise the implications. Look at the chaos when Microsoft implemented the App.Path 'rules' in Vista (or was it XP).

Understanding what's changed may give OP an idea as to how to resolve the situation and future proof the Application.

[Zvoni]

Whilst all the discussion regarding 'what should be done or what should not be done' is quite valid, the question still remains - Why has it 'suddenly' started to fail? I guess that there are Zillions of Applications out there that might have been written in a style like: 'it works so leave it alone', and may not be following "The Rules" (which can change at Microsoft's whim - and unless you're on top of all the changes you may not realise the implications. Look at the chaos when Microsoft implemented the App.Path 'rules' in Vista (or was it XP).

Understanding what's changed may give OP an idea as to how to resolve the situation and future proof the Application.

True, so the only thing i can think off that might have changed is maybe the system-dll where the ADO is stored due to a Windows/Microsoft-Update and/or one of those KillBits.

Everytime I do a Windows-Update it crashes my ListView-Control. I always have to re-register it afterwards.
Maybe it's something like that.

Or maybe he had an "On Error Resume Next" before, and now he doesn't.... who knows?

EDIT: I found this Thread: http://www.vbforums.com/showthread.p...ime-error-3251
Is it possible that you have Read-Only-Rights?

[si_the_geek]

may not be following "The Rules" (which can change at Microsoft's whim - and unless you're on top of all the changes you may not realise the implications. Look at the chaos when Microsoft implemented the App.Path 'rules' in Vista (or was it XP).

It was common knowledge pre-VB6. The earliest documentation available is Windows 2000, and it is definitely in there (link via the VB FAQs article on folders to use), and was presumably in earlier versions too (I'd guess Windows 95, and earlier versions of NT).

The biggest change regarding App.Path since the release of VB6 is that (in Vista and later) Windows users are no longer full admins by default, and to deal with that Microsoft implement various features (such as virtualisation) which enabled the rule-breaking apps to run better than they would have on Windows 2000 for a non-admin user.

While extra rules get added over time, there are surprisingly few, and they rarely contradict previous guidance.

[David RH]

DOH! :}

Well this is kind of embarrassing. I'm not sure how it happened but the .movefirst at the beginning of the operation had been deleted so that I was starting on the last record of the table. Not sure why it threw a "bookmark invalid" error instead of some sort of EOF error Or just figured it was done and moved on to the next operation. So when I happened to take a look at the record ID # I realized that it was on the last record, but should have been on the first so I went back in the code to where it should have pulled it back to the start and Wahlah, it was gone. Reinserted it and all is right with the world. The weird thing is I haven't messed with he code of this app in ages, But there is was or wasn't as the case may be.

So I tried the suggested delete method and after I figured out that I need to to run it on the DB and not the recordset it seemed to work just fine. I'm keeping the old method around rem'd out for the time being in case whatever issue caused me to go with that one over the new one rears it's ugly head again.

EDIT: Well I spoke too soon about the delete method working. What happens it now is that execution arrives at the fill operation positioned on the last record. The .movefirst fires but now instead of ending up back on the first record it ends up in some nether region. Not EOF or BOF (Both say false) and when I hover over the statement "rs.Fields("LineNumber").Value" it says "Record is Deleted". Then if I run .movenext it moves to the first record.

Delete statement:

        If rs.EOF <> True And rs.BOF <> True Then
            DE.Execute "Delete From Lines"              'clear the lines DB.
        End If

Did I miss a step or something?

Thanks for all the great input and ideas. Sorry it was a coding boo boo in my part.

[Doogle]

The DB.Execute has invalidated the RecordSet; perhaps an 'rs.Requery' is required? which I suspect will result in BOF and EOF being True so you'd then do a rs.AddNew etc.

[David RH]

Ok I got it to work by moving the - DE.Execute "Delete From Lines" - to just after opening the DB but b4 creating the recordsets.

Thanks