RE: MultiUser Issues with Access2002 - Errors 3343, Invalid SQL,21

Tech-Archive recommends: Fix windows errors by optimizing your registry

Steve,

I am not sure how to take advantage of the currentDB.execute - but I agree
that the file may be corrupt.

So I spent the day making a test network and running through the test data
several times and I received a variety of errors in "random" places (I have
never received an error twice in the same place).

I am getting errors on DMin, DMax, DLookup, docmd.openquery,
docmd.openreport, opening an ADO Recordset, looping through an ADO Recordset
and the errors are always "unrecognized database format", "already opened
exclusively by another user", "Invalid SQL Statement", "too few parameters",
"must use updateable query", etc.

It seems that the data gets out of sync when there are multiple users
accessing the same table (but different records).

So I am creating a new 2002 data.mdb and import / copy the data into it and
see it that works (if not, then I will recreate the FE also)

Thanks,
--
Access57


"SteveM" wrote:

These things are always difficult to determine when you can't reliably
reproduce the problem.

You don't have any users on WiFi do you?
I would replace all your DoCmd.RunSQL lines with CurrentDb.Execute (or
substitute CurrentDb if not the current database). You can use dbFailOnError
with this.

May be worthwhile doing a decompile on your FE.
Have you doen a compact and repair on your BE lately?
I'd be worried that some of these problems are causing corruption in my BE.

Steve

"Access57" wrote:

Program runs great all day in single user mode with absolutely no errors but
is very "unstable" in multiuser mode because it will occasionally get error
messages when two users apparently access same table (but different records)

Usually receive one of three Error messages:
predominantly 3343 - Unrecognized database format (which occurs when Access
does not write to the database properly); or
Invalid SQL String, Expecting SELECT, UPDATE, etc. 2147217900 or 7904 error.

I am confused because I am not getting 3186 / 3187 / 3188 / 3189 type
messages about record locking problems - it seems to have trouble
communicating correctly.
(Also, the error messages are not "accurate" because it does recognize the
database the rest of the time and it runs the same SQL commands with no
problems - i.e. the error messages are about the symptoms, not the real
problem.)

The errors appear random - that is to say, they occur in many different
places of the program and the error never repeats at same place, even when
using the same data (using a "Backup" copy), but it is always appears to be
when a second user tries to write to a table that was recently accessed by
the first user.

Program was originally written in 1994 (Access 2.0) then upgraded to 95,
then 97, then 2000, then Access 2002 format. I "Open database with Record
Level Locking". I am using a "data.mdb" with tables / backend sitting on a
Win XP SP2 computer with about 10 Win XP SP2 LAN computers running local
"front end" copies of the "program.mdb" in runtime mode as the front end on a
Gigabit LAN (192.168.20.XX)

When I run simultaneous sessions (multiuser test & checkout) I will get
about two errors in ten tries (that is to say, it works correctly the other
eight times). If I repeat with same data (a "fresh" copy), I will get about
the same number of errors but in different locations - so the data and
program will work properly, but it seems to depend on the timing of when the
two users hit the backend.

So I believe my code is good (which is ADO recordsets, some docmd.runSQL,
and alot of append queries to temp tables for reference purposes) but I must
be missing some fundamental element necessary for multiuser environment.

Any ideas of what I am missing? Thanks for your interest and any help you
can offer!

--
Access57
.

Relevant Pages

  • RE: MultiUser Issues with Access2002 - Errors 3343, Invalid SQL,21
    ... The only way I got everything to work was to make a new empty database BE ... docmd.openreport, opening an ADO Recordset, looping through an ADO Recordset ... Usually receive one of three Error messages: ... When I run simultaneous sessions (multiuser test & checkout) I will get ...
    (microsoft.public.access.modulesdaovba)
  • RE: MultiUser Issues with Access2002 - Errors 3343, Invalid SQL,21
    ... docmd.openreport, opening an ADO Recordset, looping through an ADO Recordset ... substitute CurrentDb if not the current database). ... Usually receive one of three Error messages: ... When I run simultaneous sessions (multiuser test & checkout) I will get ...
    (microsoft.public.access.modulesdaovba)
  • RE: MultiUser Issues with Access2002 - Errors 3343, Invalid SQL,21
    ... docmd.openreport, opening an ADO Recordset, looping through an ADO Recordset ... substitute CurrentDb if not the current database). ... I'd be worried that some of these problems are causing corruption in my BE. ... Usually receive one of three Error messages: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Cant open Local Security Policy database
    ... My guess is that your secedit.sdb may be corrupt ... The security database is corrupted. ... Run esentutl /g to check the integrity of the security database at ... > Not error messages related to this are logged in evet log. ...
    (microsoft.public.win2000.security)
  • Re: Wierd error message, Please help cant access database
    ... Your database is corrupt and may or may not be salvagable. ... collection of the TableDef object to determine the valid index names." ... Jet Database Engine 3.x Error Messages Due to Corruption - 182867. ...
    (microsoft.public.access.forms)