LARGE file when Creating/Updating Access DB through Excel VBA

[cazeek]

Hello,



I am by no means an expert / teach myself as I go, so please keep that in mind. I have created code to either create a new or update an existing access DB using ADODB through VBA for Excel. Everything works perfectly, but the resulting database files are very large (26MB). They can be brought back down to normal size (1MB) using the Compact/Repair feature within Access, but that's very inefficient/time-consuming. Can anyone take a look / offer some suggestions? Relevant code is posted below. I'm also open to any other suggestions on the code in general if something looks inefficient.

Thanks!!

Sub Update_DB_OnTime()


If Range("error_check") = True Then
    DoEvents
    Application.OnTime Now() + TimeValue("00:00:01"), "Update_DB_OnTime"
    Exit Sub
Else
    Dim dbConn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dbLoc As String
    Dim connect As String, strSQL As String
    Dim dbName As String
    
    dbName = Range("contract_code") & ".accdb"
    
    dbLoc = "C:\...Folder Location...\" & dbName
    
    Set dbConn = New ADODB.Connection
    connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
    connect = connect & "Data Source="
    connect = connect & dbLoc
    
    dbConn.Open connect


    strSQL = "SELECT * FROM Table1"
    
    Set rs = New ADODB.Recordset
    rs.Open strSQL, dbConn, adOpenKeyset, adLockOptimistic
    
    Update_DB rs
    
    rs.Close
    Set rs = Nothing
    dbConn.Close
    Set dbConn = Nothing
End If


Sub Update_DB(rs As ADODB.Recordset)


Dim start_record As Integer


If Cells(Range("record_count") + 2, 18) = Cells(Range("record_count_old") + 2, 3) Then
    start_record = Range("record_count_old")
    rs.Move (start_record)
Else
    start_record = 0
    If rs.BOF <> rs.EOF Then
        rs.MoveFirst
    End If
End If




With rs
    For i = start_record To Range("record_count") - 1
        If Cells(i + 3, 16) <> "" Then
            If .EOF Then
                .AddNew
                .MoveLast
            End If
            For j = 1 To rs.Fields.Count - 1
                If IsError(Cells(i + 3, j + 16)) Then
                    With Worksheets("Error Log")
                        .Calculate
                        .Range("C3").Offset(Range("error_count"), 0) = Range("contract_code")
                        .Range("D3").Offset(Range("error_count"), 0) = Cells(i + 3, 1 + 16)
                        .Range("E3").Offset(Range("error_count"), 0) = Cells(2, j + 16)
                    End With
                Else
                    .Fields(j).Value = Cells(i + 3, j + 16)
                    .Update
                End If
            Next
            .MoveNext
        End If
    Next
End With


End Sub

[Peter M]

I'm guessing the Excel file is also fairly small? I suspect, but do not know, that when "Select * from Table1" is called Access allocates a large amount of space to handle the unknown incoming file.
Instead of selecting the data from the excel file, can you link to it? (DoCmd.TransferSpreadsheet)

Excel files can be imported or linked use the following:
DoCmd.TransferSpreadsheet [TransferType], [SpreadsheetType], [TableName], [FileName], [HasFieldNames], [Range]

• Transfer Type: acImport, acExport, or acLink
• Spreadsheet Type: the version of Excel. Using acSpreadsheetTypeExcel12 seems to work for xlsx files.
• Table Name: the MS Access table that the data is going into
• Filename: where the data is coming from
• HasFieldNames: TRUE if the first row contains field names for each column
• Range: usually the sheetname e.g. “Sheet1!” with the exclamation (bang) at the end, can also use Named Ranges

[CJ_London]

the method used (record by record, field by field) is pretty much the slowest way of doing it.

Peters suggestion of using transferspreadsheet is a good one, but based on running in Access and importing from Excel. You can create an Access object in your excel VBA and perhaps can run it that way - or look at the transferdatabase method. Another alternative perhaps is to create a data connection and run a dao append query.

In summary, will need to know more before a better way can be suggested - some example before and after data and why you are doing this - appending new records to a log table? why not keep in excel?