Compact and Repair From Code

No, but it means that you cannot have an active connection to the BE when you do the C&R.

I do this from a form that is not bound to any tables, and only when it is the only form that is open.

If any table, query, or bound form is open when you try to run this, you will get that error message.

Well, I think that is what I am trying to do. I NEVER use bound ANYTHING, but the function I am running from has a couple of record sets that are populated with the OpenRecordset command. I tried setting the declared Database variable = Nothing before I ran my code. (But I did not set the Record Set variables = Nothing.)

My programming methods are really more like running regular VB than conventional Access design. Controls are all populated into local memory using the ".RowSource" property. And the form I am running from is just a collection of command buttons.

I will go back and set EVERY declared variable = Nothing before trying again. But I am afraid that I may have to fall back on Plan B, which is to run the process in steps and then manually compact and repair the BE between steps.

Will let you know how I make out. Thanks for the reply.

If you have some kind of link just close it.. perform the CR...relink

I NEVER use bound ANYTHING

Just FYI, Access is a RAD (Rapid Application Development) tool.  The whole purpose of using Access as a FE is BECAUSE it does stuff for you.  If you are not using bound ANYTHING, you are not gaining ANY advantage by using Access.  In fact, you have willingly accepted all the baggage that comes along with using Access and tossed away with disdain any benefit you would have gained by using Access as it is intended to be used.  You wouldn't use a hammer to screw in a nail.  Know your tools and use them appropriately for maximum gain.

If you're positive that no bound object is open, then make sure you don't have multiple instances of the database open.  Close the app.  Look in the folder where the BE lives.  Is there an .laccdb file with the same name as the BE?  If so, some other task has the BE open.  Open the BE and close it. Did the lock file disappear?  If not, Open task manager and find any active Access.exe items and force them to close.  If the lock file still isn't gone, try to delete it.  It may be an artifact of an earlier run that was terminated without closing down Access properly.

Pat:

Thank you for your perspectives. Here are mine:

If it was my decision, I would be doing all this development in regular VB. Unfortunately, it's not. I work for a large company that does not have any technologically literate people at the upper management level. So - on the one hand, it is the rare year that passes when someone doesn't announce an initiative to "get rid of all the Access databases." No year passes, as well, without the inevitable "technology fair" where we get exposed to the latest set of bright shiny objects that are supposed to make people who live in a world of vLookups, pivot tables and cutting and pasting suddenly technologically savvy. Meanwhile, our regulatory climate is constantly evolving, changing our data processing and reporting requirements so that I have to modify things, sometimes in a couple of days. Were this all being handled by our "IT Professionals" we would have to wait six months (minimum) and, indeed, our commercial applications (supported by IT) are generally between six and twelve months out of date.

Having used and developed in Access for two decades, I have invariably run across two main complaints about Access: "It doesn't scale" and "performance is lousy when you have more than a couple users." By using a form of "client/server" architecture, where each user has a dedicated version of the application on their workstation and hits on the back end database files are minimized, we can support two dozen concurrent users and use data-driven programming methodology that allows a single form to be used to manipulate multiple tables on very compact code. And we can adapt to changes in the table structures - often without having to rewrite a single line.

Admittedly, keeping this all in Access involves the baggage of having to have Access loaded - which would not be required if we could compile an executable. But it DOES make examination of the back-end tables pretty easy - and we can copy stuff in and out of Excel when needed. This gives the rest of the group at least a marginal level of comfort that they can survive if I go under a bus.

So, while it's less than ideal, it DOES work. And once a few basic coding methods are mastered, they can be reused as often as required to leverage the RAD aspects of Access without paying the performance costs associated with bound forms and controls.

As I am sure you know, when it comes to application development, there is always more than one way to skin a cat.

I too have been using Access for more two decades (boy that sounds like a long time) and I don't have any trouble with bound forms or multi-million row tables.  I guess it's a matter of development style.  Mostly my BE's are SQL Server so Access is infinitely scaleable once you understand how to distribute updates to the FE without touching each desktop.  The biggest user base I have is about 150 concurrent users.  All my apps use bound forms although I have made a couple of complex search forms that are unbound because they can bring back a few thousand rows.  Sometimes I use views if I need to speed up joins but the views are still updateable and so can work fine bound to forms.  I also sometimes us pass-through queries for bulk updates and I've had to write a stored procedure occasionally for a very complex report.

My background was transaction processing on the Mainframe where my apps might have thousands of concurrent users so coding efficiency was paramount since our service level agreement called for 1 second response time to the majority of requests and a maximum of 4 seconds for the most complex..

I use Access by choice and I still love it.  It is the most productive environment i have ever worked with.  Obviously Access is appropriate for only a sub-set of application types but for a data-centric non-web app that is small enough to be developed by a single person, Access cannot be beat.  Once the app is too big for one person, it becomes unwieldy.  If you need remote access, you're stuck unless you can use something like Citrix and it is completely useless for any app where you have anonymous users or graphic or heavy computational requirements.  I wouldn't be recommending Access for building games or operating systems.  Before the inmates took over the asylum in the mainframe world, we knew our tools and used the best tool for the project at hand.  Now it seems that everything looks like a nail so all my former colleagues have to use is a hammer.

BTW, if you skin any cats, PITA will come and get you :)

It just occurred to me that you probably shouldn't be compacting the BE from the FE anyway.  www.FMSINC.com has a good tool that you can use that compacts the BE on a scheduled basis.  It can handle all of your BE's and is very reasonably priced.

If you are compacting the FE, you might want to consider using techniques that don't cause bloat or perhaps downloading a fresh copy of the FE each time the user opens the app.  I set up my apps to run from a shortcut.  The shortcut runs a batch file located on the server (for ease of maintenance).  The batch file makes a local directory (this handles new users), deletes the existing copy of the FE if it exists, downloads a fresh copy from the server, and then opens it.

Why is it that us old guys (I'm 70 next year) always seem to be the ones who actually know how to leverage the technology when all the young whippersnappers keep jumping from one shiny object to the next?!?

Thanks for your insights.

Access FE and BE whatever you like can handle a lot of concurrent users....that's a fact...if problems arise is due to design issues...Access is not to blame on that...