DAO Error Message

[Darkbob]

I'm using DAO in a multi-terminal situation. Sorry. Can't change it now without a total re-write and that's just not possible.

So here's my code (I added line numbers to help pin down the error):

The database is open in Read-Only mode:

Set DbLookUp = DB.OpenRecordset("Names", dbOpenTable, dbReadOnly)

On error goto ETrap
Temp$="SMITH"
590 DbLookUp.Index = "Last"
600 DbLookUp.Seek "=", Temp$

ETrap:
MsgBox "Error occured in " + Routine$ + "ERL: "+Str(Erl) + " Err: " + Str(Err) + " " + Error$


This generates the following error message:

Error Number: ERL: 600 Err: 3046 Could not save; currently locked by another user.


Note that I'm getting error 3046 - could not save. what the heck!?!? I'm not trying to save - I'm doing a seek. It's a read-only database anyway.

When someone wants to write into the database another routine is called that opens the database in read-write mode and updates the record. I open it this way:

Set DbWrite = DB.OpenRecordset("Names", dbOpenTable)
DbWrite.LockEdits = False

Any suggestions (other than upgrade to .NET or switch to RDO or whatever).

Thanks in advance!

--DB

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

It has been many years since I used DOA, but I think that an .Index (which isn't used until the .Seek) requires writing something... however, why on earth are you loading all the records from the table (and then indexing and searching them) just to find one particular record?

I would have used something like this instead:

On error goto ETrap
Set DbLookUp = DB.OpenRecordset("SELECT * FROM Names WHERE [Last] = 'SMITH'" , ???, dbReadOnly)
ETrap:
MsgBox "Error occured in " + Routine$ + "ERL: "+Str(Erl) + " Err: " + Str(Err) + " " + Error$

[Darkbob]

Thanks very much for your reply.

DAO doesn't work the same as SQL. The code in my example doesn't load any records at all while an SQL statement would load all matching records.

My code opens the table but no records are transferred at all - just a pointer in memory indicates the start of the table. The Seek moves to the first record that matches but doesn't load all matches.

But back to the question... why would I get the error I got on a read?

Thanks again.

--DB

[si_the_geek]

The code you showed does load records (they get transferred from the database to the provider/driver on your computer), it just doesn't display them.

The Seek then iterates thru the records in a similar way to what the database system would for an SQL statement.

Depending on various factors (such as which database system you are working with, etc), that could be dramatically slower than the method I showed... but then again it could also be faster.

An SQL statement like the one you provided would load up all the smith's and kill database performance.

So change the SQL statement to suit what you want... either by adding more conditions to the Where clause to ensure you get a particular record (rather than a potentially random one as with your code), or by adding something like a Top clause.

But back to the question... why would I get the error I got on a read?

As I said, I suspect the indexing/seeking.

If you don't get the error with my method, then that supports the theory.

[Darkbob]

The code you showed does load records (they get transferred from the database to the provider/driver on your computer), it just doesn't display them

First, thanks for your suggestion although I can't make use of it. I would have to recode miles of code and it's simply impossible at this point to do a total re-write to SQL.

Next, unless somehow this network has a terabit ethernet card and a few hundred gigs of RAM or something I'm unaware of, DAO doesn't work the way you think it does. I'm opening a table with well over a million entries and it opens in under a tenth of a second. There is no way those records are going to transfer to the local computer in that time.

--DB

[jggtz]

IMHO Darkbob is right about the data transfer when open a table using dao...

And about your question... may you post all the code from the OnError Goto ... until the line where you turn off the OnError and also the ETrap routine

The error clearly says that another user is using the table/record and this is locked
In cases like this what you should use is retry the Seek (using "Resume" in the ETrap routine)

[Darkbob]

Thanks... I do use the Resume. Actually I wrote a little routine to wait a second. I call that routine then I do a resume. If it fails again I repeat and I do that 5 times. If after 5 times the error is still present I have the program report and log the error.

I guess I could make it go longer... maybe 10 seconds? I just don't want the user to think the program has crashed and have them rebooting.

But the question remains the same. Why would you get a 3046 - which is a WRITE error (could not SAVE) - when you are doing a READ operation on a READ ONLY database?

It appears that the Microsoft description for the 3046 error is simply wrong and it applies to either a read or a write on a locked database table.

So now I've got to see who's locking the database and why.

I will make a few other changes to the program and see if I can get this thing going properly again.

Thanks for your help.

--DB

[dee-u]

Do you always encounter the error? In what instances does the error occurs? When 2 or more users are using your software or even with a single user only?

Have you tried compacting your database if it's Access? And have you tried si_the_geek's recommendation?