Using Access with no network connection

[f0rg3tfu1]

Afternoon all,

Quick question for all those with Access experience:

We have a 2010 access database that resides on one of our servers on our network. Our techs travel quite a bit and do not alway have internet access at the sites they travel to. They usually make updates when they return back to the office, but we would like them to be able to make updates offline, then upload the updates when they return back to the office.

What is the best way to accomplish this? I looked into Infopath but not sure if it will work or not. Ideas or suggestions?

 

[Andrzejek]

What I would do would be:
Have a copy of the (server's) DB on their laptops, or just the portion of the DB relevant to what techs need. Tables would have a date/time stamp column. So when the tech returns to the office, they can (with one click) update server's DB with the data from their copy where date/time stamp is - let's say - yesterday. And the same time update their copy of DB with the data from the server.

I am sure others will have better ideas...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

The primary issue is typically the primary keys. For instance if you use autonumber columns and two remote users enter records into the same table, you will end up with duplicate keys. If these records don't have related child records, this isn't as much of a problem since using Andy's method, you can append them to the central table on the server.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Access use to be able to do this with replication. They did away with this with 2007 I believe. I think they did away with it to push using Sharepoint. If you have Sharepoint you can still basically do this using Sharepoint as the "network DB". If you want to "roll your own" replication, it may be far more complicated than Andy describes. Depends on your buisness and data rules. But basically there are unique GUIDS in every table beyond the PKs. The reason that it may need to be more complicated is to allow for conflict resolution. If I "update" my database before leaving to the field and so does another employee. What happens when I get back and update my changes to record 1, and the other employee comes back and updates record 1 with different changes not based on my changes? When access had replication and you tried to synch up it would maintain and show you changes for each conflict and allow you to decide what to keep/discard. This gets even more complicated when you have child records. So depending on what you need this can be very complicated or simple as Andy described.

 

[Andrzejek]

It looks like it would be beneficial to know a little more about your DB.
Do techs do any Inserts into DB? (new records)
Do different techs update the same record(s)?
Is there a possibility that 2 or more techs would update the same data (like MayP mentioned)? Etc.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Darrylles]

If their data inputs are relatively small - can't they connect to the server db's remotely?

Slow? Yep.
Sure? Yep.

If they're not writing novels - is it a problem?
If they are, c'est la vie.

ATB,

Darrylle