1 min to insert 1 row of excel to recordset

July 28, 2011 at 02:02:06
Specs: Windows 7
Dear all,

I was a previous VB6user. After upgrading to Window7, I has re-written the program based on Visual Basic 2008 Express Edition. The purpose of the program is insert excel data into Access MDB table. I found the time of running the program is very long in order to read each row of excel and insert into the reordset of ADODB.

Are there any problem of me here. The computer is installed with Office 2010 (Excel 2010, Access 2010)

It takes a minute to insert each row into the recordset.

Looking forward to a solution!

Thanks very much
E Lau

Library referenced as follows
"Microsoft ActiveX Data Objects 2.8 Library"
"Microsoft ADO Ext. 2.8 for DDL and Security"
Microsoft Excel 14.0 Object Library

Start of Code:
'Connect to Existing Cable Database in mdb
ggmdbCon = New ADODB.Connection
s_mdbConStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ggs_FullMdbFile & ";"
'I used this driver "Microsoft.Jet.OLEDB.4.0;" in VB6 but it is unavailable now in VB2008 Express.
ggmdbCon.CursorLocation = ADODB.CursorLocationEnum.adUseClient

Dim mdbRst As ADODB.Recordset
Dim xlsSht As Microsoft.Office.Interop.Excel.Worksheet

'defining field variables to hold the excel columns' values
Dim s_TypedBy As String
Dim s_ByWhom As String

'define Pt as counter to loop down from the excel table rows
Dim Pt as Integer

'Open excel spreadsheet
xlsSht = ggxlsWbk.Worksheets(0)

'Open the Access table
mdbRst = New ADODB.Recordset
mdbRst.Open("NewTable", ggmdbCon, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)

'Retreive row values from excel and add to Access Table
Pt = 1
It Pt > 100 Then Exit Sub
With xlssht
s_TypedBy = .Cells.Values2(Pt, 19)
s_ByWhom = .Cells.Values2(Pt, 20)
End With

'Add New to the Access Table
mdbRst("TypedBy").Value = s_TypedBy
mdbRst("ByWhom").Value = d_TypedDate

Pt = Pt + 1

mdbRst = Nothing
xlsSht = Nothing

End of code

See More: 1 min to insert 1 row of excel to recordset

Report •

July 28, 2011 at 13:59:24
Two suggestions, not mutually exclusive:
1) Try using .NET's OleDB. It might be faster than ADO. It might be faster. (I haven't bothered to test it.)
2) Instead of populating a RecordSet and adding data to it, try just running an INSERT; that should reduce overhead.

How To Ask Questions The Smart Way

Report •
Related Solutions

Ask Question