CODEWITHSUNDEEP
excelvbadao
HackSlash
HackSlash

Reputation: 5813

Open Excel table as DAO RecordSet for Appending

I'm trying to update a table in an Excel file from DAO as if it were a Database table. Sounds like something that should be possible but I can't find any documentation about opening Excel with DAO.

I got as far as opening the file and getting a recordset handle but I'm seeing error 3027 "Cannot update. Database or object is read only." when I try to add to the recordset. One part of the problem is that I can see the sheet as a tabledef but I can't find the excel table in that sheet as an object. Maybe I don't know the proper syntax to open the table as a recordset.

The code I am using specifies dbOpenDynaset, like you would to make an Access table writable. Is what I am trying even possible?

The error occurs on ".AddNew":

Const dbOpenDynaset As Long = 2
Const dbAppendOnly As Long = 8
Const dbOptimistic As Long = 3

Public Sub OpenExcelAsDB(ByVal excelFile As String) 
    Dim fileExtension As String
    fileExtension = Right$(excelFile, Len(excelFile) - InStrRev(excelFile, "."))

    Dim connectionString As String
    Select Case fileExtension
    Case "xls"
        connectionString = "Excel 8.0;HDR=YES;IMEX=1"
    Case "xlsx"
        connectionString = "Excel 12.0 Xml;HDR=YES;IMEX=1"
    Case "xlsb"
        connectionString = "Excel 12.0;HDR=YES;IMEX=1"
    Case "xlsm"
        connectionString = "Excel 12.0 Macro;HDR=YES;IMEX=1"
    Case Else
        connectionString = "Excel 8.0;HDR=Yes;IMEX=1"
    End Select

    With CreateObject("DAO.DBEngine.120")
        With .OpenDatabase(excelFile, False, False, connectionString)
            With .OpenRecordset("LogSheet$", dbOpenDynaset, dbAppendOnly, dbOptimistic) 
                .AddNew
                With .Fields()
                    .Item("errorNumber").Value = errorNumber
                    .Item("errorDescription").Value = errorDescription
                    .Item("customNote").Value = customNote
                    .Item("errorDate").Value = Now()
                    .Item("Username").Value = UserLogon
                    .Item("Computer").Value = ComputerName
                End With

                .Update
                .Close
            End With

            .Close
        End With
    End With
End Sub

Upvotes: 0

Views: 1461

Answers (2)

HackSlash
HackSlash

Reputation: 5813

Data Access Objects (DAO) is obsolete. The replacement was ActiveX Data Objects (ADO). The code needs to be rewritten using ADODB.Connection objects as seen in this question: Insert a row in an Excel Recordset through Excel VBA

Upvotes: 0

ASH
ASH

Reputation: 20342

This seems like a pretty old tech stack, at this point in time. I think it was around in the early 1990s and it certainly still persists, but the support seems to be dwindling. So, with that caveat, you can try something like this.

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Personally, I would steer clear from these kinds of things, and focus on some kind of cloud tech stack.

Upvotes: -1

Related Questions