Access VBA ADO .Edit Causes Error 3197 that Continues Successfully

Posted on 2014-01-20
Medium Priority
Last Modified: 2014-01-24
I confirm only one user on the network (no lock file - plus no one else ever runs the report) and the Access database throws an error 3197 on an ADO .Edit statement.  JUST CLICKING START BUTTON ALLOWS THE PROGRAM TO CONTINUE TO A NORMAL FINISH. So the problem is just that the program stops with the bogus error 3197 but resumes without incident.

The code hasn't changed, but I am running Access 2010 now whereas I was running 2007. Also, a new Windows 2012 file server. The same domain user can run this successfully on the file server directly.

Nothing logged in the event log for either application or system on the server or the workstation.

I would appreciate ideas what could cause this.
Question by:WilbertWaterbury
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
LVL 85
ID: 39795819
Assuming you're referring to an ADO Recordset, there is no "Edit" property. Perhaps you mean a DAO recordset, which DOES have an "Edit" property.

The same domain user can run this successfully on the file server directly.
Might have a corrupt profile on the local machine. Try logging in as a different user on the problem machine and see if the same thing occurs. If the same thing occurs, you may have troubles with the installation of Access/Office, or Windows (or ADO/DAO). Be sure the machine is fully up to date.

Next, try logging in on a different machine as the problem user. If the problems follow that user, then you may have a corrupt user profile (although that's doubtful, since you can log in as that user directly on the server and run things normally).

Finally, check folder permissions - users require at least Modify permissions on the folder hosting the backend database. I know it works on the server but trust me ... check the permissions to be sure.

Author Comment

ID: 39795837
Thanks for the response.

You are correct. I meant DAO.

I can recreate this with another user on another workstation similar environment W7 MO 2010. So its doubtful a profile corruption.

Users have Full rights to the folder on the server.
LVL 85
ID: 39795886
no lock file
There's always a lock file, even with one user. Can you look in the backend folder again?

If you have the database open, and you do NOT see a lockfile, then it's almost certainly a permissions issue. Access will create the lockfile somewhere. If it can't create it in the same folder as the .mdb/.accdb file, it'll try various other locations (including the user's local machine). Locate that file and delete it, and then figure out why that user could not write to the backend location (hint: it'll be a permissions issue).

Are users sharing the FrontEnd file (the one with the Forms, Reports, etc)? Or do they each have a COPY of the file installed on their desktops? Shared FE files can also cause this.

Also, this error can be caused by attempting to edit the same record as is being shown on a form (or report, for that matter). Unless you have a very good reason to use the DAO edit method in a bound form, you should not do so.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39796029
Thanks again for the follow-up. There is no lock file after I exit the database. Which is to settle the issue that there is only one user. Me.

There is no issue to create files in the same folder. I have full access. The .ldb file is created while I have the database open.

There are no changes to the database. I can copy a new back from the previous system. Only the local MS Access and 2012 Windows server appear to be different.

There is no multiple accesses to the same table. One opens the database and a autoexec form displays a few buttons (no connection to a table in the form). A button initiates the VBA code.

I hear what your saying. You believe the error is not bogus (the database is small < 200 KB so there are no approaching 2 GB funny stuff). Is there a way to trace access to tables in the database?
LVL 85
ID: 39796542
Next thought would be corruption. Have you Compacted the database lately (both front and back ends)? Try these steps, after making backups:

1) Compact the database
2) Compile the database - from the VBA Editor, click Debug - Compile. Fix any errors, and continue doing this until the menuitem is disabled.
3) Compact again.
4) Decompile the database: Create a shortcut with a target like this, and then "run" the shortcut: "full path to msacess.exe" "full path to your db" /decompile
5) Now compact again

Sometimes it helps to move everything over to a new, blank database.

Also, be sure that Office and Windows are fully up to data on the machines (including the Server).

Author Comment

ID: 39797612
Thank you for the other excellent suggestions. I will work on these and report back.

Author Comment

ID: 39801783
I tried your steps 1-5 without success. Error still occurred.

Next I tried copying everything to a new blank database and it worked without the error. HOWEVER, although it worked 3 times in a row, after logging off and turning it back over to the user - it immediately began happening  again.

I had marked the newly rebuilt startup form ©2014 so I new that the new version was being used. I was also now able to recreate the error again. Before testing, I double checked that no one had the file open (i.e. no .ldb file and the .mdb file not open to any user on the file server as seen from the server's Computer Management control panel)

Is there a clue in the fact that the steps I took to rebuild the database caused it stop happening, but that the success only lasted for the session until logoff? This is driving me nuts.
LVL 85
ID: 39801808
It's possible you have a corrupt object (like a Form). You can recreate the form by creating a new form and copying the controls over, then copy the code over as well. It's also possible that you have a corrupt control on the form, although that's quite far-fetched.

How do your users connect to the system? Are they running on local LAN system (i.e. in the same office), or are they using WAN services, like a VPN?

I don't really see where you've answered this:
Are users sharing the FrontEnd file (the one with the Forms, Reports, etc)? Or do they each have a COPY of the file installed on their desktops? Shared FE files can also cause this.
This is one of the more common causes of corruption - trying to run a shared FE across a network.

Author Comment

ID: 39801855
I can't thank you enough for your interest.

Forms do not appear to be related to the error. I can delete the forms and run the code via macro and get the same error.

The .mdb file is on the local C drive. It is not shared.

If I set a break point AFTER the failing .Edit statement, execution proceeds through the .Edit and stops. If the break point is removed and execution resumed, the program does not fail.

The database is a front end only in the sense that it copies an order and inventory tables from another database from a network share before beginning the process that fails.  A local table is used to analyze the data coped from the back end. The server shows that the backend database has been released after the error is encountered.

Author Comment

ID: 39801862
UNC's are used to reference the back end database.

I also sometimes get error  3040 on the same .Edit statement instead of the original message - (attached). Nothing is logged in the Event Manager on the workstation or the server.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39803661
it copies an order and inventory tables from another database from a network share before beginning the process that fails.
How is that done? Does the code copy the tables, or is this something done manually?

The new error would seem to indicate something along the lines of a failed copy, or a copy that is still in process. If your code copies those files, you might try insuring that the copy has completed before moving forward in the process. I'm not sure how you'd do that, however. Perhaps you could query the table in the remote database BEFORE the copy to determine the number of records, and then query the table AFTER the copy to insure it has the same number? Or perhaps you could introduce a loop that cycles for a few seconds or so before allowing the process to continue?

Author Comment

ID: 39808246
Inserting a loop did work to avoid the error which led me to looking at the query that created the table on which the .edit method fails.

Changing the way that this query was invoked resolved the error without the above delay loop.

So changing this...

'Set rsOrdersAppend = db.QueryDefs("qryAppendOrders")
'Set rsOrdersAppend = Nothing

To this...

CurrentDb.Execute "qryAppendOrders"

also resolved the issue. I wonder what's the difference.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question