[AccessD] Advice on A2010....

Darryl Collins darryl at whittleconsulting.com.au
Wed Dec 7 17:33:02 CST 2011


Hi everyone,

Boy, A2010 does some weird stuff.

I have an accdb database. It is on my local drive, it is only me who uses it, it is only me who can access it.  It has DAO code it uses to do stuff with recordsets within the currentDB only.  There are no external connections. There is no BE / FE setup (doesn't need it).  This database is tiny (about 7 MB) - doesn't have a lot of data or really do any heavy processing at all - basic stuff.

The only thing that is unusual for me is I have one main form, which has 37 tabs on it which in turn has listboxes and subforms - and the subforms are bound to their tables - I would usually use unbound, but in this case there is no point.  Besides they are all really small datasets.

For weeks this has worked pretty well - then suddenly I started getting an error when trying to make design changes "Not opened in Exclusive mode - another user is using the database - can't save changes" was the gist of it.  Now how can that happen?  There was some suggestion from Google search that it maybe one of the DAO recordsets was being left opened and thus Access thinks there is a open connection and thus another user(?).  I double checked I was closing all RS and setting them to nothing when I finished with them.

Nothing seemed to work.  After much poking around on Google I found this piece of code and added it.  It seems to have helped, but I am not sure if that is just co-incidence or not.  I had done a few tweaks and rebuild a couple of modules so hard to say:

"'Initiate Passive Shutdown - do not allow new Users
CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = 1"

This is meant to force access to reject any new users to the database.  I wish I could say with confidence that this was what fixed it. I guess I could comment out the code and do some tests, but right now, after losing two days, I am just wanting to catch up on the days I have lost.

The other weird issue I get at some point when using this database is "You don't have enough resources to perform the operation".  WTF? Again this will fail on doing something low impact and simple.  It is like Access hit some sort of limit (again maybe open connections via DAO.Recordset(?) - this is where it usually fails when using code to update stuff via DAO).  But all the DAO connections are all being closed in the code - and usually only 1 is open at any one time (a max of 3 in a couple of modules) and they are all set to nothing once I have finished with them.  Besides they are also doing bugger all work.  Maybe updating 10 records out of a total of 50 - that sort of thing.  What 'resources' could possibly be being exhausted with that sort of workload?  A restart of the app will usually fix the problem, but what is going here?

Has anyone else had these issues, and any ideas on what to do about them?

Cheers
Darryl.

Darryl Collins
Whittle Consulting Pty Ltd
Suite 8, 660 Canterbury Rd
Surrey Hills, VIC, 3127

p: +61 3 9898 3242
m: +61 418 381 548
f: +61 3 9898 1855
e: darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au>
w: www.whittleconsulting.com.au<http://www.whittleconsulting.com.au/>




More information about the AccessD mailing list