What is the correct database choice for a large database (1gb+)?

[Mightyginger]

I have an excel spreadsheet which saves data hourly to an Access database using DAO 3.6. As I understand the limit on the size of the database I can use with DAO is 1gb. My database is currently at 950mb and will be through 1gb next month.

I could move to ADO but I believe the limit on that is 2gb. Now obviously I could choose to split the database into serval smaller chunks but is there a some sort of driver I can use in VBA that will handle larger databases?

Any thoughts?

 

[Remou]

Unless you are using Access 2000, the limit is 2gb:

http://www.databasedev.co.uk/access_specifications.html#content1

It may be time to consider switching to SQL Server

http://www.microsoft.com/downloads/...C3-FDA5-4508-B0D0-1311D670E336&displaylang=en

http://lessthandot.com

 

[Mightyginger]

I am currently using MS Access 1997, but could easily upgrade to to 2000.

At the moment I am running this on a Windows XP professional PC at work. I have an Excel spreadsheet which updates and via a function runs code which grabs the data. My question is can I use my current DAO code but just point it toward a different sort of database instead of MS Access? Would it be better to switch to ADO? Are there limitations using these interfaces on the size of database they can access? And, finally, what database would be easiest to move to taking into account that I probably have a hundred tables or so, each table probably has 20 fields per table, and that I expect to database to run to several gigabytes eventually? I don't need anything too overkill as I'm simply going to be storing data to the database from one or two PCs and retrieving from the same PCs.

I thought I'd rather ask than go down one path and find out it was the wrong path to have gone down.

Many thanks,


Neil.

 

[danvlas]

but could easily upgrade to to 2000"

I wouldn't be so sure. Acc97 was based entirely on DAO. Later versions are based on ADO.

So you'll have to set a reference to DAO in your new Access 2000/XP/2003 database.

Not sure about 2007 - I threw it away instantly when found out there was no user-level security.

Have you compacted the database?
You said 'one hundred tables'. Are you sure there are not '100 variations of the same type of data'. Are you sure the tables are normalized?
If not, normalizing the structures will reduce the size of your database maybe 7-8 times.

If yes:

As Remou suggested, it may be high time you looked into SQL Server. Sooner or later your database will become too large or too important for Access.

You can use it as a simple data store: link the tables through ODBC and use your Access client to run the show. This is easy to do, but the queries will be slow (which is normal for Access)

Or...better... have SQL Server run the show through stored procedures and triggers and use the client interface just to issue requests to the server. You can use Access projects (adp) to do that and you'll combine the power and security of the SQL Server with the comfortable way of work of Access forms and reports.

However, this will NOT let you have local tables or queries and implies some reading on client/server configuration, views and stored procedures. If the server version is 2005 or higher, you can't use Access to create/edit objects on the server.

HTH


Daniel Vlas
Systems Consultant

 

[JoeAtWork]

If it were not for your statement of expecting to grow beyond 2 GB, the free version of SQL Server (Express) would likely be good enough for your requirements. However, I am suspicious that your size growth may be because of never compacting the database, or not using normalization.

Joe Schwarz
Custom Software Developer

http://www.plandev.net