Writing Large Amounts of Records to Access using VB

Question

I am currently writing some software in visual studio to analyse large amounts of data from an Access database using SQL. I have code to make a new calculated variable but am struggling with the amount of time it takes to write the data back into Access.

I am currently using some vb com code to communicate with my Access Database which is running in 2002/3 comparability mode. The following is my current code which runs a function in a loop to write to the database.

cnnOLEDB = New OleDbConnection
    cnnOLEDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataDirectoryName & DatabaseFileName
    cnnOLEDB.Open()

    'cmdOLEDB = New OleDbCommand
    cmdOLEDB.Connection = cnnOLEDB

    ColumnString = "ID_VAR, ID_PAR, TimeValue, strValue, ID_UPL"
    For RecordCounter = 0 To CalcData.GetLength(1) - 1
        Var_ID = Var_ID + 1
        ValueString = Format(Var_ID, "0") & ", " & Format(Parameter, "0") & ", #" & Date2String(CDate(CalcData(0, RecordCounter))) & "#, " & CalcData(CalcData.GetLength(0) - 1, RecordCounter) & ", " & Format(AsUpload, "0")
        If DatabaseConnectionInsert("INSERT INTO " & TableName & " (" & ColumnString & ") VALUES (" & ValueString & ")", "Non-Query") = "Error" Then GoTo Close
    Next

    cnnOLEDB.Close()

Here is the Function:

Public Function DatabaseConnectioninsert(ByVal Query As String, ByVal Task As String) As String
        'On Error GoTo Err

        'If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close()
        cmdOLEDB.CommandText = Query

        Select Case Task
            Case "Read Recordset"
                rdrOLEDB = cmdOLEDB.ExecuteReader()
                DatabaseConnectioninsert = "Read Recordset"
            Case "Read Scalar"
                DatabaseConnectioninsert = cmdOLEDB.ExecuteScalar
            Case "Non-Query"
                cmdOLEDB.ExecuteNonQuery()
                DatabaseConnectioninsert = "Non-Query"
        End Select

        Exit Function
Err:
        MsgBox("Database connection error.")
        DatabaseConnectioninsert = "Error"


    End Function

I am currently trying to insert ~4500 records into the Access Database for each Parameter which takes ~3minutes. However when the project goes live it will have to deal with over 100000 records per Parameter so it is no where near fast enough.

To solve this issue I am thinking of either updating my code to .net or creating a record set, so I can move all of the data in Access at once. Can anyone give me some advice as to which will have the greatest impact to improving the speed of the inserts. I am running visual studio 2005 and Access 2007, updating the database to 2007 rather than compatibility mode is possible but not ideal , however my current code can't access it.

Thank you for your help

Josh

Are you sure that using Access for a database application that would have to handle hundreds of thousands of records in a reasonable time manner is a sound technical decision? — Olaf, Aug 20 '12 at 15:33
Sounded to me like the Access db was something inherited in the analysis effort :) — David W, Aug 20 '12 at 15:36
@Pezzzz, my detail exposure to Access is rather limited, but I would at a minimum encourage you to take a look at whether you could do batched inserts (eg avoid commits with each insert), or perhaps, as a completely alternative approach, generate your new data as a text file, and just import the text file separately. I think when you start to hit hundreds of thousands of records, Access becomes a less and less desirable DB solution.. — David W, Aug 20 '12 at 15:45
Not just Access, this is RBAR and kills just about any database. — Fionnuala, Aug 20 '12 at 15:47
SQL Server OR SQL Server Compact Edition may be good choice. The later, that i know, is waaayy faster than Access. — GameAlchemist, Aug 20 '12 at 15:50
@Vincent Piel That is simply not true for a row-by-row insert. MS Access (Jet/ACE) may be faster in such a situation, because it at least may be on the same PC. — Fionnuala, Aug 20 '12 at 15:57
@Remou : SQL Server Compact Edition is on the same PC, it is a file-based DataBase, quite comparable with Access, but faster, and, in the 3.5 version, able to handle Entities. In a project i switched from Ado/Access to SQL CE and could see a tremendous speed-up. — GameAlchemist, Aug 20 '12 at 18:12
@VincentPiel So much faster that adding one row at a time amounting to thousands on records will be viable? — Fionnuala, Aug 20 '12 at 18:15
@all Thanks for the replies, unfortunately Access is the only database tool available to me. — Pezzzz, Aug 21 '12 at 07:38

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

~~As ridiculous as it sounds,~~ the very best performance you will get on an Access database is using the ~~ancient~~ DAO COM library. Use a RecordSet object to add the records one at a time in a loop and reference the fields by their index (ordinal position) rather than their names. You will find it much, much quicker than using oleDB.ExecuteNonQuery.

See the solution given here for more information. It's C# but it's easy enough to follow and convert to VB.NET if you want to try it out.

Edit
In deference to Remou's comments below: it would appear that Microsoft have in fact been keeping DAO technology up to date – in spite of declaring it obsolete back in 2002 – but you have to use the Office Access Redistributable rather than the better known DAO 3.6 library.

edited May 23 '17 at 12:20

Community

1
1

answered Aug 20 '12 at 16:46

Antagony

1,750
12
17

DAO is not ancient, it has been in development and a new version was released with 2010. However, the problem is the RBAR approach, which is just not going to be fast. – Fionnuala Aug 20 '12 at 18:16
@Remou Are you sure you're not thinking of ADO? AFAIK, the last version of DAO was 3.6 – which I believe was released with Office 2000 and in SP6 for VS6. There may have been minor security fixes on that DLL since then, but no major updates and MS [declared it obsolete](http://msdn.microsoft.com/en-us/library/ms810810.aspx#bkmk_ObsoleteDataAccessTech) as long ago as 2002. – Antagony Aug 20 '12 at 19:22
1

The latest is `Microsoft Office 12.0 Access Database Engine Object Library` – Fionnuala Aug 20 '12 at 19:23
As for it being RBAR: you're right, of course, but the timing graph on the solution I linked to show that significant performance gains can be made by using DAO over other methods. It was 30 times quicker than using an OLEDB table adaptor. – Antagony Aug 20 '12 at 19:25
It can be freely downloaded http://www.microsoft.com/en-us/download/details.aspx?id=13255 and it shows up in my com list. It is the updated version of DAO distributed since 2007. Jet is deprecated but still used, ACE is not, nor is DAO. – Fionnuala Aug 20 '12 at 20:18
@ Anatagony thanks for the reply. DAO looks like it will be the perfect solution. – Pezzzz Aug 21 '12 at 07:44
Can either of you offer me a link to some sample code for DAO using the 12.0 object library, as I only seem to be able to find code for the 3.6 DAO library. I am currently trying to update my code for Windows 7 and my Database access is causing problems. Many thanks – Pezzzz Oct 18 '12 at 11:14

Writing Large Amounts of Records to Access using VB

1 Answers1

Linked