Looping SQL query w/changing parameters in each loop not working

I have an SQL database that stores millions of mine sample results. Every quarter selected samples need to be pulled for analysis for royalty payments. I want to query the SQL database and pull back all of the relevant results into memory. Then I have the operator scan in the sample barcode and vba tells the operator (via a userform) which claim the sample is in and if it needs to be analyzed for this quarter.

I've posted the relevant portion of the code I am having problems with. Because of excels row limitation, I have to query the database in chunks of 65000. I've tested each of these modules individually and they work. However when put together, the query does not update itself to get new data when it's conditions have changed.

I want to stay away from having the code query each time a sample is scanned because the line to the SQL server experiences a high volume of traffic resulting in a 5 - 10 second wait for each query. So the idea is that the program runs when the spreadsheet opens and pulls all of the data into memory. From there the operator can scan and get instantaneous results.

Any ideas on how to get the query to refresh based on its new parameters instead of just using the first parameters given in the first loop? The SQL code was written using the macro recorder so it isn't pretty but it works. The red text indicates the place where the conditions are changed for each loop.

Please Login or Register  to view this content.

There is alot of room for improvement:

You need to investigate the parameters collection of the querytable and
write a Query like this:

"SELECT sstn_surface_samples.sample_number,
sstn_surface_samples.Royalty, sstn_surface_samples.Type,
sstn_surface_samples.Mined FROM Fusion_Central.dbo.sstn_surface_samples
sstn_surface_samples
WHERE (sstn_surface_samples.sample_number>= ? And
sstn_surface_samples.sample_number<= ?)"

Now add two parameters to the collection and refresh your query. Note
that parameter markers(="?") might be different for your database
system ("?" works for SQL server)

I would not create the Query by code and delete it. Just create it
once, and then only change the parameters after that.

Use the querytable events (Before and afterrefresh) to execute VBA that
needs to go before or after refreshing the query. This way you can also
refresh data by hand as well. This event can be used to check
rowoverflow.

DM Unseen

Thanks. So something like the following?

Please Login or Register  to view this content.

Thanks much DM Unseen! Your insight was key to getting the following code to work. Just seems too bad that you have to use an excel sheet range as opposed to a variable to automatically change the parameter.

thanks again!

Please Login or Register  to view this content.

You could change:

Application.ThisWorkbook.Worksheets("Query").Range("I1") = First
Application.ThisWorkbook.Worksheets("Query").Range("I2") = Last

Set Param1 = qt.Parameters.Add(First, xlParamTypeVarChar)
Param1.SetParam xlRange, Range("Query!I1")
Set Param2 = qt.Parameters.Add(Last, xlParamTypeVarChar)
Param2.SetParam xlRange, Range("Query!I2")

TO

set this code before the loop
Set Param1 = qt.Parameters.Add("First", xlConstant )
Set Param2 = qt.Parameters.Add("Last", xlConstant )


Set this code in the loop

Param1.SetParam xlConstant , First
Param2.SetParam xlConstant , Last

Thsi way you defined the parameters only once and just fill them
directly without a range

Dm Unseen

Laurin wrote:
>..
>
> Select Case Len(StartNum)
> Case Is = 1: First = "MOPB00000" & StartNum
> Case Is = 2: First = "MOPB0000" & StartNum
> Case Is = 3: First = "MOPB000" & StartNum
> Case Is = 4: First = "MOPB00" & StartNum
> Case Is = 5: First = "MOPB0" & StartNum
> Case Else: First = "MOPB" & StartNum
> End Select
>


You can simplify this (and the similar code for EndNum) by writing:

First = "MOPB" & Format(StartNum, "000000")

Even better. Thanks!

So the final code boils down to less than a page. Thanks for the tips.

Please Login or Register  to view this content.