Schema - Main table stores Customer Address Information, etc. Linked secondary tables store Notes regarding contacts with customer such as actions taken on customer’s behalf, conversations with customers, etc. Tables are linked on an auto-enter Customer ID number.
Need – To be able to send record(s) via internet to an agent. Have agent edit/ update certain fields in record(s) and/or add records for new contact information then return records to home office for re-import into master program. Agent can also add records regarding new customers or records regarding new contacts with existing customers. New Customer records would need to update their Customer ID field on re-import. Contacts records would have to have their ID number assigned before re-entry in order to link properly.
Prefer that agent be able to see a form view as opposed to a spreadsheet view. Need to export all information from main table and linked tables. Could provide agent with our program if necessary but is there a way to send just an executable Access-based program with a minimal form that agent could use to edit or add records? Even open to the idea of an Excel spreadsheet if it can auto open with a form view but I can’t get form view to show the linked records properly. It puts all fields from both tables onto one main form.
Prefer that process limit the amount of damage an agent can do to the integrity of my data if possible. This will probably be a once-monthly process for having agents update our sales records.
I would sincerely appreciate if anyone can tell me if and how this is possible and give me sample code if possible. I’m weary of gnashing my teeth over this one!
It's been awhile since I looked at it, and I have never really used it, but I think what might help is for you to research database replication. I might be wrong, and hopefully someone will correct me if I am, but you should be able to create an application and give a copy to each agent. The agents would then add and edit information, then when they are in the office they would connect to the master database and update and add and also get new records that other agents have added. This is all done automatically by Microsoft. What you want to do sounds very complex especially when more than one agent might be changing data for the same contact and they might update at different times. But in theory a replicated database should take care of all those details for you.
Thanks for your response.
Replication will work as far as giving them copy of program. My fears are, as you said, more than one agent or the office personel updating the same record and some changes being lost. This is probably a remote possibility but possible nonetheless. Also, the ability to import records would require the query to look to a particular directory for a table. Since I would not be able to travel to the agents' locations (60 agents worldwide) to install their program nor would I be available to save an email attachment to the right location on their computers each time an exchange transpires, I've concerns with this as a viable option.
I'm thinking of free-standing executable files going out to agent but I'm open to all options and opinions. Click on attachment, opens, makes changes, additions, close and return to me. I then import back to master program in office. I've advised my employers of the risk of overwritten data and they say they're willing to risk it in order to have the exchange capability. Spreadsheet seems easiest but it needs a form interface!