Database Longevity

[ohmydatabase]

Hello,



So my database backend, in all likelihood, will one day become too large and I will have to create a new backend to preserve functionality. What is the best way to handle something like this? As the backend grows, there is most likely some information that I would like the user to still be able to access, but there is definitely a need to store the bulk of the information elsewhere.

This problem is still far out in the scope of current database, but I would like to be able to prepare for when that day actually arrives.

Thank you.

[June7]

Options:

1. multiple Access backends

2. $$$$ - migrate to SQLServer or Oracle

[jwhite]

In non-enterprise environments SQL Server Express is a viable option, offering up to 10GB of data storage.

[orange]

June and jwhite have offered suggestions that are generally used when the original Access database grows.
There may be some archiving techniques to provide summary data or manipulated data in another backend for specific purposes.
The key is to analyze the situation and forecast the timing and metrics associated with this future requirement, and make some preliminary scenarios/preparations/advice now to avoid "unnecessary panic" when the time comes.
It's out of scope of current database, but, since you recognize this potential need, it's probably worth noting and providing some options. Are you talking a year or 2 or ????
It may be something to be put on a to do/revisit list when ever, if ever, some change in business requires that the database be adjusted.

Good luck.

[ohmydatabase]

Thank you all for the input.

I'm thinking it will probably take a year, possibly 2 for the backend to reach capacity. So this is definitely on the to-do-later list. If I make multiple Access backends, is there a way to have the front-end link to multiple back-ends? If so, is there a limit on the number of tables that I can link?

The person who taught me Access has a few applications that use SQL Server rather than Access backends, and I'm guessing this is the reason why.

[orange]

Depending on some coding practices you might want to try Compact and Repair from time to time to recover space used by Access.

[June7]

Certainly Access can link to multiple backends. Backend is a broad term that encompasses spreadsheets, text files, other db files. If there is a limit it should be quite large. I have one db frontend that links to over 200 hundred tables in a single backend Access file. It used to also link to dozens of dBaseIV tables but I have since imported those into Access.

[ohmydatabase]

[QUOTE=June7;357891]I have one db frontend that links to over 200 hundred tables in a single backend Access file.QUOTE]

You're wild @June7. Is it as simple as using the link wizard and referencing a different backend file? If this is the case it seems like one could make a back-end for every table. (or every one large enough for this to matter) Each table could have 2GB of storage right? Then in the future when a certain table is too large, queries/code can be rewritten to just reference a different table.

@orange, I've compact/repaired my frontend a few times. Out of paranoia I've never compact/repaired my backend. Is my paranoia unwarranted?

[Bulzie]

Do you have SQL at your current location? If so that is the right answer.