OK, I need some advice, as I have been struggling the past year to come up with a decent solution solution.
I joined a retail company last year (my dads small shop), he was struggling for sales, and my plan was to setup a website to sell things, which we have done relatively well... The website has about 25,000 diffrent lines, 2000 customers and has got about 1000 orders from the past year. This data is sitting in a mysql database running www.oscommerce.com as the frontend.
I have come up with a new plan to setup smaller niche websites, using smaller chunks of our inventory. We were thinking of having about 5-10 extra sites each with about 100-200 or so products, each site separated from the others, so they can each be customised. The idea, is to have:
1 offline database access
10 online databases mysql
The offline database will keep everything together and will store all products, companies, customers, invoices, stock counts, and should enable us to do product returns and credit notes and also print statements, invoices, packing slips, and reports ...
We will need to have the ability to create new customers, new invoices, new products... also edit invoices, edit customers, edit products, update order status. I have a cash draw, and receipt printer, barcode readers which I want to connect to access, for the trade counter sales.
Periodically access will connect to the online databases and will download NEW customers and orders, and will upload new products and order modifications etc...
I also want to integrate eBay somewhere so I can upload inventory from access to eBay, I know this is possible but I'm unsure about access?
All eBay checkouts will be handled through the online websites, where they can pay, and add extra stuff to their carts. Access will just download ebay orders the same as the normal website orders.
I'm not an access programmer, so if anyone knows any reason access cannot do the above, or a rough development timescale in hours then please let me know. Any advice will be great, and if anyone knows of some similar databases for download, except Northwind, then that will be ace.
Synching 11 seperate databases is going to be a headache. Designing, building and maintaining such a system could be open ended.
If you happy with MySQL, the security of your web server, and it's connection status (I'm assuming it's not your own server) you could save yourself some grief by pointing other websites to that database and returning filtered results depending on the domain querying the database.
Your own data entry would be done online like every other user through the pages you've created. Secure pages would mean other users couldn't see/enter any information you want to remain secure. You could back up to local to only view data offline.
The only possible advantage of using access would be a quick build user interface - it could also be linked to the online database.
I don't know anything about eBay's setup so it's impossible to say how long that would take.