Limitations on Split Databases

[MJ]

I have been digging around for some time on split MS Access databases and
have not seen anything that directly answers my questions. I see and
understand the efficiency wins by splitting a database in a
network/multi-user environment.

Are there any limitations on the number/size of splits?

I know that Access gets ugly as the database file approaches 2GB. We have a
database that we want to retain access to "archived" tables of completed
records. While none of these have reached the 2GB levels yet, I am wondering
if we could work with several "splits" simultanoeusly and maintain same
efficiencies? For example:

1) Database ABC (FE)
2) Database ABC (BE)
3) Database ABC (BE) Archive FY2007
4) Database ABC (BE) Archive FY2006 ...

While the Archives are not accessed regularly during day-to-day operations,
they are needed for reporting purposes from time to time. Would this work
effectively?

Thanks in advance for your assistance,
MJ

 

[Douglas J. Steele]

One regular here tells stories about a front-end linked to 23 different back
ends (Access 2, if I recall correctly...), so yes, it can work.

 

[John W. Vinson]

I have been digging around for some time on split MS Access databases and
have not seen anything that directly answers my questions. I see and
understand the efficiency wins by splitting a database in a
network/multi-user environment.

Are there any limitations on the number/size of splits?

I know that Access gets ugly as the database file approaches 2GB. We have a
database that we want to retain access to "archived" tables of completed
records. While none of these have reached the 2GB levels yet, I am wondering
if we could work with several "splits" simultanoeusly and maintain same
efficiencies? For example:

1) Database ABC (FE)
2) Database ABC (BE)
3) Database ABC (BE) Archive FY2007
4) Database ABC (BE) Archive FY2006 ...

While the Archives are not accessed regularly during day-to-day operations,
they are needed for reporting purposes from time to time. Would this work
effectively?

Thanks in advance for your assistance,
MJ

It would work but there are some limitations. The most critical may not apply
in this case, since I presume you'll not be updating the archives: you cannot
enforce referential integrity between tables in two different databases.

It's possible to create a UNION query (say) stringing together records from
tables in different backends, but performance may be abysmal. I would
certainly use UNION ALL so Access doesn't waste time (probably a *LOT* of
time) trying to remove duplicates.

Consider moving your backend to SQL/Server. That's become a very affordable
option now and will go a long way toward resolving these issues.

John W. Vinson [MVP]

 

[MJ]

Doug,

Thank you for your fast reply.

MJ

One regular here tells stories about a front-end linked to 23 different back
ends (Access 2, if I recall correctly...), so yes, it can work.

 

[MJ]

John,

Thank you for your fast reply.

While it is possible to to use multiple BEs, I was wondering if there were
some downsides to multiple BEs, and you confirmed that there can be some
trade offs. There are other departments at our company that have made the
jump to SQL Server, I guess we may need to speak with them to about the
possibility for moving our over.

Thanks again,

MJ

 

[John W. Vinson]

John,

Thank you for your fast reply.

While it is possible to to use multiple BEs, I was wondering if there were
some downsides to multiple BEs, and you confirmed that there can be some
trade offs. There are other departments at our company that have made the
jump to SQL Server, I guess we may need to speak with them to about the
possibility for moving our over.

If you already have SQL installed and have the infrastructure to maintain it,
I'd make it a priority. It's more of a challenge if you need to start from
scratch.

John W. Vinson [MVP]