Access VBA ADO .Edit Causes Error 3197 that Continues Successfully

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
WilbertWaterburyAuthor Commented:
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
WilbertWaterburyAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
WilbertWaterburyAuthor Commented:
Thank you for the other excellent suggestions. I will work on these and report back.
WilbertWaterburyAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
WilbertWaterburyAuthor Commented:
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.
WilbertWaterburyAuthor Commented:
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.
WilbertWaterburyAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.