编写大量记录以使用VB进行访问 [英] Writing Large Amounts of Records to Access using VB

问题描述

我目前正在Visual Studio中编写一些软件，以使用SQL分析来自Access数据库的大量数据.我有编写一个新的计算变量的代码，但在将数据写回到Access所需的时间上却很费力.

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.

我当前正在使用一些vb com代码与以2002/3可比性模式运行的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()

这是函数:

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

我目前正在尝试为每个参数插入〜4500条记录到Access数据库中，这大约需要3分钟.但是，当该项目上线时，每个参数必须处理超过100000条记录，因此它的速度还不够快.

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.

要解决此问题，我正在考虑将代码更新为.net或创建一个记录集，以便可以一次将所有数据移动到Access中.任何人都可以给我一些建议，这些建议将对提高嵌件的速度产生最大的影响.我正在运行Visual Studio 2005和Access 2007，可以将数据库更新为2007，而不是兼容模式，但这不是很理想，但是我当前的代码无法访问它.

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.

谢谢您的帮助

乔什

推荐答案

听起来很荒谬，在Access数据库上获得的最佳性能是使用 ancient DAO COM库.使用RecordSet对象可一次在一个循环中添加一条记录，并通过它们的索引(常规位置)而不是它们的名称来引用这些字段.您会发现它比使用oleDB.ExecuteNonQuery快得多，很多.

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.

请参见此处给出的解决方案了解更多信息.它是C#，但是如果您想尝试一下，可以很容易地跟随并转换为VB.NET.

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.

修改
为了尊重Remou在下面的评论:尽管可重新分发Office Access ，而不是众所周知的DAO 3.6库.

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.

这篇关于编写大量记录以使用VB进行访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！