Updating Access with Excel Data - Help!

[furt0414]

Hello Mr. Excel Community!

I wrote a macro for consolidating my company's budget data into Access Databases to make things easier on our IT department. The code works, however it's extremely slow and I know there is a much faster way to do this.

Right now my code loops through 100+ csv templates and transfers the data 1 cell at a time. It also checks the size of the selected database before starting a new customer, and changes to a new database if nearing the limits. Clearly I couldn't have picked a less efficient method, but this was the only approach I was able to successfully implement. I did some research and think my solution will be changing to SQL syntax and using INSERT, but I have no idea how to go about changing my code to allow for this.

Any help or suggestions would be greatly appreciated!!

Here's what I currently use:

Dim oSelect As Range, i As Long, j As Integer, sPath As StringDim aitFR, aitLR, dbNum As Long
ait.Activate
aitFR = 1
aitLR = ActiveSheet.Range("A1000000").End(xlUp).Row
Set oSelect = Range(Cells(aitFR, 1), Cells(aitLR, 7))


Dim oDAO As DAO.DBEngine, oDB As DAO.Database, oRS As DAO.Recordset, oApp As Access.Application
ChDir ActiveWorkbook.Path
dbNum = 1
sPath = "\\ocsshared\analytics\2016 Budgets\_FINAL_ACCESS_DBs\BudgetTable1.accdb"


''Check file size to see if new DB should be used.  If exceeding 1.5GB then switch to next version.'
If FileLen(sPath) > 1610612736 Then
'If FileLen(sPath) > 102400000 Then
    dbNum = 2
    sPath = "\\ocsshared\analytics\2016 Budgets\_FINAL_ACCESS_DBs\BudgetTable2.accdb"
    If FileLen(sPath) > 1610612736 Then
        'If FileLen(sPath) > 102400000 Then
        dbNum = 3
        sPath = "\\ocsshared\analytics\2016 Budgets\_FINAL_ACCESS_DBs\BudgetTable3.accdb"
        If FileLen(sPath) > 1610612736 Then
            'If FileLen(sPath) > 102400000 Then
            dbNum = 4
            sPath = "\\ocsshared\analytics\2016 Budgets\_FINAL_ACCESS_DBs\BudgetTable4.accdb"
            If FileLen(sPath) > 1610612736 Then
                'If FileLen(sPath) > 102400000 Then
                dbNum = 5
                sPath = "\\ocsshared\analytics\2016 Budgets\_FINAL_ACCESS_DBs\BudgetTable5.accdb"
            End If
        End If
    End If
End If


Set oDAO = New DAO.DBEngine
Set oDB = oDAO.OpenDatabase(sPath)
Set oRS = oDB.OpenRecordset("BudgetTable" & dbNum)


For i = 2 To oSelect.Rows.Count 'skip label row
    oRS.AddNew
    For j = 1 To oSelect.Columns.Count
        oRS.Fields(j) = oSelect.Cells(i, j)
    Next j
    oRS.Update
Next i


oDB.Close


DoEvents

 

[furt0414]

Bump! Need assistance ASAP!

 

[Sektor]

Whatta awkward solution - using database with 2 GB file size limit knowing there will be lots of data! I guess it's time to use free SQL Server Express Edition with 10 GB limits per database file.
Try the batch update (the update is outside of loop):

For i = 2 To oSelect.Rows.Count 'skip label row
    oRS.AddNew
    For j = 1 To oSelect.Columns.Count
        oRS.Fields(j) = oSelect.Cells(i, j)
    Next j
Next i
[B][COLOR=#ff0000]oRS.Update UpdateTypeEnum.dbUpdateBatch[/COLOR][/B]

 

[furt0414]

Thank you so much for the reply, and I agree this approach is ridiculous! I was given an extremely short time frame to create a process for rolling up 100+ individual templates into a format we could quickly review and pass onto IT for upload into our master systems. Having no prior experience with databases, I'm just happy it actually worked as intended!

Can you explain how your modification works? I understand the batch update will write all pending changes in the cache, but does my loop work to capture everything in the cache correctly?

P.S. Sleep easy knowing that we're investing in real budgeting software this year. No more clunky home brewed band-aids!

Whatta awkward solution - using database with 2 GB file size limit knowing there will be lots of data! I guess it's time to use free SQL Server Express Edition with 10 GB limits per database file.
Try the batch update (the update is outside of loop):

For i = 2 To oSelect.Rows.Count 'skip label row
    oRS.AddNew
    For j = 1 To oSelect.Columns.Count
        oRS.Fields(j) = oSelect.Cells(i, j)
    Next j
Next i
[B][COLOR=#ff0000]oRS.Update UpdateTypeEnum.dbUpdateBatch[/COLOR][/B]

 

[Sektor]

Everything is OK with data. The data just writes to temporal buffer which is then passed as one batch. This is MUCH efficiently, because it doesn't defragment your database file. Inserting row-by-row will impact the database file, in which case you have to compress it.
So, does my code work OK?