Hey guys,
I could use a little advice on some fundamental database and file management issues I've been facing..
A little background: I've created and been working with a few databases at a brewery, one of them records the quality and production data via operator input (whenever they brew a batch of beer for example it gives a place to put their process records and generates all the paperwork and reports for them). This is a simple access backend located on a shared and it has been holding its own for the most part (thinking about investigating SQL Server or something a bit more sophisticated but that will be down the line).
Another database, however, has access to the raw production records generated by the plant software. These are all generated week by week and put into a .dbf file for that week.
Initially, to get access to this data I rigged up a flashdrive I called the "extractor" that you'd put into an industrial computer and a program will copy the lot onto the flashdrive. Then you'd put it into a corporate computer and run another program that copies all the .dbfs onto the C drive and crunches them into one file via a command line .dbf program called "cdbf". This file can be linked via Access so it's still useful as it gets updated.. However this is a clunky method of extracting data and takes a while - it's been hard to sell this to management too!
However we've finally had a positive change in our network and now I can access a designated folder on the plant network from the corporate network.. I could potentially make a script that auto updates all the records to this point a few times a day and this up-to-date data will be available to all. However I'm wondering the best way to implement this, considering I'll be likely building an Access frontend (or possibly excel via data connections if the users here aren't comfortable with Access), I figure there must be a better way to get the data together so it's useful and accessible.
- If I kept the method similar to how it is now I could have the host computer "crunch" the .dbf's several times a day when it does an update, however this could use a lot of processing power and time.
- I'm OK with vba and have created a routine in the past that does a similar crunch method but via access.. It seems to work OK but not as fast as the previous, and this will possibly become an issue now because the data is coming across the network not via flashdrive/local
- Is there a way that within SQL a group of .dbfs could be brought together in a more elegant way (there's a new .dbf generated each week too, for the new week's data, so if I write it into queries or something it'd have to account for this)
Anyway thanks for reading, and I'll appreciate any ideas or suggestions that anyone may have..
dBforums
>
Simple .dbf frontend - there's got to be a better way!
01-22-12, 19:57
Linear Mode
