Increase Ms Access Insert Performance

Question

I am using MS Access 2010, split in front end / back end; on a network drive (WAN) with 16+ table with one table of users (1.3 Million) which is mostly used for user information and is not insert heavy and few other tables, which will receive upto 2000+ inserts daily.

I have been able to optimize most of the read/select queries. Although 1 chunk of my code looks as below. This can be used for upto 2000 iterations daily.

Do Until rec.EOF
    Dim vSomeId As Integer
    vSomeId = rec!SomeId

    'StrSQL = StrSQL & "INSERT INTO TransportationDetails ( TransportationId, SomeId)" & _
        '"VALUES(" & vTransportationId & ", " & vSomeId & ");"

    StrSQL = "INSERT INTO TransportationDetails ( TransportationId, SomeId)" & _
        "VALUES(" & vTransportationId & ", " & vSomeId & ");"

    DoCmd.SetWarnings False
    DoCmd.RunSQL (StrSQL)
    DoCmd.SetWarnings True


    rec.Edit
    rec!SomeBoolean = rec!SomeOtherBoolean 
    rec.Update
    rec.MoveNext
Loop

My objective here, is to reduce the number of calls to the db to insert all the values. and MS ACCESS does NOT support having more than 1 query in a statement, as I tried in the commented part of the code. I also think the recordset upate method is a lot time consuming, and if any one can suggest a better way to update the recordset.

Is there any way I can trick Access to insert & Update in less hits to db through SQL Queries, or any other access feature. Or optimize in anyway, It can take up to 30 mins some time. Decreasing it to At least 2 - 5 mins will be appropriate.

P.S. I can not switch to SQL Server, It is JUST NOT POSSIBLE. I am aware it can be done in way more optimal way through sql server and Access shouldn't be used for WAN, but I don't have that option.

Solution: I went with Andre's and Jorge's solution. The time decreased by 17 times. Although Albert's Answer is correct too as I found my main issue was with the sql statements in a loop. Changing the edits in the recordset to sql didnt impact much on the time factor.

Answer 1

I should point out that in the case of inserting rows, you will find FAR better performance by using a recordset. A SQL “action” query will ONLY perform better if you operating on a set of data. The instant you are inserting rows, then you don’t have a “set” insert, and using a DAO recordset will result in MUCH better performance (a factor of 10 to 100 times better).