Using Notebooks with Field Data to Append Main DB

[JonP46]

Hello:

I was wondering if anyone has any experience using laptops to append the main database. I have a group of field biologists that are presently recording thier observations on paper and later (back at HQ) inputing the data. They are recording the movements of tagged animals.

The biologists want to speed up the process by inputing thier field observations directly into a laptop. At the end of the day, they want to hook the laptop up to a spare network connection, press a button, and append the working database.

A particular observation encompasses five tables: animal, tech, observation, and two association objects.

I have a few questions. What difficulty is involved in appending from five tables? What are your suggestions on automating the append process? Is using laptops the best solution? Is thier any good way to assure that all data is appended properly?

Thanks in advance for your help.

Jon

 

[RickSpr]

I did something like this a couple years ago, gathering data on division computers' Y2K compatibility and collecting it in a central database. It was a royal pain!

The main problem is the possibility of duplicate keys. I had to use Autonumber fields to generate unique keys, and of course these became foreign keys in the child tables. But in merging the data, I had many duplicates to contend with, and of course you can't modify an Autonumber field in the parent table. I had to write a bunch of code to copy a parent table record to the central table, then substitute the new Autonumber value in the foreign keys of the child records as I copied them--all in code. Plus, I had duplicate records, and duplicate keys on different records, and I had to present the differences to a user for reconciliation.

Even if you don't have any Autonumber fields, you might still have a problem with duplicate keys. If there are no duplicates, a simple set of append queries will do the job for you (just make sure to do parent tables before their children). But if you do have duplicates, you'll have to resolve whether it's actually a duplicate record, which you can ignore, or a duplicate key with different values in the other fields, which you'll have to resolve somehow. If you only have duplicate records, you can still use an append query, ignoring the errors. But you might have to write code to figure out whether they are, in fact, duplicate records rather than just duplicate keys.

It sounds like the data collection is already in progress, in which case the above comments apply. But if it hasn't started yet, look into making your central database a replica master, and put replica copies onto the laptops. You can then use the synchronization functions in Access to import the data. You'll still have to resolve the conflicts, but at least you won't have to write code for it. The code I wrote was basically the same as a synchronization, except that I displayed the conflicts to a user instead of writing them to a table. I wish I had known about replication before I wrote all that code! Rick Sprague

 

[JonP46]

Thanks Rick:

I thought it might not be a piece-of-cake. One other option that I was considering was the entering of remote data in a denormalized form. In other words, making one big table containing all of the data. By doing so, I could simply append a demormalized central database. Then, on a regular basis, convert the denormalized database to a normalized database reusing the conversion code.

I have never used the syncronization facilities in Access. I will read-up on them.

Thanks again,

Jon Peterson

 

[JoeMiller]

I have used the replication facilities in Access for some time, and as long as your database is well designed and the relationships are instituted properly you shouldn't have any problems.

I have a master database I wrote that stores 30,000 pieces of equipment my company is responsible for servicing. We do preventative maintenance on those machines once a year and collect many pieces of data that go into the db. Our techs in the field have replicas of the master and then bring them in and sync them up with the master.

Issues that can happen:
1) Data goes into the master without being reviewed. The technicians are not extremely sensitive to valid data capture, which is not a problem, just makes my job harder.
2) I don't like to leave replicas out too long because they can "expire." Meaning they can't sync up with the main.
3) Synchronization errors can be arcane to decipher if you are not intimately aware of how the db is constructed. Fields that have "No Duplicates" (not a primary key) set on them can be a headache when two techs put in the same AssetID (a number we assign when we PM a machine the first time).

These are all minor issues though, this db has been in service for about 3.5 years and I don't have any major complaints. Make sure you read up on it before you do anything though! It's a process where "You can't go back!"

My two cents...HTH Joe Miller
joe.miller@flotech.net