CODEWITHSUNDEEP
sqlvbaexcelms-accessdao
Nick Rose
Nick Rose

Reputation: 58

Error "Object required" when running working parameterised Access SQL query (INSERT INTO) from Excel VBA

I'm running some VBA from an Excel file to update an Access database following data manipulation in Excel. It creates and executes the SQL lines via DAO links. On occasion the data to be entered contains characters which ruin SQL (e.g. an apostrophe in the values of an INSERT INTO statement), so an error catching parameterised query statement tries to make these changes.

After clearing the old errors the query statement returns the error "Object required" when it runs, but it DOES work (the database is modified in the correct way). The question is: why does it return this error and am I doing something wrong? Is it likely to be an issue?

Code summary from initial error:

Dim TableName As String, FieldString As String
Dim strSQL As String, valstring As String
Dim NewTableRef As Range
Dim rs As DAO.Recordset, ws As DAO.Workspace, db As DAO.Database

' Fieldstring is a string containing all field names for the table to be changed'
' TableName is the table to be changed'
' Connecting to database and setting ranges done previously'
' strSQL is initially set by the type of change to make'

' modify the recordset'
On Error Resume Next
db.Execute (strSQL)

If Err <> 0 Then
    ' Only an issue for inputs to Table1'
    If TableName = "Table1" Then
        ' try it with parameters'
        strSQL = "PARAMETERS @var1 TEXT, @var2 TEXT, @var3 TEXT, @var4 TEXT, @var5 TEXT;" _
            & "INSERT INTO " & TableName & " (" & FieldString & ") " & " VALUES (@var1,@var2,@var3,@var4,@var5);"

        Set qdf = db.CreateQueryDef("", strSQL)

        qdf!var1 = NewTableRef.Offset(Row - NewTableRef.Row, 1).Value
        qdf!var2 = NewTableRef.Offset(Row - NewTableRef.Row, 2).Value
        qdf!var3 = NewTableRef.Offset(Row - NewTableRef.Row, 3).Value
        qdf!var4 = NewTableRef.Offset(Row - NewTableRef.Row, 4).Value
        qdf!var5 = NewTableRef.Offset(Row - NewTableRef.Row, 5).Value

        Err.Clear
        Error.Clear

        qdf.Execute

        If Err <> 0 Then
            With ThisWorkbook.Sheets("DB Error Log").Range("Error_Log")
                .Columns(1).Offset(ErrNum).Value = Now()
                .Columns(2).Offset(ErrNum).Value = ii
                .Columns(3).Offset(ErrNum).Value = TableName
                .Columns(4).Offset(ErrNum).Value = PKey
                .Columns(5).Offset(ErrNum).Value = Error(Err)
                .Columns(6).Offset(ErrNum).Value = valstring
                ErrNum = ErrNum + 1
                Debug.Print ErrNum
            End With
        End If


    Else

        ' write error has occurred'
        ' log this error'
        With ThisWorkbook.Sheets("DB Error Log").Range("Error_Log")
            .Columns(1).Offset(ErrNum).Value = Now()
            .Columns(2).Offset(ErrNum).Value = ii
            .Columns(3).Offset(ErrNum).Value = TableName
            .Columns(4).Offset(ErrNum).Value = PKey
            .Columns(5).Offset(ErrNum).Value = Error(Err)
            .Columns(6).Offset(ErrNum).Value = valstring
            ErrNum = ErrNum + 1
            Debug.Print ErrNum
        End With

        Error.Clear
        Err.Clear

    End If

End If
On Error GoTo 0

Upvotes: 1

Views: 534

Answers (1)

Rory
Rory

Reputation: 34035

Error is a function that returns an error description from an error number; it is not an Object so you can't use Error.Clear - remove that from your code.

Upvotes: 2

Related Questions