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:
Thanks In Advance
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:
Code:
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