ms access 3734 error

michaelmccomb
michaelmccomb used Ask the Experts™
on
I am getting this error.  The MS folks say not to open the db too many times which would be fine if I hadn't already started getting the error but I have.  I didn't know that one COULD open an Access db too many times until i saw and investigated the 3734.  Running compact repair does not correct the error.  Anyone have any ideas?  Please do not point me to MS as they do not provide a solution to this problem but rather tell you how to prevent it.  As I say, I've already got it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Have you shared this database?
Have you split this database to BE and FE?

Commented:
This error is normally associated to the Database been in Exclusive Design mode. Meaning someone has opened it and taken control of it exclusively to do some design modifications, while in this mode no one can normally do anything to the database. This happens someone times when someone is changing a Report or Table structure. (Basically when the Database needs No One to be connected while it has to write a system "setting" or Change)

So I would make sure no one has open it in this Mode. The Next thing is if no one is opening it in this mode what Code has been written that is doing this.

There is a Solution. Depending on what version of Access yo are using.

Author

Commented:
the db is normally a  shared backend... this is a development backend db and therefore no one is sharing it until all of the pointers are moved to the LIVE backend...

no hanging ldbs are present, db has been open/compacted/repaired several times
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
this is a 2003 backend, both the front end and the back end display this error...  i accept that it may be corrupted in some way but does anyone know how to fix it....  why would opening a db "too many times" corrupt it?

Commented:
To be honest this would be the first time I have heard that Excuse.

Commented:
create new database and import all the object to the new database

Author

Commented:
excuse???

Author

Commented:
the "too many times too quickly" comes from MS i believe....  i am supposed to talk to my administrator about it of course.  (same old cop out)
Commented:
try todecompile and recompile the db. this may help  

decompile: in run: msaccess "dbPathName.mdb" /decompile
   Compact/repair:  in run: msaccess "dbPathName.mdb" /compact
   compile: in VB editor: debug> compile
   Compact/repair

see this
   http://www.granite.ab.ca/access/decompile.htm

Author

Commented:
i have been opening the back end alot as i play with a sort of job executions stack.  Table in the back end gets populated by a front end with a job name.  The back runs a form which loops continuously until it gets a procedure name from table (one of it's own).  At that point it executes the procedure and as a final step deletes the procedure from the table before heading back into the loop again.  I intend the thing just to run all of the time.  The back end  also has a table where it stores what procedures were run, at what time, how long it took and by what user.  Poor man's back end script.

We like Access because of it's RAD capabilities.  Run two medical clinics, dental clinc, legal clinic and a couple of other 501c3 activities with it.  It is highly customized to the point where no commercially available product can even come close to providing all of the functionality.  Quite stable and plenty fast enough for us even though it is pretty heavily loaded.  Running certain processes such as physician schedule regeneration on the server would be useful because it goes so much more quickly.  We generate a half year of openings for 30 different professionals based on individualize scheduling requirements in what takes about 10-12 seconds on the server when doing it with this batch approach.

We've looked at OpenOffice, Kexi and the like because they claim to be Access like but in truth none of them even remotely approach what Access can do.  Sure wish I could find a system like Access but so far no luck.  It seems that ALL other development environments become very disjointed at some point.

Not certain how much VB code we have in these apps.  Probably 100k+ lines built up over two or three years now.

Like I say it all works great but I am a bit disappointed to suddenly and out of the blue be getting a 3734 error.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Have you by chance recently installed Acrobat ... and has Acrobat added new tool bars to the menu?

In the past, there was a bug regarding this ... wherein the Acrobat tool bar is actually added on the fly each time the mdb is opened.  Sooo ... in a multi-user environment, this created a problem ... because the first user to open a FE for 'the day' so to speak ... Access considered this a design change ... thus, that user (1st) had actually put the mdb in the exclusive mode unknowingly, and subsequent users would get that error when attempting to load their FE.  

However, in a split FE/BE scenario wherein *each* user has a separate copy of the FE on their workstation - this would be a non-issue.

So ... just something to checkout.  And this is not necessarily limited to the Adobe tool bars.

mx

Author

Commented:
Yes, every time a user signs on they get a new copy of the front end.  Or rather i use robocopy for this and IF it is newer they get the newer version.

that decomplie trick did shrink the front end by about 15%...  i have never come across that before and would have assumed that is what the compact/repair option was doing.  Did nothing for the back end as expected but i did give it a try.  Thanks for the tip.  Not certain about what improvement in speed this might yield but will be looking for it with users tomorrow.

Author

Commented:
we have out in the acrobat 9 several places so i will watch for problems created by that... they do get their own front ends
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Or rather i use robocopy for this and IF it is newer they get the newer version."

I would suggest leaving it the way you have it now .. *always* load a copy of the master FE.  If an existing copy of the FE gets corrupted locally (even if the user does not know this) ... and you only load a new FE IF ... the version number is higher, then the user never gets a 'fresh', uncorrupted copy.   Trust me ... I've been using this approach with my database loader - which currently manages 20 mdbs across 100 users ... and it's a bullet proof system.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Decompile does not affect tables in any way.  It only affects vba code.  Doing decompile on an mdb with only tables will have no effect.

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Just so we are clear...

Are you opening multiple *instances* of the same database?

Author

Commented:
yes,  i am accessing the BE with the same version but multiple copies of the FE running on different work stations...  i am also wishing to run one form (a continuous loop) on the BE which also seems to work much of the time
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<"yes">
Well no, actually when the word "Instance" is used, it usually means you are opening various "Instances" of the same database, ...on the same machine.

What you are doing is quite normal for an Access database.

I am not quite sure what the issue might be.
I have had this problem.  My recommendation just for testing purposes is this:

1. recombine the database into a single fatabase.
does the error go away (my guess is yes)

2.  If the error goes away - switch tables one at a time.  In other words delete one of the tables local copies and make it a linked table one at a time.  eventually you will find offending tables.  

 3. Onc you have located the table look for unusual characters codes or corruption.  I think mine was a problematic field name.  

4. if you can't find anything wrong with field names in the offending tables try and delete data from the table and repopulate froma text file!


Author

Commented:
i have had to move away from this for a bit but will now re-engage with it.  We DID have problems with apostrophes in proper name fields at one point.  Doesn't seem to be a real good 3734 answer here anywhere although I have been making use of the decompile advice all over the place.  GOOD STUFF!

Looking back at comments.  
We are opening the back end once and leaving this one instance open in order to have the loop VB running.  When the loop detects a name it recognises in a specific table it executes a procedure based on the name, deletes the name form the table. and then returns to running the loop  All other access to the back end db are run by front ends at workstations.

Should work I would think.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
You should read up on Decompile from the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx

Author

Commented:
decompile was the best lead although the cause of the error isn't really explained anywhere... probably because there is no way to know what is going on

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial