CODEWITHSUNDEEP
sqlsql-serverms-accessoledb
Almazini
Almazini

Reputation: 1873

Automatically insert data from Access into Sql server

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

Upvotes: 3

Views: 9659

Answers (3)

Marty
Marty

Reputation: 2016

Since you are dealing with a large number of records, you should consider the effect that indexes might be having on the performance of your data export/import process. A technique that some people use to speed things up in your situation is to first export the source data to a convenient intermediate file format (CSV or whatever), drop the indexes on the destination table, do a BULK INSERT to pull the intermediate file data into your destination table, then finally recreate the indexes on the destination table after the BULK INSERT completes.

This can help performance significantly, because recreating the indexes at the end (one time) is often much faster than the sum of all the index updates that occur when you insert rows one at a time.

I believe you should be able to programatically implement each of the steps above using VBA and T-SQL pass-through queries with Access.

Hope it helps. Good luck with your project.

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91356

If you use something on the lines of the query below, it will run from MS Access. You also have the option of a pass-through query, or better yet, a little code to provide parameters.

INSERT INTO
[ODBC;DRIVER=SQL Server;Server=servername;Database=dbname;Integrated Security=SSPI].Table1
SELECT * FROM Table1

The easiest way to get the correct connection string is to link the table and then check the connect property.

You will also find connections strings at http://connectionstrings.com

Some examples, note that while names are used as a reference, it is really the order of the parameters that matters. For the most part, I check if I have a connection, to save time.

A query in MS Access

Set db = CurrentDb

sSQL = "INSERT INTO dbo_LocationLocationType (LocationID,LocationTypeID) VALUES (@LocationId,@LocationName)"
Set qdf = db.CreateQueryDef("", sSQL)

qdf.Parameters![@LocationId] = LocationID
qdf.Parameters![@LocationName] = LocationName

qdf.ReturnsRecords = False

qdf.Execute dbFailOnError

A stored procedure

 Dim cmd As New ADODB.Command
 Dim prm As ADODB.Parameter

    With cmd
        .ActiveConnection = "insert connection string"
        .CommandType = adCmdStoredProc
        .CommandText = "NameofStoredProcedure"

        Set prm = .CreateParameter("@LocationID", adInteger, adParamInput)
            prm.Value = frm.txtLocationID
            .Parameters.Append prm

        Set prm = .CreateParameter("@LocationName", adVarWChar, adParamInput, 255)
            prm.Value = frm.txtLocationDetailName
            .Parameters.Append prm

        .Execute

    End With

Upvotes: 1

Barry
Barry

Reputation: 31

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.

Upvotes: 2

Related Questions