Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-28-2019, 02:17 PM   #1
olmie75
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
olmie75 is on a distinguished road
Error 3197 in bound form / linked to SQL Server back end

I am using Access (Office 365, version 16.0), connecting to linked tables in SQL Server (ODBC DSN Type = SQL Server Native Client 11.0).

I basically have 3 tables, each using it's own bound form: ApplicantData, EnrolleeData, and PostSeparation data. The data source is simply Select * from [Table].

Applicant data form works just fine. I can add new records, and then go back into records and update existing records.

In EnrolleeData and PostSeparationData, I am able to add a new record, however, when I attempt to update an existing record, I get an error message ("Run-time error 3197 Microsoft Access Database Engine Stopped the process because you and another user are attempting to change the same data at the same time"). This error occurs on simple VBA code (DoCmd.RunCommand acCmdSaveRecord) on click event of "Save" button.

It's especially bizarre that this works fine on ApplicantData data, but not in the other two tables. I've tried different ODBC drivers, checked / tried different values for various properties settings in form (record locks, etc), and I've even tried removing the foreign key constraints in SQL and re-linking the tables ... this is the only difference between EnrolleeData and PostSeparationData (vs. ApplicantData) - both of these tables have a foreign key constraint to ApplicantData in SQL Server (but removing those constraints still didn't fix the issue).

I'm using bound forms, some vba to handle behavior of controls (e.g. if this value here, enable that control), but not doing anything related to recordsets in the code (other than DoCmd.RunCommand acCmdSaveRecord)

Can anyone please help me? Any ideas on what could be causing this, or what else to try? Is there any way in VBA code to remove a record lock?

olmie75 is offline   Reply With Quote
Old 09-28-2019, 02:48 PM   #2
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,312
Thanks: 11
Thanked 719 Times in 669 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Error 3197 in bound form / linked to SQL Server back end

Do the Tables tied to the errant Forms both have Primary Keys?

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 09-28-2019, 04:10 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,655
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Error 3197 in bound form / linked to SQL Server back end

Do you have any bit columns in the SQL server tables?

They must have Nulls not allowed and a default value set or you will get that message when using through Access.

Galaxiom is offline   Reply With Quote
Old 09-28-2019, 05:05 PM   #4
olmie75
Newly Registered User
 
Join Date: Sep 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
olmie75 is on a distinguished road
Re: Error 3197 in bound form / linked to SQL Server back end

That worked!!! Thank you so much ... I have no idea how I would have ever figured that out if not for your help.

olmie75 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
With SQL Server back end, call failed error Margarita General 4 02-12-2013 12:41 PM
SQL Server Backend: linked tables or no more bound forms ? byterbit SQL Server 2 03-21-2011 10:38 AM
linked server to Access 2003: error angusbeare SQL Server 0 12-16-2009 07:52 AM
Access linked SQL Server ADO error casey SQL Server 5 02-27-2007 07:28 AM
Adobe - Bound Object - OLE Server Error TJBernard General 5 11-15-2006 10:16 AM




All times are GMT -8. The time now is 03:23 PM.


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

Featured Forum post


Sponsored Links


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