Microsoft Jet Database Engine

[opostal]

I have a database that opens and runs a few queries such as delete query, append query etc to make things easy for the user. The database is accessed by a link from an internal company website. The problem I have is that sometimes (fairly often in fact) I get an error that says "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." This is not the case however as I get this same error when I copy the database to a location where only I can use the data, when I isolate the database to a single user limitation and a variety of other options. The only thing I can think of is that since I open with several queries, maybe the database is trying to do two things at once with the same data? I use a macro to perform these non stop queries prior to the user gaining access to the data.

Here is the last piece to the puzzle. If I get that error, I simply close the database down and hit the link again and it works beautifully.

Anyone have any ideas on this?

 

[The_Doc_Man]

I am reminded of the advice from the book, "Everything I Need to Know I Learned in Kindergarten."

When you are done: If you opened it, close it. If you took it out, put it back.

This happens when you have VBA code that opens a recordset behind the scenes and it does not close the recordset. It can also happen if you have logging or auditing VBA code that makes entries in a particular history table and two different events could trigger the same kind of log. If you are adding log entries, by necessity they will be in the same disk buffer of your Access file, so would conflict with each other.

Also, look for queries that are opened in normal mode when they could be opened in read-only mode, and ditto for recordsets in VBA sequences. In the latter case, use a dynaset and tell it to use optimistic locking in that case. Might also be a good idea to make all forms use QUERIES (that happen to identically match the underlying single table) rather than trying to directly open the tables from the form.

Without more information all I can offer is what I just offered - some general ways to clean up the condition you just named.

 

[opostal]

Well I believed that would probably be the problem (opening and not closing). I have been unable to find the source of this problem. Let me provide the process and maybe you can suggest a likely source. I have a table that is regularly updated and there is extra lettering in each field due to the software that adds the information to this table. I have a startup macro that goes in and clears an empty table, copies the original table to this temporary table, removes the unneccessary lettering and then is ready for searching by the user. When the user is finished, they close the forms and leave the database. I do not have any "close" instructions in there other than an actual "exit" button.

With all that being said, the database opens, provides the jet dbase engine error and is closed. When reopened, it works beautifully. Thanks again and I will continue to poke and prod to find my error.

 

[vbaInet]

I think The Doc Man was referring to you closing any objects like querydefs, recordsets etc that you're using to connect to an object in the other database. When you:

Dim rst as Recordset
set rst = Currentdb.openrecordset("...")

After you're done with it, close it:

rst.Close
set rst = Nothing

For each time you open it, close it.

Also, do you allow this autoexec macro run before you try to automate the database!

 

[The_Doc_Man]

ANYTHING that you have to define with a SET statement is going to have the problem one way or another. But if the SET xxx(object) = xxxx.Open(something or another) syntax applies, you pretty much lock in the reference so that it cannot let go until you close it.

vbaInet is right in one sense, wrong in another. If it is described as a collection and you reference it or any member thereof, you have the potential for this problem. So it is more than just recordsets, querydefs, etc.

Normally, access correctly handles cases like opening and closing a pre-defined query from the query pane of the database window. But if you open the query from code or a macro, there is a risk that you might leave it open inappropriately.

Any instantiation will also do this (set xxx = new yyy, whatever the syntax happens to be.)

 

[opostal]

Excellent. I believe I have found the source of my problem as was stated above by the use of a macro. If I individually perform the steps, no problem. When done by macro, there is not a close statment on it.

Thanks again!