Microsoft Query Not Running Query

[shVBA]

I have VBA cose that inserts SQL into microsoft query and then is supposed to refresh the query. The code runs fine without any errors and inserts the SQL into ms Query. once the code has finished if i look at the query it does not return any rows of data but then if i go into msQuery and exit and return data to ms excel it runs the query fine.

im not sure if thats a good explanation but if anyone can help it would be greatly appreciated.

Thanks

 

[xlbo]

.....telepathy function not engaged today......will need to see the code.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

once the code has finished
Which code ?

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

 

[shVBA]

Apologies...

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, "mm-dd-yyyy")
EDate = Format(EDate, "mm-dd-yyyy")

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 BackgroundQuery = False
End With

End Sub

 

[xlbo]

does it take long to run?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shVBA]

no, as soon as it has put the SQL in is changes the statusbar to "connecting to datasource: for about 2 seconds and then ends....

The code takes less than 20 seconds to run...

 

[xlbo]

20 seconds is a long time to just change the SQL string

Is there anything else happening on that worksheet?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Replace this:
.Refresh BackgroundQuery = False
with this:
.Refresh BackgroundQuery:=False

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