Solved

Access VBA ADO .Edit Causes Error 3197 that Continues Successfully

Posted on 2014-01-20
12
1,072 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
  • 7
  • 5
12 Comments
 
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.
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 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.
0
 

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 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).
0
 

Author Comment

by:WilbertWaterbury
ID: 39797612
Thank you for the other excellent suggestions. I will work on these and report back.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Accepted Solution

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now