Largest Access Database Worked With

[PTNL]

What's the largest MS Access database size (in MB) that you've ever personally worked with?
Please do not specifics that could/would indicate the owner of the Access database.

For me, the new personal record was raised yesterday to 441 MB, with 314 tables.

 

[slewed]

The largest I've ever worked with was around 800 mb before we moved it to SQL Server. This was after daily database compacts.

 

[MrMike]

521MB, and it has 20 or so linked tables from multiple SQL servers along with its own.

 

[jocooper]

800-1000 MB, about 20 tables

Its a custom POS system used by our salesforce when they don't have an internet connection.

 

[booyaa]

350MB and 58 tables

Custom solution for "membership" that is way old.. and slow.
Slowness is due mostly in part to poorly written queries I think.

Imagine a tire, it was great when it was new. Then there were holes so you patched them, and now its one big patch.

 

[PurpleFlux]

Man what kind of mdb corruption problems did you all have with these huge databases? Lots of backups I would hope.

My highest was maybe 150MB and not quite 20 tables or so. Been a while though.

 

[PTNL]

Man what kind of mdb corruption problems did you all have with these huge databases? Lots of backups I would hope.

My highest was maybe 150MB and not quite 20 tables or so. Been a while though.

I have no idea about past history on the mdb I'm now dealing with. After investigating further, it's become obvious to me that the web app and database have had countless "feature-add" done to it. I don't know about the corruption aspect, and a "Compact and Repair" only trims it down to ~390 MB.

However, a whole website redesign is on the horizon, and I'm going to do my fair share of stressing that the database tables be cleaned up and refactored before shifting any content into a new RDBMS app (likely Sql Server 2005).

 

[Korialstrasza]

5mb

 

[Y2K SE]

I've been working with Access databases nearly everyday for a couple years. I've had a couple over 1GB. Several have repeatedly hit the 2GB file size limit.

The most complicated one was built for my undergrad Senior Project. It was a financial analysis tool for potential clients that needed various reports to seek business loans or investors for property construction. The client mandated Access as the application environment. It didn't have many tables (~20), but it had about 20 forms, 4 reports that were completely manually populated through event procedures (including manual subtotalling and grand totalling) and 21 classes and modules with 9000+ lines of VBA code. It was about as object-oriented as one can get with VBA.

 

[bdwarr6]

At work our customer database is 1GB

stores customer info and also their billing history and such man its freakin huge!

 

[fyz]

Man what kind of mdb corruption problems did you all have with these huge databases? Lots of backups I would hope.

My highest was maybe 150MB and not quite 20 tables or so. Been a while though.

About 300Mb, 20 tables. I think when the database is big enough, it is more likely to become corrupt. So it is necessary to backup often. Besides, sometimes the backup will corrupt, too. I have ever used a tool called Advanced Access Repair to repair my Access mdb file. It works rather well but costs a lot. So I think keep more than one backups is very important.

 

[Liu Kang]

5mb

haha, ditto... anything larger, or anything that requires concurrent users, needs to use a "real" database.

I only use Access to build databases I can "upsize" to a third party SQL box -- assuming I don't have direct access (no pun intended) to the SQL box.

1GB?! you guys are nucking futs.... consider buying stock in Aspirin. ;-)

 

[pxc]

Yeah, really. Up to 2GB databases are served much better with MSDE, and MSDE is free. ADP makes a decent front-end for MSDE too if you're used to Access and are writing desktop apps.

Bleh if you're stuck with a web host that only supports Access.

 

[mikeblas]

The database for the website where I work is just about to hit 100 gigs; I'd still not call it "huge", but we'd never consider putting even a part of the data in Access. I think I presently have my firewall writing logs to Access *.MDF files; on a busy day, they're about 100 megs.

 

[vbrtrmn]

MS Access isn't a database. If you're (still) doing web programming with it; today is the day to stop.

 

[maw]

MS Access isn't a database. If you're (still) doing web programming with it; today is the day to stop.

yeah I agree. At the very least I'd use SQL Server Express or even MSDE instead of Access. Though back in more innocent times I did run a work flow tracking tracking site for the employees where I work using an Access back-end. Each user had to log in several times a day to update their work status. To my surprise it flawlessly handled an average of 1,200 hits an hour (8am-5pm) for a full three days. I think it was pushing several gigs when all was said and done.

 

[MrMike]

yeah I agree. At the very least I'd use SQL Server Express or even MSDE instead of Access. Though back in more innocent times I did run a work flow tracking tracking site for the employees where I work using an Access back-end. Each user had to log in several times a day to update their work status. To my surprise it flawlessly handled an average of 1,200 hits an hour (8am-5pm) for a full three days. I think it was pushing several gigs when all was said and done.

SQL Server Express is pretty robust for a free product imo. C# Express edition coupled with that while they're both free, as opposed to Access which costs money. To me it's kind of a no brainer.

 

[mikeblas]

MS Access isn't a database. If you're (still) doing web programming with it; today is the day to stop.

This is a lot of hyperbole. While it has its limitations--just like any other product--Access is a database, no matter how you slice it, and is appropriate for many different projects and applications. Discounting it completely out of hand is a disservice to yourself and anyone who heeds your advice.

 

[pxc]

While it has its limitations--just like any other product--Access is a database, no matter how you slice it, and is appropriate for many different projects and applications.

I agree with you there, but in my experience the only use for Access was due to someone who "knew Access" was around to make forms/reports/VBA code. And of course there's a lot of legacy internal applications that work well enough.

But I still don't get why people write new stuff for it. The jump to "better" languages wasn't too difficult from VBA to VB6 and now it's widened from VBS to .NET.

 

[tdg]

We have one at work that's approaching 500 MB. While not huge, with about 10 concurrent users, half of which are adding or updating data almost constantly, and the other half pulling data for reports, it corrupts frequently

 

[Wiseguy2001]

We have one at work that's approaching 500 MB. While not huge, with about 10 concurrent users, half of which are adding or updating data almost constantly, and the other half pulling data for reports, it corrupts frequently

That's the problem I've found in the past, it's quite stable unless you have more than a few users inputting data, then it's damned!

For really basic stuff i would go as far to say it is pretty good, however as soon as get past 1form:1table it starts getting [very] nasty. This is where .net and sql come in. Oh and the largest access database ~300mb which worked fine, where as a 120mb one with 8 or 10 users was a #&%$#!.