Import worksheet into Access table - how to speed up

[PaulZak]

(Office 2007)
I import a worksheet into an existing, cleared MS Access table from an Excel front-end file. Below is my code. Is there a way to re-write to speed up this process? A typical file can have 12,000+ rows and take 15+ minutes to import. STEP #2 of code (below) is the culprit.

I have a parent table with all of the “Code” values for a data row and then in a child table I have all of the year values for that row. (The years and number of years may vary by file.)

One initial thought was to write the import into the MS Access file and then call the code from Excel. But I'm trying NOT to have any VBA in the backend so I can just push out to users a new front-end for code changes. So I guess what I’m asking for here is a faster way to do this task with VBA but still from Excel. (What about DAO? Faster?)

Yes, at one point I have some Application.Wait code as a simple way to help ensure that the "parent" record is created before the "child" records go into the 2nd table. Without this, seems the code can get ahead of the db file.

I'm grateful for any ideas from the experts out there. Thanks.

‘=============================================================
Sub ImportExcelSheet()

'Import selected Excel file into db backend.

Dim szSQL As String
Dim rsdata As ADODB.Recordset
Dim i As Integer
Dim iRecKeyID As Long
Dim iRow As Integer
Dim iCol As Integer

'File Stats
Const iSourceFile_FirstRowOfData As Integer = 2
Const iSourceFile_NbrOfMetaDataCols As Integer = 16
Dim iSourceFile_NbrOfDataRows As Integer
Dim iSourceFile_NbrOfCols As Integer
Dim iSourceFile_NbrOfYearCols As Integer
' ------------------------------------------------------------------------------

On Error GoTo Err_ERROR

Set rsdata = New ADODB.Recordset

Workbooks(Dir(gCurSourceFilePathAndName)).Activate


‘=================================
'1) DELETE LAST IMPORTED DATA
'(A) Get last record ID (key) value
'NOTE: I case table is empty, insert a quick temp record so you can get an id.
szSQL = "INSERT INTO tbl_SOURCE_DollarsFile ( PE_CODE ) " & _
"SELECT " & Chr(34) & "Temp" & Chr(34) & " AS PE_CODE;"
rsdata.Open szSQL, gToolDBconnect, adOpenStatic, adLockReadOnly, adCmdText

szSQL = "SELECT Max(ID) AS MaxOfID " & _
"FROM tbl_SOURCE_DollarsFile;"
rsdata.Open szSQL, gToolDBconnect, adOpenStatic, adLockReadOnly, adCmdText
iRecKeyID = rsdata!MaxOfID
rsdata.Close

'(B) Delete all existing records
szSQL = "DELETE * " & _
"FROM tbl_SOURCE_DollarsFile;"
rsdata.Open szSQL, gToolDBconnect, adOpenStatic, adLockReadOnly, adCmdText

‘=================================
'2) READ DATA FROM SOURCE FILE INTO DB FILE

'(A) Get file "stats"

'Count nbr of columns
Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
iSourceFile_NbrOfCols = Selection.Columns.Count


'Count nbr of data rows
Cells(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
iSourceFile_NbrOfDataRows = Selection.Rows.Count - 1

iSourceFile_NbrOfYearCols = iSourceFile_NbrOfCols - iSourceFile_NbrOfMetaDataCols


'(B) Read data into table (Loop through each row of source data file)

For iRow = iSourceFile_FirstRowOfData To iSourceFile_NbrOfDataRows + (iSourceFile_FirstRowOfData - 1)

'Import the main record data
szSQL = "INSERT INTO tbl_SOURCE_DollarsFile ( DETAILS_CODE, DETAILS_DESCRIPTION, " & _
"CODE1, CODE1_DESCRIPTION, CODE2, CODE2_DESCRIPTION, " & _
" CODE3, CODE3_DESCRIPTION, CODE4, CODE4_DESCRIPTION, CODE5, CODE5_DESCRIPTION, " & _
" CODE5, CODE5_DESCRIPTION, CODE6, CODE6_DESCRIPTION ) " & _
"SELECT " & Chr(34) & Cells(iRow, 1) & Chr(34) & " AS CODE1, " & _
Chr(34) & Cells(iRow, 2) & Chr(34) & " AS CODE1_DESCRIPTION, " & _
Chr(34) & Cells(iRow, 3) & Chr(34) & " AS CODE2, " & _
Chr(34) & Cells(iRow, 4) & Chr(34) & " AS CODE2_DESCRIPTION, " & _
Chr(34) & Cells(iRow, 5) & Chr(34) & " AS CODE3, " & _
Chr(34) & Cells(iRow, 6) & Chr(34) & " AS CODE3_DESCRIPTION, " & _
Chr(34) & Cells(iRow, 7) & Chr(34) & " AS CODE4, " & _
Chr(34) & Cells(iRow, 8) & Chr(34) & " AS CODE4_DESCRIPTION, " & _
Chr(34) & Cells(iRow, 9) & Chr(34) & " AS CODE5, " & _
Chr(34) & Cells(iRow, 10) & Chr(34) & " AS CODE5_DESCRIPTION, " & _
Chr(34) & Cells(iRow, 11) & Chr(34) & " AS CODE6, " & _
Chr(34) & Cells(iRow, 12) & Chr(34) & " AS CODE6_DESCRIPTION; "
rsdata.Open szSQL, gToolDBconnect, adOpenStatic, adLockReadOnly, adCmdText

Application.Wait (Now + TimeValue("0:00:01") * 1)

'Import the yearly values
iRecKeyID = iRecKeyID + 1
For iCol = (iSourceFile_NbrOfMetaDataCols + 1) To iSourceFile_NbrOfCols
'Only import value if it's NOT zero
If Cells(iSourceFile_FirstRowOfData + i, iCol) <> 0 Then
szSQL = "INSERT INTO tbl_SOURCE_DollarFile_ByYear ( ID_MainTbl, YearOfDollars, NbrOfDollars ) " & _
"SELECT " & iRecKeyID & " AS ID_MainTbl, " & _
Right(Cells(1, iCol), 4) & " AS YearOfDollars, " & _
Cells(iRow, iCol) & " AS NbrOfDollars;"
rsdata.Open szSQL, gToolDBconnect, adOpenStatic, adLockReadOnly, adCmdText
End If
Next iCol

Next iRow

‘=================================
'3) WRAP-UP IMPORT

gImportWasSuccessful = True

'Close source file
ActiveWorkbook.Close (False)



Exit Sub

'=============================================
'=============================================

Err_Exit:
Exit Sub


Err_ERROR:
'Display standard message.
MsgBox "(MODULE) Imports.IMPORT_Dollars_Data" & vbCr & vbCr & _
"(Error #) " & Err.Number & vbCr & vbCr & _
"(Description) " & Err.Description
Resume Err_Exit

 

[gaj104]

One initial thought was to write the import into the MS Access file and then call the code from Excel. But I'm trying NOT to have any VBA in the backend so I can just push out to users a new front-end for code changes

I don't quite get your rationale here. If users have a front end (presumably multiple front ends) and 1 backend then why is there an issue with coding in the access backend? No user will be looking at this database.

Personally, I'd do this all in access, and rather than doing line by line, do a simple transferspreadsheet command. If you really need to do it in excel, then make a reference to access and control it from there.

Dim msA As Access.Application

Set msA = New Access.Application

msA.OpenAccessProject "F:\testdb.mdb"

msA.DoCmd.TransferSpreadsheet acImport 'etc etc ....

msA.Quit

 

[PaulZak]

Sorry for the confusion. Maybe this will clarify things:

Each user has their own backend db file that also contains their individual data calculation tables. They all use a copy of the standard front-end Excel file that I update & distribute as required functionality changes. So I don't want any code that would need to be updated in the back-end because then each person's db file would require updating. I think this is standard front-end/back-end design: Only data in the backend, all code in the front-end. And, the company's standard is for front-ends to be in Excel. I've done a few apps here in all Access and folks were too "afraid" of using Access so we have to make it invisible to them. (Yes, I had screen/forms, etc. but opening Excel feels better to these analysts.)

My note tried to explain that in the past when I've imported an Excel file into an Access db using VBA in the Access file, it was very quick. So I pondered writing the import code in the backend and running it from Excel front-end. But then, of course, if the import code needed to be tweaked, I'd have the problem I seek to avoid: the need to update VBA in each person's db file.

My thought is that maybe loading the Excel sheet's data into an array and then loading array into Access table might be quicker? However, I'm wondering whether the bottom line is that it will always take time to write the data into Access by individual row or cell. Hopefully someone will have some experience with this...