Consulting

Results 1 to 2 of 2

Thread: SQL Query in Excel VBA - Runtime Error 1004

  1. #1

    Angry SQL Query in Excel VBA - Runtime Error 1004

    I only receive the error with the "SET" Command, the code producing the error is:

    SET @mydate = CONVERT(VARCHAR(25),GETDATE(),101)
    SET @First = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
    SET @Last = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)

    The Query runs perfectly in Management Studio and if I remove the SET commands I no longer receive the error.

    The Entire Macro:

    GetAgencySubmissions()
    Dim SQL
    SQL = "DECLARE @mydate DATETIME SET @mydate = CONVERT(VARCHAR(25),GETDATE(),101)" + vbNewLine
    SQL = SQL + "DECLARE @First AS DATETIME SET @First = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) " + vbNewLine
    SQL = SQL + "DECLARE @Last AS DATETIME SET @Last = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)" + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "SELECT 'MW & W' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1'AND" + vbNewLine
    SQL = SQL + "Offices.PA_State IN ('AR', 'IA', 'KS', 'NM', 'MO', 'NE', 'OK', 'SD', 'TX', 'CO', 'HI', 'MN', 'NV') " + vbNewLine
    SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "'West' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
    SQL = SQL + "Offices.PA_State IN ('CO', 'HI', 'NM', 'NV') " + vbNewLine
    SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "'Mid-West' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
    SQL = SQL + "Offices.PA_State IN ('AR', 'IA', 'KS', 'MN', 'MO', 'NE', 'OK', 'SD', 'TX') " + vbNewLine
    SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "'East' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
    SQL = SQL + "Offices.PA_State IN ('AL', 'CT', 'DE', 'FL', 'IN', 'MD', 'MI', 'MS', 'NC', 'NH', 'PA', 'RI', 'SC', 'TN', 'VA', 'VT', 'WV') " + vbNewLine
    SQL = SQL + "AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "'FCUG' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
    SQL = SQL + "Offices.PA_State <> 'CA' AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last), " + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "'FIA' = (Select COUNT(Offices.PA_State) " + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices WHERE Offices.PA_EnterpriseID <> '1' AND " + vbNewLine
    SQL = SQL + "Offices.PA_State = 'CA' AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last)," + vbNewLine
    SQL = SQL + "" + vbNewLine
    SQL = SQL + "COUNT(Offices.PA_State) AS 'All'" + vbNewLine
    SQL = SQL + "FROM [DataBase].dbo.Offices " + vbNewLine
    SQL = SQL + "WHERE Offices.PA_EnterpriseID <> '1'AND ISNULL(CONVERT(VARCHAR,Offices.PA_DateSubmitted,101), '') BETWEEN @First AND @Last" + vbNewLine

    With ThisWorkbook.Sheets("Agency Data").QueryTables.Add( _
    Connection:="ODBC;DRIVER=SQL Server;SERVER=****;UID=****;PWD=****;APP=Microsoft Open Database Connectivity;" & _
    "DATABASE=****", Destination:=ThisWorkbook.Sheets("Agency Data").Range("I" & X))

    .CommandText = SQL
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False

    End With
    ThisWorkbook.Sheets("Agency Data").Cells.QueryTable.Delete
    End Sub
    Last edited by photonicman; 04-07-2010 at 08:45 AM. Reason: Adding Post Icon

  2. #2
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    You are probably getting the error because VBA is trying to execute a stored proc as a regular SQL statement. If this is a query you regularly run, consider coding it as a stored proc on SQL Server and using ADO's connection execute() method to run it, with parameters. see

    http://authors.aspalliance.com/steve...les/sprocs.asp

    .02 Stan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •