capacity (a2000)

Topic

New Reply

I was asked recently about setting up an Access database to handle comma delimited text imports because the imports had grown too large for Excel to handle, i.e., nearly a million records. Before I get any deeper in thinking about how to set it up, my initial thought is that it would be smarter to upsize from Access and use SQL Server instead because of the sheer number of records.

I’m use to working with small databases with records numbering in the 1000s or 10,000s, so this one is outside my range of experience. I’m looking for guidance from someone with experience with dbs of this size. Based on the little I’ve told you, what do you think?

esw

Reply | Quote

Replies

It really depends on the size of each individual record, but if you are approaching a million records, SQL Server would be a much better bet. For one thing, Access databases in Jet 4.0 have a max size of 2GB, so you might start bumping up against that pretty soon. In addition, if you are running queries across the network, it would really hammer your network and make things pretty sluggish. For that size of table, I would recommend the full version of SQL Server, not the Desktop or MSDE version.

Reply | Quote

A lot depends on what you intend to do with the data once it is in the db. I used Access 2.0 with tables consisting of record counts in the 100,000’s. But all I did was simple reporting and filtering. For more complex tasks I would definitely recommend SQL Server would be your best bet and may be worth the effort in the long run.

Reply | Quote

It is not really the total number of records, but the number of records in each individual table that really matters. The more BIG tables you have, the more difficult it will be to maintain good performance with Access. Good indexing will help, but at a point in time your performance may not be what you’d want. If you have the choice of upsizing to SQL server now, I’d probably lean towards that solution.

Reply | Quote