I am now trying to create a VBA coded button to allow for Admin users of my database to compact the backend tables. The trouble with this is that I have the same LogonF and UserT from the front end on the backend which uses TempVars to check for user authorization to enter the backend file. I would assume from this video that I need to use the DBEngine.CompactDatabase rather than Application.CompactRepair due to these passwords being there. Any advise on how I would go about making this work?

In addition here, I'd like to be able to do this with the button in a form on my frontend database. I have a form which can only ben opened by certain users called "Admin" where I would place the button for compacting the database backend & archive tables.

You've duplicated a Table and Form?

I just copied and pasted the LogonF and UserT from the frontend file as a measure to prevent unauthorized access to the backend file. I have been testing and so far it seems to work properly in the backend file

How do you keep them in sync when a new user or password changes?

I have an append query for when new users are added or a password changes which updates the UserT on the frontend. I hadnt considered how to keep the backend UserT updated. I will have to add an append query to the backend.

Alex,

The UserT is included in the backend as well. I just tested adding a new user in the frontend and it also shows in the backend. When a user updates their password, it shows there as well.

To get back to the issue at hand, how do I go about using VBA to compact the backend tables since each user has their own password?

Has the video not provided a mechanism to do what you need?

For the most part, yes it has. I am just concerned that since each authorized user of the backend has a different password that it won't work correctly. I plan to make a test copy of the backend to test this out.

Another issue I just found: I have my backend tables on a network folder outside of my frontend file. The code for compacting the backend does not work unless I put the backend file on my local drive.

What error does it show?

Run-time error '70': Permission denied

Perhaps I have done something incorrect in the code, though I have followed it per the video. I have discovered that the problem with this latest error is that the backend file is open at the same time the frontend file is, so it is unable to run correctly. I came to this conclusion as I tested placing the code as written in the video into a new button on a new form in a new database and running it. The result was as expected in that it created the backup file, compacted the backend file then renamed it to the original backend file.

I watched the video from Richard on Compacting my database using VBA in a button click event. I set the code up just as he did in the video. When I go to run the code via button click on my front end, it pops up with Run-time error '70': Permission denied. I assume this is due to the backend file being locked due to the tables in the front end being linked to the backend? I know in the video Richard has a fake BE file he uses which was not linked to the frontend file he placed the code in. I created a separate database file with a form and a button that has the code loaded for compacting my original backend file. I am just trying to get a handle on making this work in my original frontend file so that one day when I no longer work in this department, the next admin user can easily compact the database.

Can the Admin not just open the db and compact from the ribbon?

Yes. But I was hoping for the ability for anyone whom has been granted access to the backend to be able to compact it.

Have you tried with another db that isn't linked to the one you want to action on, does the compact?

@Daniel I haven't read all the posts here, but I will go ahead and comment on what seems to be some possible problems.

1) If you try to compact the BE from the FE, the FE is still connected to the BE and it probably (I'm guessing) compact won't work. Definitely true of a backup, since the BE has to be exclusive. Makes sense this must be the case for a compact repair too. I don't know what video you're referring to for a fake BE, but that doesn't make a lot of sense to me.

2) I would put all the passwords in the BE and then link to the BE table. That way the future administrator can enter the BE and run the BE objects that they need with the same permissions and password setup you're using on the FE.

3) Have you found a VBA way to know if anyone is still connected to the BE before you start doing compact or backups?

4) Is there a way to check for FE connections, Close the .accdb, and then open it again exclusive and then close that and reopen for FE connections? It seems to me that would be a lot for VBA to handle.

Can you share the link for the 599CD.com video(s) you're watching.

This is one of the reasons I wish the Access team would get with the twenty-year-old program they started with shadow backups. I have automated backups that run every day at a scheduled time. It's odd that Microsoft hasn't been inclined to add this technology to their flagship Office products. Doing so would take care of the backups, but clearly not the compact. I'm not a fan of the compact & repair until I get a better explanation from Microsoft on exactly what it does. At the very least, I would want a log of what was "repaired".

The video is on this page, the comment was made on.

Previous Thread: Detect multiple users

@ Thomas,

Late yesterday afternoon I was able to get this rectified... All I did was create a function to open a database where the compact button exists by itself and close the FE database using the 'quit' command. This works to my satisfaction for this problem. I plan to expand a little on this button so that it will also compact and repair the FE as well and then when its completed, re-open the FE. Also in this expanded version I plan to have the code perform a backup for both the front end and back end files... I saw an updated code in some comments for this video which seemed to work for it so I am off to try it!

@ Alex H., thanks for the clarification of the source for the thread. With all the notices (where I see most of these) and different forums, it's easy to get lost in the trees.

As to the multiple users, yes we both tackled it once before. I'll have to go back and review why the MS "Jet" solution (and others) was inadequate for the purpose I needed. Not to say it wouldn't work for others. I have a table for this, but table data is subject to error, and I would like a more definitive way to know the connection status from Access.

Once one knows that there are users attached to the ACE BE, then we need a way to disconnect them, gracefully, so as to continues with BE maintenance operations. I'll watch to see if this thread's video mentions this, with a solution.

I watched this thread's video. I think Daniel hit on the problem that was not clearly covered in the video. At 1:36 Richard says everyone has to be out of the BE database. But if you are in a FE that is attached to the BE, then not everyone is out of the database! If you have a FE that isn't attached to the BE, then the code may work, but I would like to see in the video how to test if the BE is free of other users (yes I need to go check the links for this again, as mentioned previously).

The big question for me is, since Microsoft went to pains to backup only an exclusively opened database, what might a simple copy of the BE file miss? Remember in Windows, a file just isn't a single stand-alone object that you can count on to be complete at any moment, even in a single user environment (Word is constantly telling me I need to choose which version of a document I wish to keep, when I only had one open to begin with!). There are bits and pieces spread about in memory and application temp files that aren't resolved until the file is closed (and even then it may take some time). That was the whole reason Microsoft had to come up with their shadow copy technology.