Access not strong enough?

[Fuga]

I read the Max size topic and I got a bit confused. (not necessarily by the french).

I have a db containing two main tables. The first one contains 210 000 records (growing at 500 a day). The second contains 250 000 records (growing at 5000 a day).

After reading the topic I started thinking - should I be worried about this? So far everything is working.

Also, I would very much like to add a third table to extend the db´s capabilities. This table would grow by 30 000 records a day.



Should I consider using SQL server or something? How about VB.

Fuga.

 

[Travis]

Access 97 can reach 1GB in size.
Access 2K can reach 2GB in size.

While you know how many records are being added (and since the records can be of various sizes depending on a table) have you tracked how big the MDB gets every day? By tracking this you will be able to make a very informed judgement on how to deal with this.

 

[Fuga]

How to count.

Thanks for the reply!

How do I count it? Do I sum the fields´different requirements
( date = 8 byte, single = 2 byte...(or whatever the sizes are)) and then multiply it by the number of records? Is it really as simple as that?

Is it all the same whether all data is in one table or in several, given the data is normalized?

Fuga.

 

[The_Doc_Man]

"How do I count it?"

I might take a simple-minded approach and just look at the folder containing the database with the "Details" option checked on the Window. That will tell you the KB of your database. When it reaches 2,000,000 KB you are going to have problems.

"Is it all the same whether all data is in one table or in several, given the data is normalized? "

Yes, as long as all tables are in the same database file.

However, some folks avoid the sizing limit you are about to reach by splitting the database into a front-end, back-end situation. Each part can be up to 2 Gb. But if most of your growth is due to tables, that isn't necessarily the answer.

Another approach is to build several databases, one for each individual table, and then link to them as external files. EACH FILE can be up to 2 GB. (Then the method of looking at the files in Window Detail view helps you to size individual table growth.)

The third approach, if you have it available to you, would be to use some form of external server. MS SQL server is OK, or perhaps ORACLE if you have it.

A fourth approach depends on whether you need to retain that data for all time, or whether an archiving operation is possible that would let you shrink table size by removing obsolete records.

 

[Fuga]

Thanks for the reply!

I can watch the actual growth for a few days to calculate when the limit will be reached, but how about the speed? I read that calculations tend to be slow when the tables are in the hundreds of thousands. Surely, this is depending on the field types. I was thinking maybe to change the double fields to single. Is that a good idea? Also, what happens to the speed when building FE/BE db or a new db? You won´t gain anything, will you?

I have been thinking about learning oracle for quite some time, but everyone has scared me off, so I guess it will SQL server, should I decide to use another program. It is not that different from access, is it?

As for the archiving, well the more records I have, the more accurate the statistics will be.

Fuga.

 

[pitou]

max size database

Hello Fuga

Perheaps the informations, I gave you last time confused you, I m sorry.

The sizes I gave you was also to deal with the speed of the SQL queries. It was also the result of questions I asked to many access-users, about this topic.

Pitou