Questions Regarding MS Access Memory

[JeffG3209]

I have not quite figured out the fundamentals of access programs and memory. I have been working with my "database" for about 5 months now. I update the records monthly. I have a few simple questions that I am unsure of.

When I right click the properties of the icon, I see 786mb. However, I only have 51.1mb of xls data.

This means I roughly have 735mb of access code and structure? I figure this is not the case, and maybe access creates its own cache or index. Is that the right way of thinking?

One other question I have that pertains to memory. At what MB range will I start experiencing difficulties. I have read 2GB per table, but wonder at what overall program amount will it lock up?

[boblarson]

First off, have you run COMPACT AND REPAIR? That needs to be done periodically (don't use the compact on close option as that is too often and can result in corruption if the right conditions present themselves). Access doesn't free up the space taken up by deleted objects until the compact is done.

Second, you wrote 2Gb per table but it is 2Gb per DATABASE FILE (including system objects). You could possibly use a separate database file for each table but then referential integrity can't be enforced by Access so you would have to code it in.

There really is no range as to when difficulties might arise. It really depends on the amount of data, the type of data and whether it is maintained (compact and repair done on a regular basis).

The database SHOULD be split - backend with tables only and then the frontend with everything else and a COPY of which goes on EACH user's machine. Any other method is almost a guarantee of corruption eventually.

Do not let the database get too close to the 2 GB level or else it might become non retrievable. Again, it all depends on what you are storing and how fast it grows due to deletions of objects and such which require compacting. But I would say at about 1.8 Gb you would probably want to be mindful of if it doesn't grow too fast. If it grows quickly then maybe 1.6 or 1.7 Gb just to make sure you have time to do something about it.

[JeffG3209]

Thanks Bob.

The database SHOULD be split - backend with tables only and then the frontend with everything else and a COPY of which goes on EACH user's machine.

I'm new to all of this, so please go easy on me.

A "split" (correct me if I'm wrong) would be something like this.... Data would be stored on a server. The application (access program) would be on the user's computers. The application would call to the server for the information to populate the fields. I believe this is an SQL server.

If I am somewhat right, this is what a local guy I use for java programs has recommended. I believe his recommendation was an oracle product. I asked about MySQL and he explained the less expensive route of oracle.

For someone, intermediate with computers, but beginner with database functionality, would you recommend I give it a shot? I will say I almost paid someone to build what I have now, but it's simpler than I had imagined and I actually enjoy enhancing it. A plus side is when something goes wrong, I understand the direction to start troubleshooting and don't have to set en appointment to pay $60+ an hour.

[boblarson]

The split database can use Access for both the frontend and the backend. But I would suggest just staying with Microsoft and use SQL Server Express 2008 R2 (which is free and holds up to 10 Gb of data) if you want to go with a different backend.

I don't see why you can't just continue to work on it and post questions here. Works for a lot of folks.

[JeffG3209]

Time to get to work on the search button. I realize that the data will be increasing rapidly once I start putting it to work. Might as well at least be ready for the switch. Thanks again for your time Bob!

[JeffG3209]

By the way, just decided to do a compact and repair after you mentioned it.....

WOW, from 786MB to 333MB. Insane!