Import multiple MS Access DB's (Data sub-set) to MS SQL 2000

[SimonBurrell]

Hi,

I have a requirement to import data from a totally De-normalised MS Access db at 10 remote sites into a normalised central MS SQL server. (From 1 MS Access table to multiple MS SQL tables)

The Access db's all have the same schema and have a VB front end.

I would like to be able to get the users to run some sort of query to export the required data, without having to modify the Access db (i.e add a query to the DB. This is because the DB is Access 97, and all sites have Access 2000 on their local PC's).

After exporting the data, I expect the users to send (email/FTP??) the result file to me and I will use DTS to import into MS SQL 2000.

Does anyone have any solutions? I CAN'T connect to these sites via ODBC so I don't believe DTS will work. (???)

Thanks in Advance.

Regards

Simon

 

[SQLSister]

In Access 200, they have a menu, File Export. Have them use this and choose text file as the type of file to export. They will have to do this for each table in the database.

Suggest you have one site do it as a sample and send you the data. Then you can develop and test the DTS procedures. Then have everyone send you their data after you know they can be imported. Since you are going from denormalized to normalized, I expect your DTS package will get complicated. I also expect that this kind of database doesn't have the data entry constraints it should have and that the data may need to be massaged before the import will work. For instance, if they didn't use a real date field for any dates you may have bad dates (Feb 30, 2002) or even such nonsense as ASAP in the field which won't translate into your date field in your normalized database.

Of course, once they send you their data, they can't update it until you give them their SQL database back or the records they add or change will be missing from your data. And you will still have the deletes.

 

[SimonBurrell]

Thanks SQLSister.

Still very messy isn't it. I was hoping that the 'non-technical' users would not have to go into the MS Access db specifically.

Simon