Run-time error ‘3197’ (1 Viewer)

I recently shifted all my back end tables of Access to Microsoft SQL Server 2005 keeping front end as is i.e. MS Access. I re-set all tables with a primary key as this is required by each Server table. I used ODBC linked tbl method.

Some table are running without any problem but my sales header and sales footer table don’t run smoothly. I have yes / no type fields in both tables. Initially they also produced problems but I managed to solve it. Now every code line works ok but after opening record set and manipulation, it wont update records and displays below error.

Run-time error ‘3197’

The Microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

And halts at below line

rst.Update

I don’t have any other user as SQL Server 2005 is installed on my laptop and sa is the only user.

Can you let me know how to come over this problem?

Also I am planning to set one separate high configuration pc for server and like to make different users in future. I hope in that case I have to set the permissions for work stations from server itself and hopefully the above error will not appear then.

But not it appears on my laptop.

Any idea?

Thanks in advance

Ashfaque

Is there a form open bound to the same table the recordset is updating?

are you sure YOU dont have 2 different forms open, both showing the same record

No. Not at all.

I have just entered in the db and run only Sales form which include a subform. No other form is opened that related with this sales main tbl or footer table.

Thanks,
Ashfaque

Is that an answer to Dave's question or mine? If the recordset is updating the same table the form is bound to, they can give you that error. Use a recordset or a bound form, not both.

Hi Paul,

My header form is unbound and footer form is bound to table where my data will save in T_SalesInvHead and T_SalesInvFoot tbls respectively. But while storing my data to these tables I am storing some other information to some other table called T_SalesInvoice and for which I used I wrote below code:

Set rst = CurrentDb.OpenRecordset("Select * From T_SalesInvoice where invnum=" & (InvNum) & "")
If rst.RecordCount >= 1 Then
OldPay = rst!PAmount
End If
rst.Close
Set rst = Nothing

And after some other code lines that uses the same table (T_SalesInvoice)

DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_DEL_SalesInvFoot"
DoCmd.OpenQuery "Q_DEL_SalesInvoice"
DoCmd.OpenQuery "Q_App_SalesInvoice"
DoCmd.SetWarnings True.....

Then I have below code :

Set rst = CurrentDb.OpenRecordset("Select * From T_SalesInvoice where invnum=" & (InvNum) & "")
If rst.RecordCount >= 1 Then
rst.Edit
rst!Amount = Round(TxtTotAmt, 2)
rst!Balance = rst!Amount - OldPay
rst!PAmount = OldPay

rst.Update
End If
rst.Close

Here it produce said error.

I used rst twice but I closed it after its first use.

Please advise.

what can happen is that

a) in your form you edit something - you can see this, if you include record selectors, because you will see a pencil instead of the black triangle indicating an edited record.

b) in code, you THEN try to edit the same record
(or you popup a form and try to edit something - its the same thing)

now, the second update cannot complete, because the record is already "edited", and then you get the "another user edited this record ...."

this sounds like that is what is happening.

Hi!
I think it would be better if use DoEvents after your queries:
which yeilds the execution untill all the proccess get finished.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_DEL_SalesInvFoot"
DoCmd.OpenQuery "Q_DEL_SalesInvoice"
DoCmd.OpenQuery "Q_App_SalesInvoice"
DoCmd.SetWarnings True.....
DoEvents