Access VBA ADO .Edit Causes Error 3197 that Continues Successfully

Posted on 2014-01-20
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
  • 7
  • 5
LVL 84
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 84
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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


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 84
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 84
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 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Technology Partners: 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!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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