Writing database problem Please help

[vijithamar]

My Vb6 code to write different tables in database
is like this ( i am using Access 97 Database )

Private Sub WriteBill()
on error goto errmsg
DBEngine.BeginTrans
DBEngine.Idle dbRefreshCache

Set rssysrec = Ab2.OpenRecordset("select * from invsystem", dbOpenDynaset)
rssysrec.Edit
rssysrec!invnoca = rssysrec!invnoca + 1
inum = rssysrec!invnoca
rssysrec.Update

For i=1 to n
'checking for available stock
Set tmprs = Ab2.OpenRecordset("Select product.cmcode,batch.brate from batch,product..... [Opening a Recorset]
'updataing stock (Deducting)
Ab2.Execute "update batch, product .... [two table is updating]
'Writing Transaction table1
Ab2.Execute "insert into invmonthly.... [Appending records to a table]

Next i
'Writing Transaction table2
Ab2.Execute insert into invtotal.... [Appending records to a table]
DBEngine.CommitTrans dbFlushOSCacheWrites
DBEngine.Idle dbRefreshCache

Exit Sub
Errmsg:
Errormsg
End Sub


Ab2 is a global database type variable
it is a multi-user busy retail billing system (10 user)

the problem occurs very rarely

the problem is INSERT INTO statements are not inserting the records to the tables
update command is working fine

in result The product stock quantity deducting but there is no transaction records found


the problem occurs very very rarely, say one transaction out of 1000

i checked allow zero length,default values,validations of every field all are ok

system working in win98

hope anybody will give me a solution for this problem

Thanks

 

[rdroske]

I don't see a Rollback statement. Is it in ErrorMsg?

 

[vijithamar]

Rollback stastement is there before Errormsg in ErrMsg Section. But i am not shown in the example.

 

[rdroske]

Well it must be something else you are not showing then (or something that is happening somewhere else in the code)

Whats n for example? Is this occasionally going into a loop and forcing people to kill the program?

You could implement some sort of tracking mechanism where a record was inserted into a new table each time this transaction started and stopped (one way or the other) and then analyze that for Begins without End or Rollback....

 

[vijithamar]

there is no chance of infinite loop. because the process
is completing with out any error, and next procedure is running (That is for printing the bill).

 

[strongm]

Loathe though I am to say it, this is what comes of writing busy, 10-user multiuser systems in a database product that wasn't really designed for that on an OS that wasn't designed for it...

 

[rdroske]

Yes, most likely what is happening is that something is very slow and users are killing the process (try to get one to admit it though...).

Its not some mysterious bug in the DBengine or Access and the code looks fine (at least what was posted).

 

[vijithamar]

My problem is how to reproduce the error in design mode

 

[rdroske]

That was why I suggested the trace database...

You never will reproduce it in a single user clean environment.

 

[vijithamar]

Thanks rdroske & strongm

Pleace explain me how to trace the database

 

[rdroske]

You add a new table and put instructions in your code to write records to it at various points.

Here I would put one right after the begin Trans and before the commit trans and rollback for starters. "Begin Trans", time, workstation say.

Then next time it happens look to see if you have a Begin trans record without a commit or rollback, watch any rollbacks etc.

 

[vijithamar]

Thank you rdroske
I will try that
Thank you