Thread: 20,000 Records a Day - Possible?
05-22-13, 11:19 #1Registered User
- Join Date
- May 2013
Unanswered: 20,000 Records a Day - Possible?
I think I already know the answer to this question, but figured I would ask you experts that I have been stealing ideas from for a long time. I know Access is not the best tool for this, but it may be all I have.
I have been asked to design a database that would track quality inspection data for a manufacturing plant - nine of them to be correct, but each plant would have its own copy.
We perform hourly checks on up to 20 features of one part. Each measurement will become a record in a table, linked to a Job Number and Part Number tables that contain the specifications, along with some list tables to populate fields like unit of measure, gage type, etc. Some plants have over 100 machines, so this could be end up generating 20K+ measurement records a day. I expect the table that will hold these data points to have ~15 fields.
- Primary Key (AutoNumber)
- Five number fields liked to list tables
- Date/time field
- Three numeric fields
- Three text fields
- Two memo fields
I built a test table and populated it with 500K random sample records. The MDB file ended up at 212MB - this is just the single table, not the supporting ones.
Data would be entered on the shop floor thin clients connected to a terminal server running the database. Probably 5-10 users in the database at once.
I have Access 2003. My IT support is minimal to non-existent as all resources are committed to an ERP system launch. This is to be a short term (12-18 month) solution until our ERP system is fully rolled out and we can then add on an integrated quality management system package.
On a scale of 1-10, with 10 being most doomed - how doomed is an Access 2003 database for this situation?
My goal is to purchase a stand-alone client/server version of the QMS package we want to add next year, but that may not be possible due to capital expense requirements.
Thanks for your help.
05-22-13, 11:47 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
the hard limits of Access are around the 2gb file size, so do your volumetircs based on that. calculate the actual size of the columns, and therefore the row size, multiply by the numbe rof rows expected and add say 15% to cover overhead, indexes and so on. if the app starts bloating then do a compact and repair to reclaim lost space
aside from that the world is your oyster
5 to 10 clients entering data shouldn't be an issue (if those clients are entering data only, in a correctly designed app access will support a lot more users.
to be honest if you are approaching the 2gb limit then you are probably beyond the point where a server backend woudl be usefull.
the real stumbling block is going to be the analysis / reporting side, as thats where you are going to be trucking massive amounts of data up and down the network
of course you coudl be cute and have a separate db for each machine and link as required to the other common db.
its the memo fields that could easily creep up on you and kill the app. Although memo fields are a powerful and flexible tool they can consume vast amounts of data. great for comments or where the data is unstructured. Ive yet to see an app where you actually need 2 memo columnsI'd rather be riding on the Tiger 800 or the Norton
05-25-13, 16:53 #3Registered User
- Join Date
- Feb 2012
I want to add a word of caution about autonumbers in a multiuser situation, especially when adding lots of records. I've received calls about duplicate autonumber problems repeatedly over the years, the last being just a couple months ago. I even have a little program to fix the issue quickly - it checks for the highest number in a given autonumber field, then repeatedly adds a new row and cancels it until the new autonumber exceeds the highest saved.
05-25-13, 23:38 #4Registered User
- Join Date
- May 2013
I see this as an opportunity for you. Access will certainly do the job and you are aware of some possible problems in the future (isn't there always).
It seems like they don't want to spend any money (isn't that always the case) and they know you have the skills to create and develop a system.
I would point out to them your concerns, the risks, and because they don't want to spend any money they will want you to proceed anyway. So at least document the risks (not that they will eventuate, maybe) and the management that were told.
Now develop and deploy your system. So what if problems arise in 2 years time (slowness, too big etc.), they were told.
ps. Could consider different backends such as Firebird, Postgres which are FREE. Have this in the back of your mind when developing the Access tables so if you do need to quickly convert there's no nasty complications at the front-end level (esp. SQL syntax).
Last edited by essaytee; 05-25-13 at 23:45.