Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Microsoft Query Not Running Query

Status
Not open for further replies.

shVBA

Programmer
Apr 29, 2008
16
GB

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
 
.....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
 
once the code has finished
Which code ?

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

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

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
 
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
 
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...
 
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
 
Replace this:
.Refresh BackgroundQuery = False
with this:
.Refresh BackgroundQuery:=False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top