Three Million Records

[BikeToWork]

A client wants me to develop an Access database to hold 3,000,000 rows of data that they have in a different format. I know the size limit for an Access 2007 database is 2GB. Although having a table with 3 million rows of data won't necessarily make the database over 2GB, it would be pushing the limit. Wouldn't the database be sluggish and unresponsive with that many rows? What kind of workaround is there? I'm pretty sure the client does not have a more robust RDBMS. Would it work to split the monolithic 3 million row table into multiple tables. Any advice is most welcome.

 

[vbajock]

It won't work, don't waste your time.

 

[vbajock]

Oh, and the work around is to buy SQL Server.

 

[BikeToWork]

Maybe SQL Express is a viable option. What do they cripple in the free version of SQL Server? Can multiple users access it if it is used as a backend? I am leary about trying to make a 3 million row Access database.

 

[vbajock]

SQL Express has a 4 gig limit. Off hand, I would say it would handle a 3 million record table. The real drawback to Express is that if you don't own the SQL Server full version, you won't have the SQL Design Studio interface that allows you to work the tables in a GUI. One way around that is if you own Visual Studio, most of those GIU features are built into it - using Server Explorer you can connect to the db and then create databases and tables,indexes, etc and then use an ODBC DSN to connect the tables to Access.

If cost is the problem, the other option is MySQL, which is free and full featured, and there is a big community of users out there supporting it via forums and such.

 

[dhookom]

I would definitely consider SQL Express since it is free. You can find a comparison at Compare Microsoft SQL Server Editions.

One of the primary limitations with Express is that it supports only a single processor. Otherwise, most of the functionality you want should be available.

There had once been a limitation regarding the number of concurrent users but I think this is gone.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Visual Studio 2010 includes SQL Express so you can spin off a DBMS at no cost with your app to the user, so a copy of VS at $600 sure beats 2 grand and up for SS