Office & remote access

[JamesCanedo]

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

James

 

[simmsey]

Replication is a possibility. But be warned it opens up new possibilities for problems unless a strict structure is kept to.
To do what you are suggesting you'd probably want to setup partial replicas on the laptops to avoid having security concerns of all your data on the laptop. You'd also have to setup the minimal front end.

On the other hand you are not going to use any of replications main strengths. Your data access needs are so simple you might be better off avoiding replication altogether.
Why not have a VERY simple database for the laptops that they enter new records on. That data is exported out to your main office and the new records are simply imported in.

There are many ways you could write the data out (using VB or a report), email or other method it back to the office and read it in as new records to add to your main database.
It's largely dependent on how secure you need the data to be in transit.
Given your requirments this will be much simpler to create and administer than replication.

Hope that helps. If you are still interested in Replication

These links may be useful:

Microsoft replication usenet.
news:microsoft.public.access.replication

Website of Michael Kaplan an expert on replication and regular to the microsoft replication forum

http://www.trigeminal.com/index.asp?1033

 

[JamesCanedo]

Thanks for getting back.

After reading many posts, links and articles on replication, I feel this may 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.

Could the laptop users could run an append query when connect to the network. How would this work and could the data be deleted automatically from the laptops? Am I looking at this the best way?

I am keen to learn about the best ways (VB, report, e-mail) to write this data out. 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.

 

[simmsey]

You could run an append query to add the records to the main database. That probably isnt a good idea over a phone line (or other remote connection) but would be fine if they are connecting to the network directly.
When that has run successfully you just need to run a delete query.

The alternative is to try and set it up as an offline task.
e.g. export the data to a text file. Transfer the text file to the main office and run an import.

With either method you are probably going to want to automate the process using VB to ensure that everything happens OK. (You want to be sure that the records added OK before deleting from the remote db)
There are many different levels of automation you could take this to. You could go as far as automating the creation of the email message. Setup a special email account at main site and automate collection of the emails and the importing of data. A completely hands off system. By the sound of it your experience suggest a simpler system requiring more manual involvment is a good starting point.

You might want to consider carefully when the delete is run.
It may be better to have retain the records on the remote laptop for a while (as backup) before deleting.
Your forms can be setup to not allow access to these "old" records so the user won't see a difference between the records being deleted or not. If something goes wrong with an import at some stage you have the records to go back to to try again.
A nother suggestion if you are running the offline update system is to send a message back from the main db that lists the ID's of the records imported which can be used to delete the appropriate records in the remote DB thus ensuring nothing is deleted until a confirmation of import is received.
I would suggest you try and setup the export/import to uniquely identify these records (with suitable ID) so that if import is accedentally run twice the records do not get duplicated.

In terms of getting down to actual code you'll have to work on that more yourself but try looking up:
TransferText Action to export/import data from a table or query to a text file. You can also connect to a text file using DAO (and also probably ADO) in VB. You'll then be able to read the text file as if were a table and move the data into your main database using VB.
You may be able to do all of your required actions using queries. The isses will come associated with any errors that these queries may generate (e.g. dealing suitably records not added due to key violations etc.)
You may want to look at ADO that would allow complete involvement and control as you itterate through the record one by one in code to add/delete etc.

That's about as much help as I have time for at this stage.
Simmsey

 

[JamesCanedo]

Thanks 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. You suggest this may not be the best approach.

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.

- I like the idea concerning the creation of the email message with a special email account at main site. You are right, my experience would not enable me to write something which automates all these tasks. Any pointers to get me on my way would be appreciated. A completely hands off system would be great but happy for someone central to import manually. I presume the SendObject can be used to e-mail a text file? However, I still have the problem in the first bullett above to resolve.

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