Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 03-26-2010, 06:19 AM   #1
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 482
Thanks: 4
Thanked 13 Times in 13 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Run-time error 3197

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

Ashfaque is offline   Reply With Quote
Old 03-26-2010, 07:16 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,089
Thanks: 10
Thanked 3,874 Times in 3,817 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time error 3197

Is there a form open bound to the same table the recordset is updating?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-26-2010, 11:23 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Run-time error 3197

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

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 03-27-2010, 06:02 AM   #4
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 482
Thanks: 4
Thanked 13 Times in 13 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Run-time error 3197

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
Ashfaque is offline   Reply With Quote
Old 03-27-2010, 11:18 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,089
Thanks: 10
Thanked 3,874 Times in 3,817 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time error 3197

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-28-2010, 01:28 AM   #6
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 482
Thanks: 4
Thanked 13 Times in 13 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Run-time error 3197

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.
Ashfaque is offline   Reply With Quote
Old 03-28-2010, 03:36 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Run-time error 3197

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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 03-30-2010, 12:14 AM   #8
Khalid_Afridi
Newly Registered User
 
Khalid_Afridi's Avatar
 
Join Date: Jan 2009
Location: Kuwait
Posts: 492
Thanks: 1
Thanked 11 Times in 11 Posts
Khalid_Afridi is on a distinguished road
Re: Run-time error 3197

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

__________________
Best Regards,
Khalid

if this helped, please click the scales at the top right of this posting.
Best wishes.
Khalid_Afridi is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Working with Date and time fields wjburke2 Forms 10 01-04-2011 07:11 AM
Range Global error if run same code second time Rx_ Modules & VBA 2 03-24-2010 08:45 AM
Run time error '13' Type mismatch alant37 Modules & VBA 1 06-22-2005 06:32 AM
Run Time Error - No Value Given RichardH Modules & VBA 3 06-16-2005 04:17 AM




All times are GMT -8. The time now is 08:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World