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
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
s_TypedBy = .Cells.Values2(Pt, 19)
s_ByWhom = .Cells.Values2(Pt, 20)
'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