Hi listers,
In my applications,I use MS Access to store data production and to make reports on the past production. It works correctly when the quantity of data is not too important. I store every day 1000 products and 20 data per product. -> 20000
data per day. When you are in March and you want to interogate the DataBase to get information on January, the time needed is acceptable ( less than 30 s). some requests could be more
complicated than a simple sum. But when it is the end of the year, the same information could take 1 or 2 minutes. (Pentium 200 MHz).

Now, I have applications when the number of products stored is about 20000 products per day with 20 data per product -> 400000 data per day.
I am not sure that Access fits this amount of data. I hesitate to program in C++ the requests. It takes more time than programming a request with Access. Another point is the reporting
of data. Making a formatted printing with C++ is quite difficult. Perhaps I can use software like Crystal reports.

Do you have some experiments with production data exploitation? What kind of tools are efficient with time calculations, quality of reporting and printing and easy to program.?

Thank you.

J-F Portala
SoViLor company
[email protected]

 

I believe you are correct in thinking that the data quantity will be a problem with MSAccess. You are going to need a more robust DBMS like Oracle to handle your requirements. We are completing a project in which we are capturing gas production values among other pieces of data and storing it for at least 7 years. For this, we
are using Oracle for the DBMS and have developed a GUI and reports using Visual Basic.

You said that you have developed the reports you need in MSAccess. If you change your DBMS, you may still be able to use MSAccess by linking to the DBMS tables and running your queries/reports against them. Beware, that the SQL syntax for
Oracle are not 100% compatible with MSAccess but enough so that the modifications you will need to make may be quicker than learning a new reporting package.

Manny Hellstern
Mustang Engineering
Houston, TX

 

Hi, J-F,

<< [MS Access] works correctly [with] every day 1000 products and 20 data per
product. -> 20000 data per day. [...] Now, I have applications when the
number of products stored is about 20000 products per day with 20 data per
product -> 400000 data per day. >>

First, regarding your existing MSAccess database performance, verify that all fields used in "WHERE" clauses are indexed. This can make a 50:1 or 100:1 (measured!) performance improvement for simple queries when you have a lot of
rows.

Secondly, MSAccess has its place, but as you imply, there comes a time when you need an industrial-strength database... and server.

"Lurker John" G. Boland, president
VisiBit Corporation
One Parker Square Suite 408
2525 Kell Boulevard
Wichita Falls, Texas 76308
940.322.9922
940.723.1478 fax

 

Hello J-F,

Access should be able to handle 4G of data. (Long Data Type)
It seems that you are collecting ~1.5M of data a day...
Do you have your tables indexed (preferably keyed index) ?

Can you break down the tables by months into an Archived database?
You can link queries to these external tables to do your reports.

I can't believe that C++ is slower than Access.

Crystal reports is easy to use.

I do my database in VB5.0 Profesional ver. and it is easy to use.
VB allowed me to make complex reports easy.

1 to 2 minutes to generate a report from data that size is normal for a slow P200 like you've got. Get a newer PIII-700. It will reduce the time by 5 to 10 times!

Good Luck, Dan B.

ps. A good place to ask for help would be on the
comp.databases.ms-access newsgroup

 

It may be interesting to note that Microsoft is obsoleting the current MS Jet Engine (database driver under MSAccess) and replacing it with the Microsoft Data Engine (MSDE.) MSDE is essentially the SQL Server backend - minus the management tools and support for more than 5 (?) concurrent users.

The biggest problem with Access is that the database cannot be compacted when the database is being used. In an Industrial application this means that the database cannot be compacted while you are logging to it. Hence you need to shut the application down to compact the database.

Pete