I inherited an MS 2000 access application that is 70mg without any data. I need to try to reduce this size some how. There are 130 tables, 300 queries, 125 forms, and 90 reports. Is there any way to see the size of each module? How can I tell which objects are the largest and prehaps could be re-written? Lots of these queries are very complex, some pulling over 5 tables together and doing calculations within the queries. Would these be better off if these were built as temporary work tables and have the calcuations done to the data in the tables? Any recommendations would be appreciated!!
-
-
Is that size after compact-and-repair?
Also, query objects add almost nothing to mdb size, but temporary tables will bloat database constantly. :) -
Yes, this is the size after compact & repair. I thought it was more efficient to build temporary tables then to have the massive queries. So, given the fact that there are 130 tables, is that what causes the .mdb to be so large?Comment
-
How much did the programmer that developed this behamouth weigh? Shooting him and cremating his remains wouldn't shrink the size of the database, but it would ease a number of problems on Planet Earth!
Sorry! Couldn't resist! Obviously, with a database this size one has to assume that this is a multi-users app. As such, of course, it should be split into a front end/back end app. A copy of the front end should be on each user's local hard drive, while the back end, holding all tables/data, should be on a shared drive/network.
This changes the concern about database size. The current objects, aka the front end, are only taking up about 1/28 or so of the maximum size (a little more than 2000 MB) of an Access file, which leaves a lot of room for (gasp!) expansion.
But I'd look at your tables, first off. I see posts here, and elsewhere, every week, where the poster has really gone overboard with "normalization!"
They have a table with
CustomerName
CustormerID
another table with
CustormerID
CustormerTeleph one
another with
CustormerID
CustormerCellph one
another with
CustormerID
CustormerFax
another with
CustormerID
CustormerStreet Address
You get the idea! Normalization can be overdone!
And there are Access databases out there where the records exceed 300,000, so the back end can absorb a huge amount of data.
Linq ;0)>Comment
-
Believe me.... there have been so many times that I wanted to put my hands around his throat! I will take a look at your suggestions and focus on the tables. I can see where they could have been designed more efficiently. Problem of course would be that they would have to give me the time to 're-do' this!Comment
Comment