Access Database on Laptops

[Fiona M]

I have created a database of customer contacts for use my a team of remote sales staff (10 -15 people) on their laptops.

I would like assistance in deciding how to keep these users upto date.

The remote users are based all over the country and will all be visiting their regional office at varying intervals - they will then have access to a LAN where the information could be updated.

I would anticipate that each user would be updating only their own customer's records.

I anticipate that I shall split the front end of the database form the data which would then require to be syncronised. The remote users have varied computing skills and therefore will probably not be updating the front end - can I secure the front end to stop them making changes.

The system should be kept as simple as possible for the remote users.

I am using Access 2003.
I have minimal network experience.

[MMcCarthy]

- can I secure the front end to stop them making changes.

Securing the frontend is not a problem as you can create an mde.

To create an mde of the frontend do the following:
Create a master backup of the mdb file as you won't be able to alter the file about to be created.

1. Create a backup copy of your "filename.mdb" frontend
2. Go to Tools - Startup and set the startup form to the switchboard/menu form you've created. Uncheck the "Display Database Window". If you have created a custom menu then change menubar to the custom menu and uncheck the "Allow Full Menus" and "Allow Toolbar/Menu Changes" boxes. Please note that if any of your users are experienced users they may find ways around this. It would require an experienced developer or some time to explain and code them out fully.
3. Go to Tools - Database Utilities - Make mde file. This will create an executable version of the file and this cannot be altered structurally by you or anyone else.

I would like assistance in deciding how to keep these users upto date.

The remote users are based all over the country and will all be visiting their regional office at varying intervals - they will then have access to a LAN where the information could be updated.

I would anticipate that each user would be updating only their own customer's records.

This part is a little more difficult.

Each of the frontends would have their own temporary backends which is what would need to be updated. These would need to be linked on each laptop and therefore the linked table manager tool would need to be available in the frontend to allow for this.

The first problem is whether the users would be updating any records or in any way changing the data as this would require a two way synchronisation.

If they each needed a specific set of data (i.e. their own customer records then this would require some complicated VBA code adapted for each frontend to specifically update only their records). It would be easier to update all records and only allow them access to their own customers based on login data.

If there is no two way synchronisation and all records are being updated then they would only have to copy the backend from the lan and replace the backend on their own laptop.

Mary

[Fiona M]

Thanks Mary

I would prefer my users to be able to view (and edit) all the records, however I would expect mainly edits to their own customers.

Do I need to synchronise all the replicas on the same schedule as the users will have access to the LAN at different times. Can they update their back end replicas on their own schedule.

[MMcCarthy]

Thanks Mary

I would prefer my users to be able to view (and edit) all the records, however I would expect mainly edits to their own customers.

Do I need to synchronise all the replicas on the same schedule as the users will have access to the LAN at different times. Can they update their back end replicas on their own schedule.

If users are updating their records you have an even more complicated situation. A standard synchronise won't work both ways. You will need a very experienced VBA programmer who can draw up a set of procedures to validate the data update in both directions. This is a complicated issue and I would advise employing an expert to deal with it.

Mary

[Fiona M]

If users are updating their records you have an even more complicated situation. A standard synchronise won't work both ways. You will need a very experienced VBA programmer who can draw up a set of procedures to validate the data update in both directions. This is a complicated issue and I would advise employing an expert to deal with it.

Mary

Mary

Can you give me a explanation as to why this won't work as I will have to get authorisation from my manager to employ a VBA programmer if required.

[MMcCarthy]

Mary

Can you give me a explanation as to why this won't work as I will have to get authorisation from my manager to employ a VBA programmer if required.

You have a master backend probably on the LAN network with all customers details. You edit the customers details. For example an address correction. The outside rep has a copy of the backend data on his laptop and he makes a correction to the customer name. How do you decide to update. If the rep's records are copied to the master backend first then the address change will be overwritten. If the rep updates his records from the master first than his name correction will be overwritten.

The only way this can be accomplished is by running validation procedures on all differences between the master and the locally held data and using some kind of update transaction records to decide which corrections take precedence.

It's an interesting problem with no easy solution and will very much depend on the data being held and operating procedures of your company. The issue should be assessed by an expert who can draw up a set of procedures particular to your situation or suggest an alternative approach.

If I can help further please let me know.

Mary

[NeoPa]

In case you're wondering if anyone else might have a different view on this - then I'm afraid not.
Assuming, as stated earlier in your requirements (post #3), that all remote databases will need to be able to take and upload updates to the main database on your LAN, then this is very complicated and there are very few who understand this situation better than Mary.
The downloading side is much less complicated on its own, but with the requirement to edit the data, that would be useless.

[Russell G]

Hi

There is a Product call DATA ON THE RUN that you might want to look into. We have used it for exactly what you are describing. It does have some limitations but works well with simple databases.

Goodluck

[Fiona M]

Following duscussion with the team leader - we will be able to have set each customer as read only for all salesmen except their own who will have edit rights. Only the team leader and administrator (myself) will have full edit rights, we both have access to the LAN at all times and can edit the main copy.

Could this main copy could then synchroised with each salesmans copy when they can connect to the LAN.

[NeoPa]

Just to clarify, that is the worst case scenario, as you will have to allow for changes synchronising in both directions.
Bear in mind, The number of different entities that may be able to edit the main copy is irrelevant. If you want anyone to be able to, there will need to be code to handle it (and check for updates etc etc etc).

[MMcCarthy]

Following duscussion with the team leader - we will be able to have set each customer as read only for all salesmen except their own who will have edit rights. Only the team leader and administrator (myself) will have full edit rights, we both have access to the LAN at all times and can edit the main copy.

Could this main copy could then synchroised with each salesmans copy when they can connect to the LAN.

I there is no requirement to update edited records then you simply need to write a short procedure (probably triggered by a menu option or a button click event) to copy the backend of the database from the lan and overwrite the backend copy on the laptop. A couple of things to keep in mind are:

• the read only status will have to be hard coded into the frontend of the database rather than based on workgroup status.
• there will have to be a set location for the backend on each laptop and I would suggest using the root (C:\).

Mary