Database on laptop then transfer to desktop

[bigalpha]

Semi-theoretical scenario:

We take our current database from my desktop workstation (the db also has a server location) and put it onto a laptop. We then take the laptop out into the field and we change data in the database. That database (from the laptop) will get loaded back onto the desktop, then re-uploaded to the server.

I don't have a way of connecting the laptop directly to the desktop nor the server. My process will include moving the actual database files.

1. Is there any special design features that need to be taken into account for this?
2. Will I need to track 'last modified' times in order to reconcile the newest data?

 

[MarkK]

When you have a copy of the database out in the field, will users need to edit data on the server while you are out?

 

[bigalpha]

When you have a copy of the database out in the field, will users need to edit data on the server while you are out?

I don't know for sure, but my feeling is that it will happen.

 

[MarkK]

Well, your simplest solution is keep one authoritative data file. If you take it with you on the road, you have it, and don't allow edits at the office. Maybe create a big flag at login for everyone there that edits will not be saved. Period. Then when you get back to the office, copy your edited file onto the server and remove the warning.

Other solutions will get a fair bit harder almost immediately, like, if users at the office must do edits while you have the file, then you can see that you are going to have to create a way to parse everything in one file, and see if it's different in the other file, and then pick which one is more correct. <= Tricky.

Or put a database online, which is also harder, and then you might as well not use Access.

Etc...

 

[bigalpha]

Yeah, I assumed that comparing records was probably fairly difficult. My initial thought was to have a 'date created' and 'date modified' for each record in all tables, but that's pretty intensive.

Thanks for the perspective!

 

[nanscombe]

I used to employ three special fields in my databases:

ID - A programatically generated Long ID based on instance of the database

Deleted - Boolean flag to signify a record is no longer in use. if you physically delete a record, but consolidate it with other datafiles, the record may reappear ay some point.

Datestamp - The Date/Timestamp of when the record was last updated.

The scenarios that my creations were used for would be for inspections of various items.

The base data would not actually be changed out of the office but only be used for reference.

It would be inspection type info that would be entered / amended in the field and fed back into the office copy of the database later.

 

[bigalpha]

Yeah, ours is essentially for inventory control of wastes that are getting shipped out of our facility.

 

[nanscombe]

Continued from the post above ...

I would have two versions of each table in the database. The first would be the table proper (as a link) where the data was stored. The second would be a link to where the external data would be imported / exported .

I would then have two Update queries (Source -> Target and Source <- Target) which used an outer join between the tables. They would be linked on the field ID and would update if the Datestamp of the target table was either missing or earlier than the Datestamp of the Source table.

 

[nanscombe]

From what you say about being an inventory I imagine you'll probably have a lot of data which would be static as far as the laptop was concerned; Locations, people, substances. These will probably only travel in one direction (Desktop -> Laptop)

Then there will possibly be other data about how much of a substance in a particular location and possibly where, and when, and how much will move. That data will probably flow in the other direction (Desktop <- Laptop).

You may well add the initial data from the field onto the laptop, about how much of what substance is stored where, then update it on the Desktop when movement is planned and actually occurs.