Using SQL in VBA to Query External Database 1

[shVBA]

Hi,

I am trying to use SQL in VBA to query an external database, i have set the query up in microsoft query and just want to pass the SQL to the query with variables in each time. I am getting an error when running the code at With Sheets("Sheet1").Querytable(1)

The Code is as follows:

Sub Refresh_Query_5A()

Dim SDate As Date
Dim EDate As Date
Dim MySQL As String

Sheets("Control").Select

SDate = Range("D19").Value
EDate = Range("D20").Value

MySQL = "SELECT Invitation.INVT_ID, Invitation.INVT_ADD_DATE, Person.PN_PARTNER_SYS_REF, Person.PN_FIRST_NAME, Person.PN_SURNAME, Max(Course_Invitation.CRSINV_COURSE_ID) AS 'Max of CRSINV_COURSE_ID', Person_Role.PROLE_ORG_NAME, Person_Role.PROLE_BA, Person_Role.PROLE_PAY_LOCATION "
MySQL = MySQL + "FROM CFOUR.dbo.Course_Invitation Course_Invitation, CFOUR.dbo.Deleg_Invitation Deleg_Invitation, CFOUR.dbo.Delegate Delegate, CFOUR.dbo.Invitation Invitation, CFOUR.dbo.Person Person, CFOUR.dbo.Person_Role Person_Role "
MySQL = MySQL + "WHERE Deleg_Invitation.DELINV_INVT_ID = Invitation.INVT_ID AND Deleg_Invitation.DELINV_DEL_ID = Delegate.DEL_ID AND Delegate.DEL_PERSON_ID = Person.PN_ID AND Course_Invitation.CRSINV_INVT_ID = Invitation.INVT_ID AND Person_Role.PROLE_ID = Delegate.DEL_PROLE_ID "
MySQL = MySQL + "GROUP BY Invitation.INVT_ID, Invitation.INVT_ADD_DATE, Person.PN_PARTNER_SYS_REF, Person.PN_FIRST_NAME, Person.PN_SURNAME, Person_Role.PROLE_ORG_NAME, Person_Role.PROLE_BA, Person_Role.PROLE_PAY_LOCATION "
MySQL = MySQL + "HAVING (Invitation.INVT_ADD_DATE>={ts '2008-03-01 00:00:00'} And Invitation.INVT_ADD_DATE<{ts '2008-04-01 00:00:00'})"

With Sheets("Sheet1").QueryTable(1)
.CommandText = MySQL
.Refresh
End With

End Sub

Thanks In Advance

 

[PHV]

I am getting an error
Any chance you could post the error message ?
Anyway, I'd replace this:
With Sheets("Sheet1").QueryTable(1)
with this:
With Sheets("Sheet1").QueryTable[!]s[/!](1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shVBA]

Thanks very much - That has helped, i was getting an object error, howver i have now changed the code to contain my variable and am now getting the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]syntax error converting datetimefrom character string

the code I have chaged is:

MySQL = MySQL + "HAVING (Invitation.INVT_ADD_DATE>={ts '" & SDate & "'} And Invitation.INVT_ADD_DATE<{ts '" & EDate & "'})"

Thanks
smherron

 

[Andrzejek]

Do yourself a favor and do this:

MySQL = MySQL + " HAVING (Invitation.INVT_ADD_DATE >= {ts '" & SDate & "'} And Invitation.INVT_ADD_DATE < {ts '" & EDate & "'})"

[blue]Debug.Print MySQL[/blue]

then, copy your sql from Debug window, and try it in your microsoft query.

Many times, just by looking at your SQL in Debug window you may be able to see your error.


Have fun.

---- Andy

 

[SkipVought]

Hi,

And as far as your DATES, which, it seems, you may want in your HAVING clause (which I have positioned in the WHERE clause and I am assuming that your DB may be Oracle)...

MySQL = "SELECT"
MySQL = MySQL & "  Invitation.INVT_ID"
MySQL = MySQL & ", Invitation.INVT_ADD_DATE"
MySQL = MySQL & ", Person.PN_PARTNER_SYS_REF"
MySQL = MySQL & ", Person.PN_FIRST_NAME"
MySQL = MySQL & ", Person.PN_SURNAME"
MySQL = MySQL & ", Max(Course_Invitation.CRSINV_COURSE_ID) AS 'Max of CRSINV_COURSE_ID'"
MySQL = MySQL & ", Person_Role.PROLE_ORG_NAME"
MySQL = MySQL & ", Person_Role.PROLE_BA"
MySQL = MySQL & ", Person_Role.PROLE_PAY_LOCATION "
MySQL = MySQL & "FROM"
MySQL = MySQL & "  CFOUR.dbo.Course_Invitation Course_Invitation"
MySQL = MySQL & ", CFOUR.dbo.Deleg_Invitation Deleg_Invitation"
MySQL = MySQL & ", CFOUR.dbo.Delegate Delegate"
MySQL = MySQL & ", CFOUR.dbo.Invitation Invitation"
MySQL = MySQL & ", CFOUR.dbo.Person Person"
MySQL = MySQL & ", CFOUR.dbo.Person_Role Person_Role "
MySQL = MySQL & "WHERE Deleg_Invitation.DELINV_INVT_ID = Invitation.INVT_ID"
MySQL = MySQL & "  AND Deleg_Invitation.DELINV_DEL_ID = Delegate.DEL_ID"
MySQL = MySQL & "  AND Delegate.DEL_PERSON_ID = Person.PN_ID"
MySQL = MySQL & "  AND Course_Invitation.CRSINV_INVT_ID = Invitation.INVT_ID"
MySQL = MySQL & "  AND Person_Role.PROLE_ID = Delegate.DEL_PROLE_ID"[b]
MySQL = MySQL & "  AND Invitation.INVT_ADD_DATE>=To-Date(" & sDate & ",'yyyy/mm/dd')"
MySQL = MySQL & "  And Invitation.INVT_ADD_DATE <To-Date(" & eDate & ",'yyyy/mm/dd') "[/b]
MySQL = MySQL & "GROUP BY"
MySQL = MySQL & "  Invitation.INVT_ID"
MySQL = MySQL & ", Invitation.INVT_ADD_DATE"
MySQL = MySQL & ", Person.PN_PARTNER_SYS_REF"
MySQL = MySQL & ", Person.PN_FIRST_NAME"
MySQL = MySQL & ", Person.PN_SURNAME"
MySQL = MySQL & ", Person_Role.PROLE_ORG_NAME"
MySQL = MySQL & ", Person_Role.PROLE_BA"
MySQL = MySQL & ", Person_Role.PROLE_PAY_LOCATION "

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shVBA]

Thanks for everyone's help...

There is just one last thing.... it is running fine now with no errors however it is running the query between the dates:

03-January-2008 & 04-January-2008 and what i need it to do is run from 01-March-2008 to 01-April 2008

i will post the code i have again so it is clear:

Sub Refresh_Query_5A()

Dim SDate As Date
Dim EDate As Date
Dim MySQL As String

Sheets("Control").Select

SDate = Range("D19").Value
EDate = Range("D20").Value

SDate = CDate(SDate)
EDate = CDate(EDate)

SDate = Format(SDate, "yyyy-mm-dd")
EDate = Format(EDate, "yyyy-mm-dd")

MsgBox SDate
MsgBox EDate

MySQL = "SELECT"
MySQL = MySQL & "  Invitation.INVT_ID"
MySQL = MySQL & ", Invitation.INVT_ADD_DATE"
MySQL = MySQL & ", Person.PN_PARTNER_SYS_REF"
MySQL = MySQL & ", Person.PN_FIRST_NAME"
MySQL = MySQL & ", Person.PN_SURNAME"
MySQL = MySQL & ", Max(Course_Invitation.CRSINV_COURSE_ID) AS 'Max of CRSINV_COURSE_ID'"
MySQL = MySQL & ", Person_Role.PROLE_ORG_NAME"
MySQL = MySQL & ", Person_Role.PROLE_BA"
MySQL = MySQL & ", Person_Role.PROLE_PAY_LOCATION "
MySQL = MySQL & "FROM"
MySQL = MySQL & "  CFOUR.dbo.Course_Invitation Course_Invitation"
MySQL = MySQL & ", CFOUR.dbo.Deleg_Invitation Deleg_Invitation"
MySQL = MySQL & ", CFOUR.dbo.Delegate Delegate"
MySQL = MySQL & ", CFOUR.dbo.Invitation Invitation"
MySQL = MySQL & ", CFOUR.dbo.Person Person"
MySQL = MySQL & ", CFOUR.dbo.Person_Role Person_Role "
MySQL = MySQL & "WHERE Deleg_Invitation.DELINV_INVT_ID = Invitation.INVT_ID"
MySQL = MySQL & "  AND Deleg_Invitation.DELINV_DEL_ID = Delegate.DEL_ID"
MySQL = MySQL & "  AND Delegate.DEL_PERSON_ID = Person.PN_ID"
MySQL = MySQL & "  AND Course_Invitation.CRSINV_INVT_ID = Invitation.INVT_ID"
MySQL = MySQL & "  AND Person_Role.PROLE_ID = Delegate.DEL_PROLE_ID"
MySQL = MySQL & "  AND Invitation.INVT_ADD_DATE>=(" & SDate & ")"
MySQL = MySQL & "  And Invitation.INVT_ADD_DATE <(" & EDate & ") "
MySQL = MySQL & "GROUP BY"
MySQL = MySQL & "  Invitation.INVT_ID"
MySQL = MySQL & ", Invitation.INVT_ADD_DATE"
MySQL = MySQL & ", Person.PN_PARTNER_SYS_REF"
MySQL = MySQL & ", Person.PN_FIRST_NAME"
MySQL = MySQL & ", Person.PN_SURNAME"
MySQL = MySQL & ", Person_Role.PROLE_ORG_NAME"
MySQL = MySQL & ", Person_Role.PROLE_BA"
MySQL = MySQL & ", Person_Role.PROLE_PAY_LOCATION "

Debug.Print MySQL

With Worksheets("BAE Volume 5 Part A").QueryTables(1)
.CommandText = MySQL
.Refresh
End With

End Sub

Thanks Again Everyone!!!

 

[shVBA]

ok sorry about this i have managed to get the dates in the right format now...

BUT!

the actual query is not refreshing, the SQL is being entered and if i go into microsoft query it has already been changed dependant on the variables and i can then run it but when it goes through the refresh command it is not bringing back data or an error message?

if anyone can help it would be greatly appreciated...

Thanks
Stuart

 

[SkipVought]

Your problem is in the conversion of dates.

Are the values in D19 & D20 REAL DATES? You can test that, if you can change the FORMAT in both cells from DATE to GENERAL, which will not change the VALUE, only what is displayed.

I suspect that you have TEXT in these cell and not DATE values.

Your TEXT is in the form of d-m-yyyy and Excel ASSUMES m-d-yyyy when the conversion occurs.

The safest way to enter date values in in the yyyy-m-d format. There is no ambiguity.

faq68-5827.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[PHV]

Furthermore, I'd replace this:
.Refresh
with this:
.Refresh False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shVBA]

The problem im having seems odd, it connects to the database and then seems to refresh really quickly. when it finishes there is no data there, but if i go into microsoft wuery and click "cancel and return data to excel" then it runs the query with the parameters set by the VBA code. Does anyone know why it would do this.

Thanks

Stuart