Select Query Does Not Work When Converted to Vba - Invalid SQL Statement

SELECT query does not work when converted to VBA - invalid SQL statement

That error message is misleading. The real problem is that DoCmd.RunSQL is intended for "action" queries: UPDATE; INSERT; DELETE; etc.

It will not accept a plain SELECT query. For example, this simple SELECT query gives me that same "A RunSQL action requires an argument consisting of an SQL statement" message from DoCmd.RunSQL:

Dim SQLstr As String
SQLstr = "SELECT * FROM tblFoo;"
DoCmd.RunSQL SQLstr

However, DoCmd.RunSQL executes this valid UPDATE statement without error:

SQLstr = "UPDATE tblFoo SET long_text='bar' WHERE id=1;"
DoCmd.RunSQL SQLstr

You need a different method to use your SELECT query. And the choice of method depends on what you want to do with the results returned by the query.

SQL to Excel table in VBA using IF EXISTS complains Invalid SQL statement

You're using the ACE OleDb provider to interact with the spreadsheet. That means you can use only SQL features that provider supports for your data source. IF EXISTS is not supported in this situation.

However, there may be an even more basic problem. I don't believe it's possible to alter the contents of a spreadsheet with SQL executed from ACE OleDb.

SQL Syntax for VBA

Replace this:

SQLType = "SELECT tbl_Users.[UserType] FROM tbl_Users WHERE tbl_Users.[SOEID]='" & UserName & "';"
Debug.Print SQLType
DoCmd.RunSQL SQLType

With this:

SQLType=DLookup("UserType","tbl_Users","SOEID='" & UserName & "'")

SQL query does not work in Excel but works in Access

Essentially, the issue is due to the LIKE operator. Whenever you run an Access query over an ODBC/OLEDB connection, the wildcard to use is the current ANSI version %. However, in Access GUI, the wildcard uses the older version, *. See MSDN docs discussing this wildcard usage.

To be compatible between Excel and Access (VBA or GUI), consider undocumented ALIKE operator to only use %. Additionally, use ADO parameterization using ADO command and avoid concatenation of values to SQL statement. Below replaces the first LIKE with = since no wildcard is used and the make-table action using INTO was removed. Also, New is removed from any Dim lines.

Dim DatabaseFileName As String, connectionstring As String, SQL As String
Dim conn As ADODB.Connection, rs As ADODB.Recordset, ExecSQL As ADODB.Command
Dim I As Long

connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
                    & DatabaseFileName & "; Persist Security Info=False;"

Set conn = New ADODB.Connection
conn.Open connectionstring

' PREPARED STATEMENT WITH ? PLACEHOLDERS
SQL = "SELECT COUNT(v.SITE_ID) AS GCOUNT " _
    & "FROM VRSC_CUSTOMER_SITES v " _
    & "WHERE v.SITE_STREET = ? " _
    & "  AND v.SITE_ST = ? " _
    & "  AND v.SITE_PHONE NOT ALIKE '999%';" _

For I = 2 To 1235
    If ThisWorkbook.Sheets(1).Cells(I, 7) <> "" Then
        Set ExecSQL = New ADODB.Command
        With ExecSQL
            .ActiveConnection = conn
            .CommandText = SQL
            .CommandType = adCmdText

            '  BIND PARAMETERS
            .Parameters.Append .CreateParameter("street_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 7))
            .Parameters.Append .CreateParameter("st_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 5))

            '  EXECUTE QUERY AND BIND INTO RECORDSET
            Set rs = .Execute
        End With
            
        ThisWorkbook.Sheets(1).Cells(I, 8).CopyFromRecordset rs
    End If
Next I

With ThisWorkbook.Sheets(2)
    .Range("A1").CopyFromRecordset rs
End With

Setting Parameters to VBA and SQL Query using DAO

Several issues with your attempts:

• Named Objects: Calling a saved query with ADO Recordset.Open which mostly expects SQL statements or command objects and not named objects. Hence, the reason for your first error. Instead, use Conn.Execute which prepends standard SQL syntax to named objects. Alternatively explicitly pass SELECT * FROM with query objects. This is not an issue for DAO recordsets (library specifically focused on the MS Access object model whereas ADO is generalized for any backend).

• Parameters: Using form control values in backend queries that do not see form values. Any query not run with DoCmd like OpenQuery (for select queries) or RunSQL (for action queries) does not recognize form controls. Hence, the reason for second error. Instead of Forms!MyForm!MyControl, use ADO Command parameters or DAO QueryDefs parameters.
  Search my [vba] tag answers for countless ADO or DAO parameters solutions. See below for your use case:

  Sub EmailQuery(strQueryName As String)
  On Error GoTo Err_EmailQuery_Click
      Dim strEmail As String
      Dim db As DAO.Database
      Dim qdef As DAO.QueryDef
      Dim rs As DAO.Recordset
  
      Set db = CurrentDb
      Set qdef = db.QueryDefs(strQueryName)
  
      With qdef
          ' BIND PARAMETER
          .Parameters("PrmID") = [Forms]![frmMainMenu]![cboAgreement]
          ' OPEN RECORDSET
          Set rs = .OpenRecordset()
      End With
  
      '...loop and email...
  
  Exit_EmailQuery_Click:
      rs.Close
      Set rs = Nothing: Set qdef = Nothing: Set db = Nothing
  Exit Sub
  
  Err_EmailQuery_Click:
      MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
      Resume Exit_EmailQuery_Click
  End Sub
  
  

• Special Functions: Running only MS Access GUI methods like NZ in backend queries that do not recognize such functions. You will run into this error if you resolve above two issues. Use IIF + ISNULL/IS NULL. Similarly, VBA user-defined functions will not be recognized.

---

Related Topics