Automatically insert data from Access into Sql server

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.

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.

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