?
Solved

Access VBA ADO .Edit Causes Error 3197 that Continues Successfully

Posted on 2014-01-20
12
Medium Priority
?
1,241 Views
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.
0
Comment
Question by:WilbertWaterbury
[X]
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
12 Comments
 
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.
0
 

Author Comment

by:WilbertWaterbury
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.
0
 
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.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:WilbertWaterbury
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?
0
 
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).
0
 

Author Comment

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

Author Comment

by:WilbertWaterbury
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.
0
 
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.
0
 

Author Comment

by:WilbertWaterbury
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.
0
 

Author Comment

by:WilbertWaterbury
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.
Error-3040.jpg
0
 
LVL 85

Accepted Solution

by:
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?
0
 

Author Comment

by:WilbertWaterbury
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")
'rsOrdersAppend.Execute
'rsOrdersAppend.Close
'Set rsOrdersAppend = Nothing

To this...

CurrentDb.Execute "qryAppendOrders"

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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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: …
Suggested Courses

752 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