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

Question

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

What is Error supposed to be, and how do you get an error when you have an On Error Resume Next statement? — Rory
– Rory, Commented May 21, 2015 at 12:24
@Rory Ideally there won't be an error the second time - it will just run. I get an error when checking the "if Err <> 0". It should equal 0 because I cleared it before executingthe query. — Nick Rose
– Nick Rose, Commented May 21, 2015 at 12:25
That doesn't really answer either question. What is Error in your Error.Clear line? And how do you get any error message when your code is suppressing them? — Rory
– Rory, Commented May 21, 2015 at 12:29
@Rory - the error cleared is 3075 - "Syntax error (missing operator in query expression ''Security of members' leadership'." This is the error which leads to the code section posted being called (i.e. the first "if err <> 0 then"). After clearing this error the second (unexpected) error is 424 - "Object required". I get an error message because I check for errors and write any to an error log. It isn't an error message which pauses code running. — Nick Rose
– Nick Rose, Commented May 21, 2015 at 12:35
I guess I'm still not being clear. What do you think Error is in the code? It's not a built-in VBA object, so what is it? (note I am not asking you what error you're getting, I'm asking you what you think this line actually does: Error.Clear) — Rory
– Rory, Commented May 21, 2015 at 12:46

Rory · Accepted Answer · 2015-05-21 13:41:06Z

2

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.

answered May 21, 2015 at 13:41

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related