Programmatically Compacting Database

[injanib]

I have created a database in Access 2007 64 bit. I have compiled it into an ACCDE file. Once I distribute the database, I won't have access to it to do any kind of maintenance on it. I would like to implement and easy method for the person who will be the owner of this database in a network of of 4 computers. I would like him to be able to compact the database. Since it is compiled into ACCDE, and all access tool bars are hidden; I would like implement a button that can be clicked to compact the database whenever he needs to.



Is this possible? If yes, how?

Thanks in advance.

[khalid]

One option is to compact the Application on close:

Goto Ms-Access Option
Select the check - Compact on close

you have to do this on the source copy of your application before making the ACCDE file, the application will be automatically compacted whenever accde will close.

[injanib]

Thanks, but what if there are other instances of the database is opened by another user?

I was hoping to maybe have a code that would check if other instances are opened elsewhere, if there is, then close it. And then, just to be safe, make a copy of the database in another folder before compacting. Maybe I am asking for too much.

[RuralGuy]

Assuming this application is split to FrontEnd and BackEnd then *all* users should have their own copy of the FrontEnd.

[injanib]

No, this database is not split. It is a stand alone database. I left at as a stand alone because there is no way to secure the backend. I have a login feature included in the database, but if I split it, then only the front end will be somewhat secured. The backend which is more important will be open to the user with read/write access.

[RuralGuy]

If it is not split then *everyone* has their own copy of the tables and compacting when closing should not be an issue.

[khalid]

The best way is to split the database in front end and back end. The back-end should be protected with password on the network drive where users should have only read-write access, not deletion access.
Using the stand alone access will cause the major problem of data corruption and slow execution of process.

[injanib]

Thank you both. Please help me understand.
As RuralGuy said, Everyone will have their own copy of the tables in the case of a standalone database. I don't disagree, but I didn't know this was true. How does everyone get their own copy of tables when everyone is accessing one file. And why does it show one record as DELETED# on one user's computer if the another user deletes it on another computer? I am sorry, I am just keen to understand how it works.

Now about the split database.
The network share where the db is located is set to Read/Write access to everyone. No one can delete any file from here unless he/she is an admin, which they are not. I would assume that a user would have to have unrestricted access to a table to be able to work with data in it. How can I password protect the backend without having to provide the password to the users? I have tried it, but when the front-end is opened it asks for the password for the backend. This defeats the purpose of setting up password for the backend. When users access the table through a form, they will be able to do with a record only what the tools on the form can do for them, however, if they can open the table, they can pretty much do what ever they want.

Please tell me how this password protecting works. I would like to know it.

although slow execution is not so much of a concern for me, but I would like to know what is the chances of data corruption if only 2 or 3 users are using a standalone database concurrently. They will not be using it very frequently either.

thanks again, and I look forward to your additional feed back.

[RuralGuy]

The best MultiUser link I've ever found: http://www.accessmvp.com/TWickerath/.../multiuser.htm

[khalid]

As RuralGuy gave you a useful link on MultiUser link, there are some easy steps how to split your database in fe/be files, and make the back-end password protected:

Steps to split the database:

• Make copy of your Original database.
• Create a blank database and import all your tables to your newly created database.
• Protect your newly created database with password (you need to open your db as exclusively)
• Now Delete all the tables from your copy of original database, leaving forms, modules, macros, queries. (don't delete them)
• Now Link your tables from newly created database to your copy of original database.
• It will ask for password, give the correct password and link the tables.

You will have two files now, one is back-end with all your tables and the second one you will have the front-end with your forms, queries, modules etc.
Just make the ACCDE file of your front end and copy to your clients machines, now everybody will have their own copy of accde linked with back end.
your data will be extracted from the back-end by each user and they will work on it on their local front end copies.

Hope you got the point.

[injanib]

can I convert the Backend to ACCDE before encrypt it with a password and link to it to make it more secure? if yes, can I change it back to ACCDB if I need to?

[khalid]

If you change the back-end to ACCDE, then it will be difficult for you to work on the Original back-end data. My suggestion is not to change it to ACCDE.
You will not be able to work in design mood if you do so, ACCDE is used for front end to keep the designing of form modules etc locked from the users interface.

Note:- Always keep a source copy of the front end (not ACCDE) with you, before you give the ACCDE front end to users, you might need changes in the front end, so then you can do it on your source copy (ACCDB) whenever a new version needs to deploy.

[injanib]

Thank you very much. I go ahead and follow your instruction. I will let you know how I like the outcome.

[injanib]

Khalid, I used your method to secure the back end. I wasn't originally going to split the database because I don't have access to the network where it was going to be hosted. But I gave myself enough courage to walk someone having access to that network to split and connect the front end to the tables in the backend. I had to pull my hair over the phone a few times, but the outcome was successful and it was worth it. I don't have the responsibility for keeping the data secure and backed up, but should any design change be required, it will be so much easier. The other person is now somewhat experienced in connecting to the backend database anyways.

So I appreciate your helpful instruction and suggestion as as RuralGuy's help.

[RuralGuy]

Thanks for posting back with your success.