Have Geek Brain

Can splitting .MDB files into segments help stability?

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

:

  • split a large file .mdb

    into smaller .mdb

    files
  • have one "central" .mdb

    containing links to tables in smaller .mdb

    files

How easy would it be to make this change in a supported .mdb

VB application?

Can changes be made to the database so that there are no changes required for the external application?

+2


a source to share


4 answers


Edit home
Short answer: "No, this will not solve the problem of a large database."

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

Typically, large MS Access databases have speed and data corruption issues.

Speed ​​Will
this help with speed? You still have the same amount of data to query and search, and the same algorithm. So all you do is increase the overhead of opening multiple files for each request. So I expect it to be slower.

You may be able to speed it up by reducing the time it takes to erase information from disk. You can do this in several ways:

  • faster drives
  • set MDB to RAID (maybe RAID-1.0 could be faster)
  • split the MDBs up (as you suggest) into multiple MDBs and put them on separate drives (maybe even separate controllers).

(how well this would work in practice versus theory, I cannot tell you - if I did that much work, I would still decide to switch DB mechanisms)



Data Corruption
MS Access has a well-deserved reputation for data corruption. To be fair, I haven't had this happen to me in a while. Perhaps this is due to the fact that I learned not to use it for something big; or it could be because MS has worked hard to solve these problems; or rather a combination of both.

The main culprits of data corruption are:

  • Hardware: for example, cosmic rays, electrical interference, iffy disks, iffy memory, and iffy processors. I suspect MS Access doesn't have good error handling / fixing like other databases do.
  • Networks: A lot of collisions on a saturated network can confuse MS Access and convince it to scramble important records; since network protocols can be implemented with optimal implementation. TCP / IP is good, but it's not invincible.
  • Software: As I said, MS has worked on MS Access for many years, if you don't update on your patches (MS Office and OS), please update. Problems usually occur when you push extremes like the 2GB limit (some bugs are hard to test and won't show up except in extreme cases, making it less likely to appear or fix unless the message is motivated by the MC).

All of this is compounded by large databases as larger databases usually have more users and more workstations accessing it. In general, the large database and the number of users are multiplying to provide more opportunities for corruption.

Change end

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

Once you've done that, you can then port your VB applications to us instead of SQL Server.

+4


a source


If you have more data than one MDB, you should get a different database engine.

One of the main issues to consider is that you cannot enforce referential integrity between tables stored in different MDBs. It should be a show stopper for any real database.



If it doesn't, you probably don't have the correct circuit designed in the first place.

+2


a source


For reasons more adequately explained by CodeSlave, the answer is No, and you should switch to an appropriate relational database.

I would like to add that it doesn't have to be SQL Server. Quite possibly the reason you are reluctant to do this is one of the costs, and SQL Server is quite expensive to acquire and deploy unless you are in an educational or charitable organization (when it's remarkably cheap and then usually completely hassle-free) ).

I recently got very good results by moving the Access system from MDB to MySQL. At least 95% of the code functioned unchanged, and the remaining 5% were simple, with only a few limited areas where significant effort was required. If you have sloppy code (not closing connections or freeing objects) you need to fix them, but overall I was surprisingly surprised at how painless this approach was. Of course, I highly recommend that if the reason you don't want to go to the database backend is one of the costs, you shouldn't try to manipulate .mdb files and instead use a more robust database solution.

+1


a source


Hmm well, if the data goes through this central DB, there will still be a bottleneck there. The only reason I can think of why you would do this is to bypass the mdb access file size limitation.

Having said that, if the business functions can be decoupled in separate applications, it might be a good option with a central DB containing all related tables for reporting purposes. I used this to good effect

0


a source






More articles:


All Articles