Error 3075 SQL statement in VB

[ainemc76]

Hi

Still trying to debug the following code and getting error messages. Complies ok when I try to run it pops up error message. Can anyone help?

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
'On Error GoTo Err_AuditEditBegin
    'Purpose:    Write a copy of the old values to temp table.
    '            It is then copied to the true audit table in AuditEditEnd.
    'Arugments:  sTable = name of table being audited.
    '            sAudTmpTable = name of the temp audit table.
    '            sKeyField = name of the AutoNumber field.
    '            lngKeyValue = Value of the AutoNumber field.
    '            bWasNewRecord = True if this was a new insert.
    'Return:     True if successful
    'Usage:      Called in form's BeforeUpdate event. Example:
    '                bWasNewRecord = Me.NewRecord
    '                Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String

    'Remove any cancelled update still in the tmp table.
    Set db = DBEngine(0)(0)
    sSQL = "DELETE FROM" & sAudTmpTable & ";"
    db.Execute sSQL

    ' If this was not a new record, save the old values.
    If Not bWasNewRecord Then
        sSQL = "INSERT INTO" & sAudTmpTable & " ( audType, audDate, audUser ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & "WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
        db.Execute sSQL, dbFailOnError
    End If
    AuditEditBegin = True

Exit_AuditEditBegin:
    Set db = Nothing
    Exit Function

End Function

Error line appears on db.execute sSQL. Is asking for a missing operator in SQL statement.
Thanx a mil%-)

 

[SkipVought]

Hi,

insert a Debug.Print statement...

        sSQL = "INSERT INTO" & sAudTmpTable & " ( audType, audDate, audUser ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & "WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"

        [b]Debug.Print sSQL[/b]

        db.Execute sSQL, dbFailOnError

Can you run the assembled sSQL?

Skip,

_[red][/red]

 

[SkipVought]

Add a space before the " WHERE...." for one.

Skip,

_[red][/red]

 

[ainemc76]

Hi SkipVought
This still does not solve problem. I am a complete novice at this, this is the first time I've written/adapted VB. I am unsure what you mean by 'Can you run the assembled sSQL?'. Sorry!

Thanx a mil :~/ %-)

 

[vacant]

Which execute statement is it failing on? (there are two in your code)

Think you need to put at least a couple more spaces in; "DELETE FROM " and "INSERT INTO "

Use the debug.print line as SkipVought says, then run the code. This will print the constructed SQL string to the Immediate Window (press Ctrl+G if that's not already visible)

Copy this SQL string

Open a new query in SQL view and paste in the SQL from the clipboard

Switch the query into Design View (if it lets you), and you can often spot errors a lot quicker this way. At least that's the way I would do it...

 

[PHV]

Anyway, the INTO clause has 3 field names and the SELECT clause has much more, so the whole INSERT instruction is invalid ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"This still does not solve problem."

What I proposed to you was a TOOL for debugging your SQL.

If you run your code and then look in the Immediate Window, you'll see the VALUE in sSQL. That VALUE is the SQL code you are assembling, building, concatenating, whatever.

Post that VALUE (the generated SQL). As PHV observed, you have some problems in the SQL you are generating. Examining the assembles string will be alot easier than trying to figure out what's wron from just the code.

Skip,

_[red][/red]