Access DB too large, ideas?

[anoble1]

I have a Access database that is over a gb. I have 2 tables that I dump monthly info in. When I open the table it takes over 5 minutes for it just to open the table.

Any suggestions? Don't have a SQL Server or money for one.

[twinnyfo]

anoble1,

What do you store in your tables (I know, data--but what kinds)? Is your DB fully normalized (this can help significantly on wasted resources. Also, have you compacted and repaired your DB recently? Be sure to make a back up first before you compact and repair.

[anoble1]

You may have to help me out a bit here. I compact and repair every few days for sure. Do you mean what data types I guess?

[Seth Schrock]

FYI you can get SQL Server Express for free.

[anoble1]

I don't know much about SQL Server. But, I do not have a dedicated PC for SQL Server. I have my PC. I am the only person who uses this though. Wonder if I could throw that on my pc and just store data on that and link it?

[twinnyfo]

My question was in terms of types of data. If you have attachments included in your database, your file could grow rather large. Keep in mind that I have no idea what your DB is used for, nor how your tables are built. But, from a "basic, generic MS Access DB user" standpoint, a DB over 1 GB in size is almost inconceivable to me.

A coworker of mine has a DB that is 600 MB, but it is a RAW DATA pull that contains hundreds of text fields and the entire thing is 100% un-normalized. I use a RAW Text DB pull that is about 300 MB, but after I extract the necessary data and normalize it for my purposes, it is about 150 (still a lot).

My point is, I'm just wondering what you are storing that contains 1 GB of data. not that there is anything wrong with what you have or how you are doing it. Just a little amazed at the size of the file for a low-budget operation.

[anoble1]

Attached is the data that I import into Access using the Wizard. No attachments.

[twinnyfo]

Only 15 fields? It looks like it is all text, too. It might be possible to normalize the data during the import process. I assume you receive the data in a spreadsheet? how many lines are imported each time?

our friend JForbes could probably calculate how many rows you would need to max out Access with only 15 Fields, but it must be a lot.

[anoble1]

Varies.. 15,000-50,000 records in a sheet. When I open the table it says running query at the bottom of access. Wonder if it would help if i made an import function.

[twinnyfo]

I would definitely recommend looking into that. I use an import function rather than a straight import. Your data looks highly un-Normalized and this could save considerable space.

And.... you can practice your coding skills!

:-)

[jimatqsi]

Do you compact the database from time to time? It could be the actual size is less than you think? Look under the Manage option for Compact and Repair (or under tools for older versions of Access)

Jim

[twinnyfo]

Hey, Jim! Good to see you again. Seems like you have been away for a short while. Hope things are going well!

[jforbes]

Haha, Yeah, that would be a lot of records... a lot, sorry for being late to the party Twinnyfo, I spent sometime in Germany for work. It was a blast and they appreciate math. =)

jimatqsi is on it. If you create a lot of records then delete them, your db size wont drop on its own.

Make sure you clear out (delete) all your temp tables after you perform an import. Another guess is that you have some appendix tables that are no longer used. Even at that many records at once a month, your no where near a Gig. I can do the math tomorrow, salesmen permitting. ;)

[twinnyfo]

All,

I am sure there must be "something" creating such a large DB file. But without knowing what data are in the tables, it is all conjecture.

My first thought was (Post #2), as was with many others, to Compact and Repair.

Outside of that, proper Table Structure could help.

@Anoble1, can you post the Table Structure you have for this Table (or tables, as the case may be). Please include the Field name and Data Type. This may help us redirect some structural changes.

[anoble1]

I compact and repair the database a few times a week. I have a feeling something is wrong with these data types. I took over this database and it has always been done with the Access automated import from Excel.

After looking at Capture2. Some of this stuff I am not familiar with, such as Field Size and Format. Sounds like I need to make a new table