Rookie MS Access - Creating the Front End MDE

Question

ok guys. here are some rookie questions for you:

I created a database for tracking metrics, with some automation tricks (email, .doc,.ppt presentations, etc) with a very large Main-table, and lots of forms/GUI. This is the first time I have ever I worried about an MDE/front-end for the thing. So if you would be so kind to answer a few questions, or offer any advice, it would be greatly appreciated (I would hate for all this work to not be utilized).

• What is the first thing I need to do? It the 2000 version that must be converted to 03 to create the MDE, but does that get done before I use the database splitter?

• Will the amount of objects in the database effect the ability to do this? I have something like 80 forms, 70 queries, 20+ macros, 12 tables, etc...but does the amount of objects prevent some of this from working well once the front end is there?

• when i split the database, can I continue to work/make changes and such on the "back end", and have those changes directly effect the front end?

These may be some basic questions, but I don't know the answer so.....Thanks!

Answer 1

Here is my 2 ¢.

Question 1 - I have never used the database splitter as I feel I have more control doing it manually. If you do it manually you can do it to a version that does not have a database splitter. But if you do use the splitter then--yes--you will have to upgrade to a version that has a splitter before doing it.

To do it manually here are the steps.

1. Backup everything.
2. Create a copy of your file into the same directory. So if you have an MyApp.MDB create a copy into the same directory with a new name, such as MyAppDATA.mdb.
3. Open the new DATA file (MyAppDATA.mdb) and delete all of the objects EXCEPT the TABLES.
4. Open the App file (MyApp.mdb) and delete all of the tables.
5. Also in MyApp.mdb...go to the File/Get External Data/Link Tables menu to link the tables in MyAppDATA.mdb to MyApp.mdb. Select All and create the links.

That should do it. And if you screw up you made a backup...right?

A couple of tips and gotchas...be sure that you go to Tools/Options and that you are NOT showing System and Hidden tables. You just don't want to delete system tables from MyApp. Another way to do it is do NOT delete tables that start with MSys or USys.

Question 2 - Does not matter how many object you have. In fact you don't have that many objects anyway.

Question 3 - Yes...you will make backend changes in MyAppData.mdb and when you open MyApp.mdb those changes will auto-magically be there to see and query against etc. (In the query designer you may need to save/close/reopen to see new fields if you made the mod while in the query). The EXCEPTION to that is New Tables You will have to use the File/Get External Data/Link Tables option to create links to new tables.

One thing to remember (and that I hope you already realize) is that the one downside of splitting the database is that when you deploy the front end file that usually the relative path to the data will vary from machine to machine and there is no automatic re-linking of tables in access. If your target clients have full access you can always use Tools/Database Utilities/Linked Table Manager to refresh the links to the right location. If you can't do that then you will have to do one of the following:
1. Write code that does the automatic re-linking for you. Basically it will check the links...if invalid it will prompt the user for the data location (or look it up in an INI file) and re-link the tables.
2. Always deploy your app to the same location on all machines. If you have commercial visions for your application this won't work...I mention it for academic reasons. It might be doable for a limited deployment where you have a lot of control over file placement on each machine.
3. Put the Data file (MyAppDATA.mdb) onto a network share and link the table across the network using a drive mapping or UNC (\myserver\mydata\ApplicationData\MyAppData.mdb). The latter is preferred but both of them run the same risks as number two.

Seth

PS This answer assumes Access 2003.
PPS If you have commercial visions for your application then the table linking has got to be REALLY robust. PPPS I agree with the commenter that you may want to take the plunge and do SQL if it is in your skill set.

Answer 2

This would be a comment to Seth's answer, but my rep isn't high enough to comment yet.

Seth did a great job answering your questions, I just wanted to add a bit more to part #1 about using the Database Splitter. The Database Splitter in the Tools menu works fine. Doing it manually is alright too, but it's a whole lot faster and easier to use the Database Splitter. I've used it a dozen times and never encountered any issues after using it.

http://www.databasedev.co.uk/split_a_database.html has a decent page about some of the pros, cons of splitting your database.

http://www.accessmvp.com/TWickerath/articles/multiuser.htm also has some good info when dealing with a split database in a multi-user environment.

Answer 3

Seth gave you a very good answer. But I'll add a few comments.

The number of objects only becomes relevant when you get close to about 1000 forms, reports and modules which have code. There's a limit about there. If you do get that message when trying to make an MDE then you almost certainly have a code error and need to compile to find the error

Another resource is "Splitting your app into a front end and back end Tips"

See the Auto FE Updater downloads page to make the process of distributing new FEs relatively painless.. The utility also supports Terminal Server/Citrix quite nicely.

Answer 4

One thing that hasn't been discussed, and that's the issue of whether the compile to MDE could fail. Basically, if your code compiles in your front-end MDB, it will convert to an MDE. But I've noticed that lots of people never compile.

Some hints for keeping your VBA code in good shape:

1. in VBE options, turn off COMPILE ON DEMAND.

2. add the COMPILE button to your standard VBE toolbar and USE IT OFTEN.

3. periodically, backup your MDB and decompile/recompile it.

Also, remember that you must keep the MDB source, as the VBA code is not editable in an MDE and not recoverable by any good method.

EDIT:

Steps for a decompile:

1. backup your MDB.

2. start an instance of Access with the /decompile commandline argument. For, instance, I have a shortcut on my deskstop that has this as the target:

   "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile

3. having opened that instance of Access, open the MDB you want to decompile. You will see nothing happen. DO NOTHING FURTHER IN THIS INSTANCE OF ACCESS -- close this instance of Access (the reason for this is that Michael Kaplan, who knows a thing or two about this, recommended that you never do any work in an Access instance opened with the decompile switch because he said there was no guarantee that the Access application code executed under those circumstances in a way that was fully safe for all kinds of Access work).

4. open the just-decompiled MDB holding down the shift key (you want to be sure that startup routines don't run because that would likely recompile the product before you've finished your cleanup) and compact the MDB (holding down the shift key again).

5. open the code editor and compile the project (DEBUG -> COMPILE [db name] for those who haven't step #2 in my original compiling instructions at the top of the post before the edit).

6. compact the MDB (doesn't matter if you bypass startup, since it's already fully compiled).

Why so many steps?

Because the purpose of the decompile is to get rid of the compiled p-code in order to start afresh from the canonical VBA code. Following the steps above insures that you have completely cleared the data pages storing the compiled code before you recompile. The reason for this is that without the compact step after the decompile, under some very rare circumstances, the code can behave strangely. I can't imagine that the old discarded p-code is being used again, but there's something about the pointers between the canonical code and the compiled code that apparently doesn't get completely flushed by a decompile without a compact.