Advice on importing Access data into MSSQL table using code

Hi,

I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.

I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?

The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.

If anyones done anything like this before, I'd love to hear their views......!

Thanks!

What are you going to use for a scheduler? You will still need to user MSSQL jobs or else you will need an external scheduler to perform this for you. What is wrong with using the DTS?

Ha - funny you should say that, after writing this post, I went and had a look at what Enterprise Manager had to offer and the DTS looks great so I'll be using that!

My only problem is that I have no control over the database I'm importing the data on and it has no "new data" flag so at the mo I have to copy the entire table across and then remove duplicates. The database is Access 97 and is populated by some third party software.

As far as I could find there was no way of comparing two tables from completely seperate databases before I transfer the data across.

What I may do is import each table into a temporary table from Access to MSSQL and then write a query that compares the data between the live table and the temp table and copies the data across accordingly. Do you think this a good way of going about it?

Take a look at the subscription replication for the DTS. Check to see if it can handle heterogenous database replication.

Cheers - it looks like it can. I'll give it a try and post my findings.

Actually, the subscription replication doesn't support subscribing from a heterogenous db, only publishing to. You have to write your own program in VB or C++ in order to do this, using the Microsoft SQL snapshot Control dll (sqlinitx.dll).

Looks unneccesarily complicated!

Is the MS Access database read only? Will updates come from both MS SQL and users? Will the data in MS Access need to be published back to MS SQL?

The database is read only and updates will only come from the third party software, nowhere else. The data will never need to be published back to Access (the data is being replicated from Access to MSSQL). So it would literally be a straightforward merge from the Access DB to the MSSQL db.

I need it in MSSQL because its alot faster, and as the Access Db is version 97 I'm not able to use a whole series of SQL queries that I need to on the data stored therein... :-)

I've spent all day practically trying to figure out a way of replicating from Access to MSSQL and I haven't managed it. I've managed everything else - merging from MSSQL to Access, from MSSQL - MSSQL, but not from Access to MSSQL (I guess it would need to use ODBC)

If you are going from MS Access to MS SQL you could use an ODBC link tool. I believe this is avaible in the version you are using. Create the table in MS SQL and create a linked table in MS Access.
...
If the project will be displayed in MS SQL why is the data not directly entered into MS SQL? Just a question. You may run into file contention issues and slowness when using MS Access as your data entry point if your number of concurrent users grows.

I've got the same type of issue. My 3rd party application writes to an Access 97 database that is not editable, hence I cannot add any pass-through queries on it to help with the data transformation.