SQL ? (A2K)

Topic

New Reply

You seem to have “INTO refCMC_SBAD_ADDR3” all over the place! It should only be there once, after “INSERT INTO”. Remove all the other occurrances.

Reply | Quote

Replies

Extremely long lines split by HansV

I’m hoping someone can show me my code error:

Dim dbs As Database
Dim rst As Recordset
Dim mySQL As String

Dim ind As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("dbo_CMC_SBAD_ADDR")
    With rst
        .MoveFirst
            Do Until .EOF = True
                If IsNull("SBSB_CK") = False Then
                    mySQL = "INSERT INTO refCMC_SBAD_ADDR3 " & _
                    "(SBSB_CK, SBAD_TYPE, SBAD_ADDR1, SBAD_ADDR2,SBAD_CITY, SBAD_STATE, _
                       SBAD_ZIP,SBAD_PHONE) " & _
                        "INTO refCMC_SBAD_ADDR3 " & _
                "VALUES ('" & !SBSB_CK & "','" & !SBAD_TYPE & "','" & !SBAD_ADDR1 & "','" & !SBAD_ADDR2 & "," & _
                   !SBAD_CITY & "','" & !SBAD_STATE & "','" & !SBAD_ZIP & "','" & !SBAD_PHONE & "') " & _
                "INTO refCMC_SBAD_ADDR3"
            Debug.Print mySQL
            DoCmd.RunSQL mySQL
            Debug.Print
                End If
        .MoveNext
        Loop
    End With
rst.Close
MsgBox "Process Complete"
End Sub

I get “Run Time Error 3134”–“Syntax Error in INSERT INTO statement” with the BOLDED (above) highlighted.

Thanks,

Reply | Quote

Do what Mark says. you also have a problem in the line:

“VALUES (‘” & !SBSB_CK & “‘,'” & !SBAD_TYPE & “‘,'” & !SBAD_ADDR1 & “‘,'” & !SBAD_ADDR2 & “,” & _

The “,” at the end of the line should be replaced with “‘,'” to become:

“VALUES (‘” & !SBSB_CK & “‘,'” & !SBAD_TYPE & “‘,'” & !SBAD_ADDR1 & “‘,'” & !SBAD_ADDR2 & “‘,'” & _

Reply | Quote