Can splitting .MDB files into segments help with stability?

Question

Is this a realistic solution to the problems associated with larger .mdb files:

• split the large .mdb file into smaller .mdb files
• have one 'central' .mdb containing links to the tables in the smaller .mdb files

How easy would it be to make this change to an .mdb backed VB application?

Could the changes to the database be done so that there are no changes required to the front-end application?

Answer 1

Hmm well if the data is going through this central DB then there is still going to be a bottle neck in there. The only reason I can think why you would do this is to get around the size limit of an access mdb file.

Having said that if the business functions can be split off in the separate applications then that might be a good option with a central DB containing all the linked tables for reporting purposes. I have used this before to good effect

Answer 2

If you have more data than fits in a single MDB then you should get a different database engine.

One main issue that you should consider is that you can't enforce referential integrity between tables stored in different MDBs. That should be a show-stopper for any actual database.

If it's not, then you probably don't have a proper schema designed in the first place.

Answer 3

Edit Start
The short answer is "No, it won't solve the problems of a large database."

You might be able to overcome the DB size limitation (~2GB) by using this trick, but I've never tested it.

Typically, with large MS Access databases, you run into problems with speed and data corruption.

Speed
Is it going to help with speed? You still have the same amount of data to query and search through, and the same algorithm. So all you are doing is adding the overhead of having to open up multiple files per query. So I would expect it to be slower.

You might be able to speed it up by reducing the time time that it takes to ge tthe information off of disk. You can do this in a few ways:

1. faster drives
2. put the MDB on a RAID (anecdotally RAID-1,0 may be faster)
3. split the MDB up (as you suggest) into multiple MDBs, and put them on separate drives (maybe even separate controllers).

(how well this would work in practice vs. theory, I can't tell you - if I was doing that much work, I'd still choose to switch DB engines)

Data Corruption
MS Access has a well deserved reputation for data corruption. To be fair, I haven't had it happen to me fore some time. This may be because I've learned not to use it for anything big; or it may be because MS has put a lot of work in trying to solve these problems; or more likely a combination of both.

The prime culprits in data corruption are:

1. Hardware: e.g., cosmic rays, electrical interference, iffy drives, iffy memory and iffy CPUs - I suspect MS Access does not have as good error handling/correcting as other Databases do.
2. Networks: lots of collisions on a saturated network can confuse MS Access and convince it to scramble important records; as can sub-optimally implemented network protocols. TCP/IP is good, but it's not invincible.
3. Software: As I said, MS has done a lot of work on MS Access over the years, if you are not up to date on your patches (MS Office and OS), get up to date. Problems typically happen when you hit extremes like the 2GB limit (some bugs are hard to test and won't manifest them selves except at the edge cases, which makes the less likely to have been seen or corrected, unless reported by a motivated user to MS).

All this is exacerbated with larger databases, because larger databases typically have more users and more workstations accessing it. Altogether the larger database and number of users multiply to provide more opportunity for corruption to happen.

Edit End

Your best bet would be to switch to something like MS SQL Server. You could start by migrating your data over, and then linking one MDB to to it. You get the stability of SQL server and most (if not all) of your code should still work.

Once you've done that, you can then start migrating your VB app(s) over to us SQL Server instead.

Answer 4

For reasons more adequately explained by CodeSlave the answer is No and you should switch to a proper relational database.

I'd like to add that this does not have to be SQL Server. Quite possibly the reason why you are reluctant to do this is one of cost, SQL Server being quite expensive to obtain and deploy if you are not in an educational or charitable organisation (when it's remarkably cheap and then usually a complete no-brainer).

I've recently had extremely good results moving an Access system from MDB to MySQL. At least 95% of the code functioned without modification, and of the remaining 5% most was straightforward with only a few limited areas where significant effort was required. If you have sloppy code (not closing connections or releasing objects) then you'll need to fix these, but generally I was remarkably surprised how painless this approach was. Certainly I would highly recommend that if the reason you are reluctant to move to a database backend is one of cost then you should not attempt to manipulate .mdb files and go instead for the more robust database solution.