How can I save Access Database when the rows of it reach a specific number?

[Golden_H]

I am using Labview7.1 and database connectivity toolset to deal with MS Access database.

the problem is:

Now I want to save the database as another name when the rows of it reach a specific number (such as 10000),

so that the database will not be too large.

And I'll delete all rows of the current recordset named "result_summary" so that my main

VI can still write reports into it.

I have tried using activex to get the "recordset->fields count",but the count likes to be column's(the database has 26 columns,and count is equal to 26 too).

Who can help me? thanks advanced.

[Dynamik]

Hi Golden_H,

I don't know what utils the DC Tools provide, but the following SQL comand will return the number of rows in a table:

SELECT COUNT (*) FROM YourTableName

Re: Clearing your table, again the DC Tools may provide some utils, however...

To move the table-data, lookup SELECT INTO and INSERT INTO on the web.  There's a lot of options to clear a table.  You could "DROP" it then "SELECT (*) FROM TemplateTable where 1=2", or use CREATE TABLE.  I think with the right "connection string"s, you can move data across .MDBs.

Your COUNT question was pretty basic - sounds like you need to do a bit of homework - and the web is full of great SQL examples.

Luck/Cheers.

[becktho]

Hi

What also could be not to forget is to compress the database. Although I did not make the experience because I do not have to delete data from the databases, I heard of Access databases which were not smaller after deleting data and needed to be compressed.

Thomas

[Dennis_Knutson]

That's correct, the size of the Access database does not change until you compress it and as far as I know, that cannot be done with SQL. I'd also like to make a comment. For a database, 10000 rows is a pretty small number. Even Excel can handle that number of rows. By scattering data into multiple databases, you are going to make data retrieval that much more difficult. I really have no idea exactly how many rows of data my database currently has but I do know that it retains a record of every test made since it was first created 7+ years ago. At the very least, customer service and quality can check to see when a unit was tested, how many times, test status, and who tested it. I would never trust Access to do such a job but there plenty of alternatives including the free MySQL database.

[becktho]

I also don't see a problem due to the number of rows.

I have a testrig storing testdata into an Access db. It know contains app. 200'000 datasets after running for about 2 years. The data is made available for the department quality through a separate user-interface in the LV application where they can select data by setting a time-range. The data can also be exported to a csv-file (which I limited to Excels max. of about 65000 rows). I did not have any problems up to know and with a size of 27MB I would say there is no problem.

[Golden_H]

Thank you all at first.

I am quite a newer to database.

Now when there are about 500 rows in my Access database,Its size is approx. 2MB.Our test is quite complicated.One product has 32 columns data including SN,test time,test station number and others. So when the rows=50000,Its size maybe reach 200MB.I don't know when such a recordset is so large,has it any problem? Or I should use another database tool except Access?

[becktho]

I think Access 2003 should not have a problem with huge databases, although I never reached the critical volume of 1GB. I can't give you advice about other database systems, but maybe a small "calculation". You say you have 32 columns - so either it is not so well designed or it is just different data. I assume the second which could mean your product is slightly complex. If you know how many items of your product are produced each day/year you can calculate how it takes to get a 200MB database. Maybe you'll see that it takes a 5 years time for instance.

[Dennis_Knutson]

Personally, I wouldn't use Access for anything. SQL Server, Oracle, MySQL, are much more robust. I currently use SQL Server to store test results. It also contains numerous other tables and is several orders of magnitude larger than 200 MB. SQL Server and Oracle can get to be kind of expensive so if you are looking for something cheap and robust, try MySQL.

However, you may want to look closer at the overall design of the database. There is a design methodology called normalization that will give you the greatest effeciency in database usage. One thing you want to verify is that you never have an empty column in a row. For example, I use TestStand and I have a mix of numeric, string, and Boolean test results. Instead of having a row that has columns for each type, there are separate linked tables for each type of result. I have, in the past, seen on-line references to databse design but I don't have any links to provide. Try googling "database design recomendations" or something similar and see what you can turn up. I have the help of individuals in our IS department who are much more capable than I am. If such a resource is available to you, by all means take advantage of it. 

[Dynamik]

Hi Golden,

You might consider using one of the DBs Dennis mentioned as the "back-end", and use MSAccess as the "front-end."  I also used SQL Server to store [test] data - you'll love TSQL & stored procedures - but MSAcess seemed like the most practical way to put a pretty GUI on the data.  Honestly, I'm early in this process, so may be wrong.

Cheers. 

[Dennis_Knutson]

Access does have nice tools for user interface design. SQL Server and the others don't come with anything. My IS department designed the front end here so I'm not sure about all of the details. I think they used Crystal Reports for some of it. All of the queries and reports are available as web pages on our intranet so that makes it easy for anyone, anywhere to look at data. LabVIEW also makes a nice front end. I had actually prototyped something in LabVIEW, published it as a remote panel, and showed it to IS as an example of what was needed.