Using import/export to share data

[shaunacol]

I know there are a lot of posts on this issue but mine is very specific. I have 10 different sites now with staff using standalone databases. I have read up on web enabling and sharepoint but have used sharepoint before and it was a nightmare. The thing is, there are only 3 tables that these sites have access to update and they are tbl_customer, tbl_orders and tbl_orderddetail. I have been trying for a couple of days to think of the best way to get info from these 10 sites and load into a master copy. They may only do a few new records and receiving updates once a day is enough to make sure the manager knows how much money has been taken etc. So here is what I am thinking and I would appreciate any feedback:

1. I have set the PK of these tables to be unique to the location (to make it easy to bring database back together)
2. I plan to create an automated process to attach the 3 tables in excel form when database is closed (I can do one at moment with sendobject and am just looking for some code to attach all 3)
3. Once email received I guess the manager will have to download to his local area
4. In a master copy I create an update query he can run to collect the 3 spreadsheets and append records to the appropriate table.



Obviously this will be a faff for 10 different sites but I cant think of another way to implement this quickly (manager wants it implemented in1 or 2 days). There used to be a feature where excel spreadsheets could be liked to but I don't see this in MS Access 2010. Are there other features that might help with this sharing issue?

Your thoughts are appreciated. Thank you.

[CJ_London]

If you need a quick solution then having your satellite systems generate an excel workbook or .csv file is the obvious answer. If you have made your PK totally unique (one option is to make the PK autonumber a replication id rather than long) then this simplifies the import process. Personally I prefer .csv files (rules on data presentation are stronger and when looking at the file you know exactly what you are getting and you don't have the row limit in Excel) but personal choice.

It sounds like you are simply exporting the entire tables so your import will need multiple queries - one for new records, one for changed records and perhaps one for deleted records (depending on how you system is intended to work).

There used to be a feature where excel spreadsheets could be liked

can't work out what this means

[shaunacol]

Thanks, I hadn't thought of CSV files, that could work well. I also hadn't thought of records that had changed which I will have to deal with. Thanks so much for the input.