Would I be able to do this task using Access?

[jambog82]

Hello all,

I am looking for a solution to be able to edit a database offline and be able to sync the data between three computers (while online or by some sort of merge).

I have three people out in the field with tablet computers and no internet connection in most places. As we visit customers, I want to be able to add, edit or delete information on their account and also have them sign to verify the information is correct. When the three field agents get back into the office, I would like to be able to sync the updated customer information between their three tablet computers, and also print out a sheet for each customer account with the updated information, including an image of their signature.

What would be the best solution?

I am thinking this could be achieved with Microsoft Access, but I know very little about it. Which direction would I start moving in to learn how manage this type of data. Access Forms or maybe Access Sharepoint apps?
I have a subscription to Lynda.com and also packt publishing, so I have plenty of learning resources available...I just don't know what direction to start learning in. Is there a better solutions to this dilemma than access all together?



Thanks

[CJ_London]

It's late and I'm about to hit the sack, but a couple of points

re your tablets - access is an Office application and will only work on a full windows OS - I seem to recall 8.1 was the first to be installed on tablets, previously tablets used what I think is called windows ES which does not run Office. If your tablets don't have win 8.1 then access is not a solution.

re syncing - it can be done, but you need to think about whether your people need a copy of the full database (all customers) or just their own customers and whether they 'share' customers - so need to be updated with new records. The other factor to consider is whether records can be updated in two locations at the same time (e.g. a customer record has been copied to your file person. That person makes a change and also someone in the office makes a change - when you then want to sync - which is the 'right' record.

Any db will have potential 'issues' around syncing so it is important you understand what you actually require before looking for a solution

Access is actually two applications - the forms, reports, code etc and the backend database itself. In access this is Jet (or ACE since 2007), but you can just as easily use sharepoint, sql server (or express) MySQL and others instead for the backend.

Access 2003 and earlier had the ability to have a master copy (which would reside in your office) and a replica (which would reside on your peoples tablets). This facility was lost from 2007 but can still be recreated in vba if done correctly.

Having a web based solution does not necessarily form part of your solution if your people are returning to the office to sync, but could if they were updating from home or another location

[jambog82]

The tablets are running windows 8.1, so that isn't an issue. I'm not to savvy on what microsoft access in capable of. My first attempt at doing this was setting up an apache and PHP/MySQL webserver on the tablets and running a website locally where the MySQL database can be edited with a front end in a web browser, even offline. This works, but I just figured there should be an easier way than having to set each tablet up as a webserver to run a PHP/MySQL website offline. Also, it doesn't allow syncing of the data. Syncing isn't extremely critical, and for the most part customers are broken up into locations...but there has been occasions where one agent will run into a customer outside of their location, and would be convent if each agent would have access to the entire database of information instead of just a portion, but not entirely necessary. Syncing would be done only at the office, so it wouldn't have to be web based. Would you say that access would still be a better way to go, or does setting up a server on each tablet sound at least somewhat practical? I already know enough PHP/MySQL to make this happen, it just seemed to me there might be a simpler more practical solution that I could learn.

Also, as mentioned before I would like to have customers sign the form. The way I have this set up with the PHP site is simply a javascript signature field that captures the input. Can a signature field be implemented with access?

[CJ_London]

Access is certainly capable of doing what you require, but if you know what you are doing with php/MySQL I would be inclined to stick with what you know.

One of the potential benefits of Access as the backend (as opposed to MySQL) is that it is simply a file, which can be copied across to your network when they are in the office - this may make syncing easier. You can still use php for your front end to control data input. With regards a signature field there is no such animal in access but you can store image files - so if your routine captures an image of the signature it would not be a problem.

with regards syncing (regardless of access/mysql) the principle would be to

a) incorporate into each table design a creation timestamp field, an updated timestamp field, a 'lastsynced' timestamp field and a replicationid field - the latter as a primary key (select autonumber of type replicationid)
b) develop a routine to go table by table, comparing the records in the replica table with the ones in master. This typically involves several queries along the following lines
.... identifying new records in the replica and appending to master based on replicationid
.... identifying new records in the master and appending to replica based on replicationid
this next bit is the tricky bit - I may have missed something
.... identifying records in master and replica which match on replicationid and lastsynced timestamp but where the data is different and updating the appropriate db based on the latest update timestamp being the correct version
.... identifying records in master and replica which match on replicationid but not lastsynced timestamp - further analysis and prompt for user to decide which record is the appropriate one