Automatically insert data from Access into Sql server

Question

Situation: We receive statistical source data on monthly basis. It comes as a CD with an Access database which contains two tables. So we have 1 *.mdb file with 2 tables inside. Analysts should be able to import data, calculate and standardize it and check results. If results are fine data is populated. Analysts are not programmers and they don't have SQLServerManagementStudio.

My task: To create an access form as front-end for analysts with buttons: import, calculate, check, populate. Every button will fire the path trough query which run't stored procedure on sql server's side.

My problem: The bottleneck of my project is data import. It should be done automatically(NOT manually as analysts don't have management studio installed).

What I tried:

1) I linked SQL server's tables into access and tried to use regular queries to copy(INSERT INTO ** FROM *) but It takes too long. ( I have about 4 million rows in two tables)

2) As I fond out BULK INSERT is not working with importing from access file.

3) OPENROWSET approach:

SELECT *
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0','\\euro-dc\DOTS\2014\dots.mdb';'admin';'',TimeSeries)

Which returns me the error:

Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Openrowset solution is not preferable. I would like to avoid it. Because it will will work on some computers and won't on other ones because of OLDB driver. After windows re-installation will need to check OLEDB driver ... it is not very comfortable as there are 15 analysts. Only if there is no other way to import data.

\euro-dc\dots folder is accessible for sql server - it is tested in my other project.

Will appreciate any fresh ideas. thanks

Answer 1

Use the import wizard and save the import specifications for each group of data you want to import. Then write some code/macro under a button that executes the import specifications. The tables you import into can be built in Access, then upsized to SQL Svr.

4 million recs is going to be slow, so you may want to put it in a separate MDB so that it doesn't tie up your analyst's working MDB.