MDE and MDB

[LDanix]

Is there any way to get tables in an MDE to link back to tables in an MDB? I
don't want to change the MDB to MDE because the database is constantly having
changes made to it and its modules.

Thanks

 

[Rick Brandt]

Is there any way to get tables in an MDE to link back to tables in an
MDB? I don't want to change the MDB to MDE because the database is
constantly having changes made to it and its modules.

Thanks

Here is the "best practice" that nearly all serious developers use.

Split the app into two files. One with just tables. One with everything
else. The one with tables (the "back end") goes wherever you want it on
your network and then in the "front end" you create links to the tables in
the back end. When creating the links go through Network Neighborhood
rather than using a mapped drive letter. That way the link path works
everywhere on your LAN without having to map drives consistently on all PCs.

Here's the good part for the developer: You can now create as many copies
of the front end file as you want and they will all work and all point to
the same tables in the back end. You can create MDE copies of that file and
give them to your users while you keep a copy of the MDB to work on. Any
time you want to give your updates to your users you simply give them a new
MDE (that part can be easily automated).

If you need to make changes to the back end then that is the same situation
as you have now. You need to kick all users out and make the changes when
you have exclusive access. However, most updates only involve the front end
file and that won't be necessary..

 

[LDanix]

Will this work if my users are using forms in the MDEs to change the data in
the linked tables?

 

[Rick Brandt]

Will this work if my users are using forms in the MDEs to change the
data in the linked tables?

Yep.

 

[Rick Brandt]

Okay, everything is split and linked to the backend MDB. Now, when I
attempt to create the MDE, I get this error:

Microsoft Office Access was unable to create an MDE database.

When I click Show Help, this is what I get:

This error is usually associated with compiling a large database into
an MDE file. Due to the method used to compile the database, a
considerable number of TableID references are created for each table.
The Microsoft Jet database engine version 4.0 can only create a
maximum of 2048 open TableIDs at one time. Exporting a database as
an MDE potentially can exceed this limit if the database has a large
number of objects (table, macro, form, report, etc). There is no
accurate method to estimate the number of TableIDs the Jet database
engine uses during the process of compiling a database as an MDE.
However, each VBA module and each form uses one TableID, as a result,
if the database has 500 forms, and each form's HasModule property is
set to Yes, as many as 1,000 TableIDs are used.
More information about this error message online.

According to this, it seems as though what we are trying is not
possible. Am I missing something somewhere?

No it doesn't mean that at all. As I said, virtually all developers use
split apps with MDE front ends so this is not something "exotic" that you
are attempting.

What version of Access are you using? Access 2002 and 2003 both use the
2000 file format by default, but they cannot make MDEs from that format.
You would have to convert to the 2002/2003 format first.

In all versisions making an MDE will fail if you have any compile errors in
your project. Open the VBA window and do a compile on your project. If any
lines won't compile they should be shown to you so you can fix them. Once
the project will compile without errors then you should be able to make an
MDE successfully.