MS Access and Performance when Splitting Two Backend (Past 2GB Limitation)

Question

If I have to create multiple Access Databases due to the 2 GB limitation, will I gain better performance. That is if only the backend data is getting split to two databases. Only one database is getting full so we have to spilt to multiple databases but use the Link Manager to connect the other tables.

COULD WE EXPECT BETTER PERFORMANCE or not really? Or even worst performance?

Thank you in advance for your time

Answer 1

That depends. If I split the Northwind sample database in FE and BE, and then split the BE in two, with Orders table in the one, and OrderDetails table in the other, and I then query across both tables, I would expect that to be slightly slower than if both tables were in the same BE. Of course the BIG problem would be that there is no Referential Integrity across 2 databases. That would be a deal breaker for me.

Conversely if the Order-related tables were in one BE, and the Inventory-related tables in the other, and those two major functions rarely cross databases, then I would expect performance to be the same.

Why don't you take a few minutes and try it out yourself?

If I have that much data, I would seriously consider putting it in SQL Server. The free edition can accommodate up to 10 GB of data in one database. The standard edition is not free, but can accommodate virtually unlimited data.

---

-Tom. Microsoft Access MVP

Answer 2

I would expect that to be slightly slower than if both tables were in the same BE. Of course the BIG problem would be that there is no Referential Integrity across 2 databases. That would be a deal breaker for me.

Hi Tom,

I haven't tested it either what the difference in performance is with two tables in one backend, or two tables in different backends. But as far as I understand, it should not make a difference. In both cases it is "just a reference" to a table in an external database. But I can be wrong. Late binding could have some influence.

I use these "split tables" or late bindings on a regular basis. Personally I do not feel the problem with the Referential Integrity, as a replacing functionality is build in in the FE of each application.

Imb.

Answer 3

Great recommendations and yes now it is making sense for Splitting correctly based on "OBJECTS" or "Entities".

So Tom, I really like your recommendation to move to SQL Server and my only questions is what happens when every month we received the new data that includes the previous and as of day's data. Do I need to totally truncate and re-load the Spreadsheet data to the SQL Server every month or is there a way to set it up to run automatically like an ETL process. Thanks a lot for your time

Answer 4

It's better to start a new question in a new thread.

Short answer: you can use SQL Server Integration Services to import data as an ETL process.

---

-Tom. Microsoft Access MVP