Split .mdb back-end

[alex]

Using Access ‘03

I’m wondering if anyone has experienced this situation:

I have an mdb with quite a few tables (about 30) and a split mde.

The mdb holds two different kinds of information (case management/CM
and accounting/AC) and the mde of course manages all of it.

I’d like to separate the mdb into two distinct back-end databases
while still using the same front-end. I think that would improve
performance (not that it’s bad now) and keep things a little simpler
to manage.

My only issue in doing this is that while the relationship between CM
and AC are different they share a single user id table which has a one-
to-many relationship with the main CM and AC tables.

I suppose I could make two user id tables, but that may be tough to
manage.

Any ideas on how to split a back-end database and continue to share a
mutual table between the two while keeping a relationship?

Thanks,
alex

 

[Jerry Whittle]

If you want to keep it simple, splitting out a third mdb file isn't really
the way to go. As you noted, Access won't carry over the relationships
between the two MDB files. You'll have to "roll your own" probably by
checking for dupes in forms and such. You could have the same table in both
MDB files and do frequent update queries.

If size and performance aren't a problem, the old adage "if it ain't broke,
don't fix it" comes to mind.

 

[alex]

If you want to keep it simple, splitting out a third mdb file isn't really
the way to go. As you noted, Access won't carry over the relationships
between the two MDB files. You'll have to "roll your own" probably by
checking for dupes in forms and such. You could have the same table in both
MDB files and do frequent update queries.

If size and performance aren't a problem, the old adage "if it ain't broke,
don't fix it" comes to mind.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.













- Show quoted text -

Thanks Jerry. That's what I was thinking...didn't know if there was
some kind of trick.
alex

 

[Tony Toews [MVP]]

I have an mdb with quite a few tables (about 30) and a split mde.

30 tables is medium sized. I have a few apps with 120 and 160 tables
each.

I’d like to separate the mdb into two distinct back-end databases
while still using the same front-end. I think that would improve
performance (not that it’s bad now) and keep things a little simpler
to manage.

No, it'll get more difficult to manage. And performance won't be any
better.

Tony

 

[alex]

30 tables is medium sized.  I have a few apps with 120 and 160 tables
each.


No, it'll get more difficult to manage.  And performance won't be any
better.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Granite Fleet Managerhttp://www.granitefleet.com/

Thanks Tony!