Jet Engine Error 3197 without Edit 
Author Message
 Jet Engine Error 3197 without Edit

I'm using VB3 and Jet 2.0 to implement a multiuser system which
involves servicing a queue.  Network "Clients" add requests (records)
into a table and a "Server" polls this table for new records and
services these requests.  There is also a "Queue Manager" which polls
the same table but it just displays the pending and completed requests
without doing anything to them.

Older versions* of this system have worked, but lately I'm getting two
occassional errors--"occassional" means one or the other occurs
approximate every 30 minutes or so with a client submitting requests 2
or 3 times per minute and the Server and Queue Manager each polling
every second.  Each error occurs just after a client has submitted a
request.

1)      A GPF occurs in MSAJT200.DLL when the Queue Manager performs
its update (poll) after a new request has been submitted.  The
statement which produces the GPF is:

    Set snapQueue = DB.CreateSnapshot("SELECT * FROM QUEUE ORDER BY
XID ASC")

As described, this occurs roughly 1% of the time, so it seems related
to the timing of the polling vs. the request subittal.

The Queue Manager operates by creating this snapshot on every
update--every second or two--and displaying its result in a listbox.

2)      Error 3197 --  Data has changed; operation stopped.

This error occurs in the Server when it identifies that a record
(request) has been added (through a successful .MoveFirst method on
the QUEUE table) and READS a field from that table.

The Server open the QUEUE table when it starts up and keeps it open.
It "polls" the table every one second by executing a .MoveFirst.  When
the queue is empty this produces a (trapped) No Current Record Error
and the server gives up until the next 1 second timer tick.

This is a piece of the code:

    tQueue.MoveFirst
    Do While Not tQueue.EOF
        nStatus = tQueue("STATUS")
        '
        'etc.
    Loop

The error comes from the nStatus = tQueue("STATUS") statement.

This is the description from VB's online help:
This error can occur in a multiuser environment.
Another user has changed the data you are trying to update.  This
error can occur in Visual Basic when multiple
users open a table or create a dynaset and use optimistic locking.
Between the time you used the Edit Method
method and the Update Method, another user changed the same data.
To overwrite the other user's changes with your own, execute the
Update method again.
<<

What I find strange is that this error is only supposed to occur on an
update, and no Edit or Update is being done here. Also, retrying the
statement (with a resume) doesn't help, and I'm not sure what is
necessary to get passed this problem.

I should mention that other data access/locking errors such as:

Error 3186 --  Couldn't save; currently locked by user 'Item2' on
machine 'Item1'.

are automatically retried up to 50 times add short (.2 second)
intervals so I generally don't see them.

*I mentioned that this has worked in the past.  There have been
various changes made since the last version, one of which has been
setting all tables (at client and in server) for Optimistic locking.
This was done to reduce contention/retries.

Somehow I suspect that these problems are related, and both involve
the polling application (Queue Manager or Server) polling the database
at just the WRONG time relative to the creation of the Client's
request.

Perhaps it should be noted that the Client's request is created within
a transaction block (BeginTrans ... CommitTrans) because the request
actual involves entries in two tables, the Queue table described here
and another, and the transaction block is intended to insure that the
Server never sees a semi-complete request.

SOMEBODY PLEASE HELP




Mon, 26 Jul 1999 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Error: 3197 Description: The Microsoft jet database engine ...

2. 3197 Microsoft Jet database engine

3. Puzzling Error - 3197 - Jet Stopped Process?

4. Error 3197, the jet database stopped

5. error using recordset.edit method (3197)

6. recordset.edit method won't work (error 3197)

7. Storing and editing paintbrush pics in Jet engine

8. Reading/Writing .DBF files without the JET Engine

9. Error 3197

10. Error 3197?

11. Run-Time Error 3197 - please help its driving me insane

12. Error - 3197 - Please help

 

 
Powered by phpBB® Forum Software