Howard
If it were me Id set it up as similar to a sales logic ERP program that a
former company of mine used, but with more draconian rules that you are
willing to use. It would look something like:
Main database backend on the server has everything.
Sales rep database backend has ONLY all of the sales reps customers and
projects data
All of the front end databases are the same build.
Sales rep is getting ready to take a swing through his territory. His front
end database is connected to the Main backend database on the server and he
pushes a "I'm am out of the office" button to download all of his customer
information from all of the tables to a local database on his laptop. In
the process of doing this all of his customer records gets a CustCheckedOut
field set that allows the management to see all of his data, BUT not change
anything.
Meanwhile our sales rep goes out into his territory making what ever changes
are necessary, and Even adding new orders and customers. (You will have to
flag new orders and customers as such for later import back into main).
Sales rep comes back with a boat load of sales and new accounts. He
attaches his database front end to the Main Database on your server and
pushes "Hello I'm Back" Button. This button updates all of the existing
records in the Main database and insert's new records from his laptop to the
main database. All of his records CustCheckedOut records in the Main
database are set to false, and management can now make changes as necessary.
The database on his laptop gets all of it records deleted. All the while
our star sales rep is in the office he uses the Main backend.
Whew that's a lot of work you say, and I agree, but look at the advantages.
Management has all the data from all of the customers in one place for
forecasting, reporting, etc. (How are you going to tell the sales manager
that it is gonna take two days to to find your top 50 customers because you
have to gather data from 350 seperate databases? I have never seen a sales
manager type who'd take kindly to that.)
There is no risk of accidentally deleting the wrong database (it is only
a matter of time till you get bit by that)
The whole process is automated and the Sales Rep always has access to
ALL of his accounts
The sales rep can not leave the company and take all of the accounts, as
he has access to only his.
If it were me I'd never delete a potential customer from a database. If
you leave him there you can at least market to him at a later date with a
letter or Email. It's a cheap way to beat the bushes.
Ron W
Howard said:
Hi Ron,
I appologize, I haven't been clear. Currently my database is all in one
application. There exists a customer file in which customers are all
referenced by ID which are in turn referenced from the item and labour
tables. However, having a separate database for each customer would satisfy
my client's criteria to take the database on the road with several mobile
sales reps. If each sales rep creates a new client and adds all the item and
labour records for that customer, synchronzing data back at the server will
be a nightmare. A separate database for each customer will be ideal, but with
central product, supplier, resource and other lookup files. Users can then
just pass a customers database back and forth for updating without any
duplication or synchronization problems. I've seen huge tax and accounting
programs developed this way so client records can be shared amongst teams of
mobile accountants. Same as a doctors office with a file for each patient.
I've been a systems/database analyst for 25 years with over 10 semesters of
system and relational / hierarchial database design courses. However, my
experience has been mostly with proprietory databases and some Microsoft SQL.
I'm still new to Access/VB programming. Please let me know if there is an
efficient way to keep each customers data as a separate mdb file. Thanks!
