Office & remote access

[Canedo]

We have a secured database which needs to be on a server so that is available for six users in our office. In readiness, I have split the database. I am planning to have the front-ends on the office PC’s and the back-end and workgroup file on the server in a shared network drive. Additionally on each PC, I would give them a shortcut to open the database using the mdw file. I see from posts I would need to secure the back-end again. I hope that I have understood correctly so far.

A last minute requirement arose! Three people will be obtaining new customers and scheduling appointments remote from the office using laptops. The only database feature required is to add new customers. My company does not want them to do anything else or have access to existing customer data. I have set a security profile up accordingly, hidden all tables and have everything running from queries.

What I would like is for these users to enter customers details in an ‘offline’ style mode and then connect to the server daily and download the new data.

Replication and synchronisation seems to suggest a solution. I have downloaded some MS whitepapers and have been busy searching posts for help on how I could get this to work. I would really appreciate some advice from someone as I have never tried this before and I am under some tight timescales.

Would the laptop users each have a copy of the front-end and replica of the back-end? Would they then synchronise the back-end? Could this operate with the office users working as I intended in the first para above? Would the office users have to now have replicas? Would this mean the laptop users would have a complete list of existing customers on their laptop? What would be the best way for users to synchronise? Do I need to purchase any additional software; I currently have Windows 98 & Access 2000? What else should I consider.

Guidance on how I should proceed would be very much appreciated.

Thanks

 

[MikeAngelastro]

Create a limited front end for the laptops. It should only contain a preliminary customer table and a link to the customer table in the back end. When they are off line, they can add customers to the preliminary table. When they get on the network, they can run an append query to attach the new customers to the backend customer table. If you don't want them to see the link, hide it.

 

[Canedo]

Thanks for getting back.

After reading many posts, links and articles on replication, I feel this would not be the best solution.

At the moment, to enter new information there is a main customer form. This holds some sub forms and forms which are linked to the customer form by customer ID. All of these forms are based upon queries with owner access. I presume I would need to create duplicate tables and queries which can be used for the laptop users so they can enter data in an ‘offline’ mode. All forms for laptop users are in an ‘Add only’ mode.

If the laptop users could run an append query when connected to the network, how could the data be deleted automatically from the laptops? I have never attempted anything like this before and confess I am a little confused. The company wants data to be entered ‘offline’ and once uploaded into the main db, not held on the laptop users laptop.

I would very much appreciate any further assistance.

Thank you

 

[MikeAngelastro]

Hi,

Once the new records are appended, run a delete query on the laptop table where you join the records of the laptop table to the database table and delete from laptop table the records that are in both tables.

If this doesn't work then you will have to use a cursor or code. Do a DLookup on the database table where the criterion is based on a lookup to the laptop table.

Or just set up error trapping in the proc that appends the laptop data to the database. If no error occurs, you can assume all the records were appended freeing you to just empty the laptop table.

Mike

 

[Canedo]

Thanks Mike for your responses. Your help is appreciated. Apologies for any ignorance on my part, but I am still trying to get a solution to work.

I have built a separate db for laptop users with forms to enter new customer details. These forms have 9 forms/subforms linked by Customer ID. In total there are 6 tables used to store new customer data each with an Autonumber primary key. For each table, I have a different primary key for the laptop db to the one in the main db (eg for Customer table it is CustLaptopID). I store this in a additional field in the main db in an attempt to avoid any key conflicts when appending data to the main db tables. I have set up two macros; one to append all tables & another to delete data.

I was intending for laptop users to remotely dial into the network and do this.

My queries are:

- I can append data for one table ok, but with multiple tables - no. I know this has something to do with the way I have a new Autonumber field in the laptop Customer table (CustLaptopID) but cannot figure out how to solve this.

- If I can get the above to work, I need to be ensure records are deleted only when correctly appended. I need to delete otherwise duplicate records will continue to be added and my company does not want old customer records held on laptops.

- Can you tell me more about this error trapping procedure which will delete records on a laptop table if all records have been correctly appended to the main db. I would not know where to start with this.

- I have also been looking at ways of emailing the data in the tables for someone to remotely import in. However, I still have the problem in the first bullet above to resolve.

Am I still looking at my problem the best way?

Thanks again for your help and the time you have taken to read & respond to my queries.