MultiUser Issues with Access2002 - Errors 3343, Invalid SQL,214721

[Access57]

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!

 

[SteveM]

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]

Steve,

First - Thanks for the reply, I appreciate it.
Second - no wi-fi, just a Gigabyte LAN Network.
Third, - I do the automatic C&R on exit for the FE, so it does it pretty
frequently (and I typically make a copy about once an hour when I am
programming). It is a little more difficult on the BE - it is supposed to be
automatic every night at 3:30 - but definitely once every weekend.

You make a good point - I need to determine exactly where the error is
occuring. I am not familiar with CurrentDB.Execute & dbFailOnError - but I
am going to give it a try along with the decompile and will let you know the
results.

Geof

 

[Access57]

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,

 

[Richard_doing_2007]

Once during a 97 into 2000 conversion I saw corruption simliar to this and
after 3 weeks of intense testing and enhancing the error routines the whole
issue was realized and fixed. I discovered that it was due to that the
sysadmin had changed the virus scanning software to "[x] scan network
drives"; ambitious fellow I recall - <splinky>.

My advise - look far and wide - not as much at Access but other sources.

 

[Access57]

Richard,

Thanks - it was a long weekend and all of my attempts have danced around the
problem, so you offer some good advice that I am taking into account.


--
Access57

Once during a 97 into 2000 conversion I saw corruption simliar to this and
after 3 weeks of intense testing and enhancing the error routines the whole
issue was realized and fixed. I discovered that it was due to that the
sysadmin had changed the virus scanning software to "[x] scan network
drives"; ambitious fellow I recall - <splinky>.

My advise - look far and wide - not as much at Access but other sources.

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,

 

[Lythandra]

I've had weird problems in the past like that before where sometimes
everything would work and sometimes not. Very annoying and pretty much
untraceable.

The only way I got everything to work was to make a new empty database BE
and import in all the info in new tables (where fields were the exact same).
Also check in the tables if you able (due to size) and see if there are any
corrupt records that "Compact and Repair" just will not fix. It appears for
me as squares in the field instead of data. Manually deleting this line item
has fixed weird problems before.

Perhaps not the answer to your problem but it has worked for me in the past.

Richard,

Thanks - it was a long weekend and all of my attempts have danced around the
problem, so you offer some good advice that I am taking into account.


--
Access57

Once during a 97 into 2000 conversion I saw corruption simliar to this and
after 3 weeks of intense testing and enhancing the error routines the whole
issue was realized and fixed. I discovered that it was due to that the
sysadmin had changed the virus scanning software to "[x] scan network
drives"; ambitious fellow I recall - <splinky>.

My advise - look far and wide - not as much at Access but other sources.

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


:

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

:

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]

I have tried taking a brand new BE file and imported all the tables with no
visible results. Then I basically recreated the tables from scratch and
added limited data and had much better results - but before I was able to
complete my testing we recieved an Executive decision to go with a solution
that fixed the symptom.

When I have the chance I am going to come back to this.

Also, I found MS KB article 238401 which sounds exactly like my problem -
but I incorporated the solution several years ago (primarily referencing DAO
3.6 Object Library). So I think that Steve has a good suggestion with the
CurrentDB.Execute and I am trying to incorporate that into the program as
well. And I think that there may be some corruption in there somewhere.

Thanks,


--
Access57

I've had weird problems in the past like that before where sometimes
everything would work and sometimes not. Very annoying and pretty much
untraceable.

The only way I got everything to work was to make a new empty database BE
and import in all the info in new tables (where fields were the exact same).
Also check in the tables if you able (due to size) and see if there are any
corrupt records that "Compact and Repair" just will not fix. It appears for
me as squares in the field instead of data. Manually deleting this line item
has fixed weird problems before.

Perhaps not the answer to your problem but it has worked for me in the past.

Richard,

Thanks - it was a long weekend and all of my attempts have danced around the
problem, so you offer some good advice that I am taking into account.


--
Access57

Once during a 97 into 2000 conversion I saw corruption simliar to this and
after 3 weeks of intense testing and enhancing the error routines the whole
issue was realized and fixed. I discovered that it was due to that the
sysadmin had changed the virus scanning software to "[x] scan network
drives"; ambitious fellow I recall - <splinky>.

My advise - look far and wide - not as much at Access but other sources.


:

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


:

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

:

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!