SQL in VBA without record macro

[awwcraig]

I have this query which is usually run from in a separate program and then the results get moved over to excel. I’m trying to bypass that step:

SELECT count("PRICE"."ID")
FROM "REFERENCE",
"PRICE",
"ISSUE"
WHERE ( “REFERENCE"."ID" = "PRICE"."ID" ) and
( " PRICE"."ISSUE" = "ISSUE"."ID" ) and
( ( "REFERENCE"."TYPE" = 'NAME' ) AND
( "PRICE"."DATE" = to_date('03/25/2014','mm/dd/yyyy') ) AND
("PRICE"."CHANGED" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS') OR
"PRICE"."ENTERED" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS'))


When I do a “record macro,” and paste in the SQL query from above to the Connection I had setup, it turns into:

With ActiveWorkbook.Connections("TEST").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT count(""PRICE"".""ID"") " & Chr(13) & "" & Chr(10) & " FROM ""REFERENCE"", " & Chr(13) & "" & Chr(10) & " ""PRICE"", " & Chr(13) & "" & Chr(10) & " ""ISSUE"" " & Chr(13) & "" & Chr(10) & " WHERE ( “REFER" _
, _
"ENCE"".""ID"" = ""PRICE"".""ID"" ) and " & Chr(13) & "" & Chr(10) & " ( "" PRICE"".""ISSUE"" = ""ISSUE"".""ID"" ) and " & Chr(13) & "" & Chr(10) & " ( ( ""REFERENCE"".""TY" _
, _
"PE"" = 'NAME' ) AND " & Chr(13) & "" & Chr(10) & " ( ""PRICE"".""DATE"" = to_date('03/25/2014','mm/dd/yyyy') ) AND " & Chr(13) & "" & Chr(10) & " (""PRICE"".""CH" _
, _
"ANGED"" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS') OR " & Chr(13) & "" & Chr(10) & " ""PRICE"".""ENTERED"" >= TO_DATE('25-03-" _
, "2014 8:00:00','DD-MM-YYYY HH24:MI:SS'))")
.CommandType = xlCmdSql
.Connection = _
"OLEDB;Provider=**REMOVED**"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
.ServerFillColor = False
.ServerFontStyle = False
.ServerNumberFormat = False
.ServerTextColor = False
End With
With ActiveWorkbook.Connections("TEST")
.Name = "TEST"
.Description = ""
End With
ActiveWorkbook.Connections("TEST").Refresh
End Sub


The record macro code works as long as the date is hard coded, but my goal is to be able to change the dates in the SQL query using an input box, which I have setup below. I can’t seem to get it to work with record macro though.

What is a cleaner way to use VBA to pull data from the connection? So I don’t have to deal with chr(13) // chr(10) and random line breaks.


Dim sToDate As String
Dim sEnteredDate As String
Dim iDayNum As Integer
Dim iMonthNum As Integer

Dim dCurrentDate As Date
Dim dRunDate As Date

dCurrentDate = Date

Response = InputBox("Process Date: ", "Report Date", dCurrentDate)

dRunDate = Response

iDayNum = Day(dRunDate)
iMonthNum = Month(dRunDate)
iYearNum = Year(dRunDate)

If iMonthNum < 13 Then
sToDate = "'" & iMonthNum & "/" & iDayNum & "/" & iYearNum & "'"
sEnteredDate = "'" & iDayNum & "-" & iMonthNum & "-" & iYearNum & " "
End If


SELECT count("PRICE"."ID")
FROM "REFERENCE",
"PRICE",
"ISSUE"
WHERE ( “REFERENCE"."ID" = "PRICE"."ID" ) and
( " PRICE"."ISSUE" = "ISSUE"."ID" ) and
( ( "REFERENCE"."TYPE" = 'NAME' ) AND
( "PRICE"."DATE" = to_date(“ & sToDate & “,'mm/dd/yyyy') ) AND
("PRICE"."CHANGED" >= TO_DATE(“ & sEnteredDate & “ 8:00:00','DD-MM-YYYY HH24:MI:SS') OR
"PRICE"."ENTERED" >= TO_DATE(“ & sEnteredDate & “ 8:00:00','DD-MM-YYYY HH24:MI:SS'))



Thank you,

 

[Norie]

Try replacing this with your SQL statement, without all the Chr(10), Chr(13) etc.

Array( _
 "SELECT count(""PRICE"".""ID"") " & Chr(13) & "" & Chr(10) & " FROM ""REFERENCE"", " & Chr(13) & "" & Chr(10) & " ""PRICE"", " & Chr(13) & "" & Chr(10) & " ""ISSUE"" " & Chr(13) & "" & Chr(10) & " WHERE ( “REFER" _
 , _
 "ENCE"".""ID"" = ""PRICE"".""ID"" ) and " & Chr(13) & "" & Chr(10) & " ( "" PRICE"".""ISSUE"" = ""ISSUE"".""ID"" ) and " & Chr(13) & "" & Chr(10) & " ( ( ""REFERENCE"".""TY" _
 , _
 "PE"" = 'NAME' ) AND " & Chr(13) & "" & Chr(10) & " ( ""PRICE"".""DATE"" = to_date('03/25/2014','mm/dd/yyyy') ) AND " & Chr(13) & "" & Chr(10) & " (""PRICE"".""CH" _
 , _
 "ANGED"" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS') OR " & Chr(13) & "" & Chr(10) & " ""PRICE"".""ENTERED"" >= TO_DATE('25-03-" _
 , "2014 8:00:00','DD-MM-YYYY HH24:MI:SS'))")

Something like this, which should also make it easier to substitute the date from the input box.

strSQL = strSQL & " SELECT count(""PRICE"".""ID"")"
strSQL = strSQL & " FROM ""REFERENCE"",""PRICE"",""ISSUE"""
strSQL = strSQL & " WHERE ( “REFERENCE"".""ID"" = ""PRICE"".""ID"" ) AND "
strSQL = strSQL & " ( "" PRICE"".""ISSUE"" = ""ISSUE"".""ID"" ) AND "
strSQL = strSQL & " ( ( ""REFERENCE"".""TYPE"" = 'NAME' ) AND "
strSQL = strSQL & " ( ""PRICE"".""DATE"" = to_date('03/25/2014','mm/dd/yyyy') ) AND "
strSQL = strSQL & " (""PRICE"".""CHANGED"" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS') OR "
strSQL = strSQL & " ""PRICE"".""ENTERED"" >= TO_DATE('25-03-2014 8:00:00','DD-MM-YYYY HH24:MI:SS'))"

With ActiveWorkbook.Connections("TEST").OLEDBConnection
 .BackgroundQuery = True
 .CommandText = strSQL

 

[awwcraig]

I just tried your suggestion, but I get the "Failed to refresh" error box.

Do you just look for any double quote and add an extra? So many double quotes makes it hard to ensure I got them all haha.

 

[Norie]

I didn't add any double quotes, well not intentionally anyway.

I actually tried to be as accurate as I could to the recorded code.

Mind you I don't think I've ever seen an SQL statement with field names enclosed in double quotes, are you sure they are needed?

 

[awwcraig]

The SQL query is going into Sybase/Infomaker 11.5 via the excel connection.

The query was originally in Infomaker & then we'd save the results into excel (I'm trying to get rid of that step). The syntax for the query is what I pasted above (with the double quotes).

I basically just need the macro to open the connection & paste in the query to the command text section (including the date parameter to update it daily), but I can't get it to work other than with a defined date & the record macro function.