SQL Query from Excel VBA

Question

Post reply

SQL Query from Excel VBA

photonicman

Right there with Babe

Points: 736
More actions
April 7, 2010 at 10:26 am

#386657

I've been receiving Run-time error 1004 when executing the macro. The error is being generated by the "SET" commands. I can execute the same query in SQL Server Management Studio without error however VBA rejects it.

I'm not sure if they're is a different way to send the "SET" Commands via VBA? :unsure:

Sub GetSubmissions()

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 [1stcomp].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 [1stcomp].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 [1stcomp].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 [1stcomp].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 [1stcomp].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 [1stcomp].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 [1stcomp].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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Here's some stuff that can help us answer your question:

1) add a DEBUG.PRINT SQL and paste the results here so that we can more easily see the whole SQL command that you are trying to execute.

2) Print out the error message (not just the number) and paste that here as well..

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

photonicman Right there with Babe Points: 736 More actions · Answer 2

The Debug.Print SQL command doesn't yield any results, the SQL Query is not allowed to execute, excel stops the query from executing with the run-time error 1004.

I think there is another way to SET the variables from excel...

Running the Same SQL Query in SQL Server Management Studio works 100% - No Errors, if I remove the SET commands from the same query in Excel VBA I no longer receive the run-time error however my variables are null returning no results.

photonicman Right there with Babe Points: 736 More actions · Answer 3

photonicman

Right there with Babe

Points: 736

April 7, 2010 at 11:36 am

#1147607

Also, the error is not very descriptive, I attached a copy of the error, if I goto Debug, the select line of code is at the end of the macro where the SQL Query should be invoked.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

photonicman (4/7/2010)

The Debug.Print SQL command doesn't yield any results, the SQL Query is not allowed to execute, excel stops the query from executing with the run-time error 1004.

I think there is another way to SET the variables from excel...

Running the Same SQL Query in SQL Server Management Studio works 100% - No Errors, if I remove the SET commands from the same query in Excel VBA I no longer receive the run-time error however my variables are null returning no results.

Your VBA "Debug.Print SQL" command must be after all of your "SQL = SQL & ..." commands, but before any other VBA commands. And you have to be in debug mode. Put it before the "With.." statement and add a breakpoint on the WITH if that helps.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

photonicman Right there with Babe Points: 736 More actions · Answer 5

Thank you, that did help, however I'm still lost 🙁

Immediate Window:

DECLARE @mydate DATETIME

DECLARE @First AS DATETIME

DECLARE @Last AS DATETIME

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)

SELECT 'MW & W' = (Select Count(Offices.PA_State)

FROM [1stcomp].dbo.Offices

WHERE Offices.PA_EnterpriseID <> '1' AND Offices.PA_State IN ('AR', 'IA', 'KS', 'NM', 'MO', 'NE', 'OK', 'SD', 'TX', 'CO', 'HI', 'MN', 'NV')

AND CONVERT(VARCHAR,Offices.PA_DateSubmitted,101)

BETWEEN @First AND @Last)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

photonicman (4/7/2010)

Also, the error is not very descriptive, I attached a copy of the error, if I goto Debug, the select line of code is at the end of the macro where the SQL Query should be invoked.

OK, that's just the generic "VBA crashed because there was an error that was not handled" error message. If you add an error-handler to your routine, you should then be able to trap and display the actual original error.

(NOTE: this is always a good idea anyway as any unhandled VBA error will cause the entire Excel VBA environment to crash and lose all of your VBA context. Excel can keep running, but all of your VBA variables and objects either gets wiped out or reset back to their original values).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

photonicman Right there with Babe Points: 736 More actions · Answer 7

photonicman

Right there with Babe

Points: 736

April 7, 2010 at 11:49 am

#1147629

Just to Clarify, the Debug Window Code, pated into Management Studio works 100%

photonicman Right there with Babe Points: 736 More actions · Answer 8

I'm not able to determine the cause of the error, excel refuses to allow the SET command, then again, maybe the SET command is invalid for use in the query from excel... I don't know what to do to produce a different error message with another description, and I am baffled as to why excel errors only with the presence of "SET"

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

photonicman (4/7/2010)

Just to Clarify, the Debug Window Code, pated into Management Studio works 100%

Understood. This is uncommon, but still a reasonable occurrence, primarily because the SQL that you are passing must first be interpreted by your access methods provider (probably either ODBC or ADO) before it is passed on to SQL Server.

If you are using a valid SQL Server feature or syntax that ODBC or ADO does not support, then you can get this exact behavior.

Looking at your query now myself, I can see three possible causes:

1) ODBC/ADO does not like/understand your use of SET (which is probably what you are suspecting),

2) ODBC/ADO does not like/understand the SQl Server CONVERT function, or...

3) ODBC/ADO does not like/understand the tricky subquery that you've put together...

Possible solutions:

1) Use a stored procedure instead. This is my recommended solution, you already know that your SQL works on SQL Server, so this should be straight-forward.

2) Use SELECT instead of SET to assign values (this is the "other way to SET" that you were asking about). If the problem is (1) abve, then this should fix it.

3) Use ODBC/ADO compliant conversion. If (2) above is the problem then this should fix it. I cannot remember off-hand exactly how to do this, so I would have to research it...

4) Rewrite your query without the subquery. I don't think that you need it for this, so if (3) above is the problem then this should fix it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Oops, I forgot one other possible solution: Use a PASSTHOUGH option. Almost all access method libraries have some kind of option to allow the query to be passed striaght to the DB without any intermediate interpretation. You lose some other features, but it usually fixes these kinds of problems.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

photonicman Right there with Babe Points: 736 More actions · Answer 11

Excellent Advice,

I just tired SELECT VS SET and I still receive the same error message, this must mean it wasn't the "SET" command causing the issue. Also I have another QUERY that is almost identical, the only different is I do not have variables:

SQL = SQL + "SELECT 'MW & W' = (Select Count(Offices.PA_State) " + vbNewLine

SQL = SQL + "FROM [1stcomp].dbo.Offices WHERE Offices.PA_Status = '4' AND Offices.PA_EnterpriseID <> '1'AND" + vbNewLine

SQL = SQL + "Offices.PA_State IN (" & Region("MW & W") & ")" + vbNewLine

SQL = SQL + "), " + vbNewLine

SQL = SQL + "'West' = (Select Count(Offices.PA_State) " + vbNewLine