Database size

[ccordner]

Hi

I am a bit concerned about the size of my Access Database. There are no pictures or anything, just a lot of tables and queries: yet the size is 150-200MB already!



I am a bit concerned about how much this will grow when the tables start to get a lot of data in: it's manageable when working on a wired LAN connection at the same location as the server, but when working via our WAN or on Wireless it can take ten or twenty minutes to save and close!

I have set it to automatically 'compact and repair'. I did think about trying to modify the field types to save on space, but many of them are links to autonumber fields in other tables, so they have to be Long.

Is this a common problem or is it likely I've made some glaring error?

Thanks
Chris

[bigroo]

Hi ccordner,

If you have a lot of tables and lots of forms, this will blow the database size out, Also Linked tables dont help too much either.

I compact all my databases on close to try and keep the size down as much as possible.

I have one database which imports 9 text files which I run daily, some of the text files which I import, 3 infact have about 800,000 rows. All the other 6 text files have around 200 to 300,000 rows, so this is big. After compacting is around 460meg.

James
Austin, Texas

[John_G]

Hi -

Try splitting your database, so that the data is in one .mdb, and everything else in another. When you say "lots" of tables and queries - how many is "lots"? How many records approx. are in the tables?

John

[ccordner]

Thanks for this.

My database is in two portions: one with tables, one with queries and reports etc.

The tables bit is approx. 100MB, the queries/reports bit is around 180MB.

The database size isn't really a problem on my computer, but most people access it via a network and it can be slow: especially shutting down.

More importantly, I access it via Excel, through vba Code and I think (I haven't been able to prove this) that what is happening is that when the code closes the database, it doesn't always close it properly (because it takes so long) and corrupts the database, so I have to keep replacing the 'Front End'. This of course causes problems when I'm not there to do it.

When I say lots, I mean maybe twenty tables, about half of which have a negligible number of records, but the rest have maybe four hundred records: which will rise drastically (That's about two weeks' worth of information and I would hope the database would retain it for at least twelve months without archiving).

Given that much of this information is just integers, it seems very high!

Is there a database package which might be preferable? I need to be able to access it via VBA code from Excel and hopefully import data from Excel (so I could transfer it across from the old Access file).

I'm starting to have reliability concerns about Access, which strikes me as odd!

Thanks
Chris

[John_G]

Those database sizes do seem to be high, especially the data part. As for the slowness bit - are all your users accessing the same front end on the network? If so that is the wrong approach - each user should have the front end on their own machine; having all users accessing the same fornt end file is just asking for problems, and that could easily be the source of your corruption issues. It could also explain the slowness, since all users are competing for the same resources, and it increases network traffic as well.

You said that you have implemented compact and repair on close. From my own experience, that does not work for linked-table databases, and it's not a good idea anyway if there are multiple users. That could also explain the very slow shutdowns - if you compact and repair on close, how can MS Access do that if other users have the database open?

Work on getting each user their own copy of the front end, and see if that helps.

John

[Albert_Eines]

Description: Kernel for Access Repair is the technically advanced tool that helps in retrieving lost Access database by repairing MDB and ACCDB files. This tool performs a recursive scanning of the lost files and folders and successfully retrieves the data back.