Use VB to change a query's SQL statement in MsAccess

I am running a query on a query.  So the append query (qry_Append) runs faster I'm trying to limit it to crunch 1 day's data at a time by using the "qry_Filter_Append" query.  

There's a subquery within that append query which causes it to run slowly (lot's to sort but not the issue).  I've got 200K lines of data and limiting it by day will help the process run much more smoothly.

Thanks for the help.
Function RunSeqCheck_Step3()
 
Dim ShipDay As Variant
Dim qrySeqChk_3 As QueryDef
Dim rst As DAO.Recordset
Dim dbsCurrent As Database
 
Set dbsCurrent = CurrentDb
 
ShipDay = 1
 
Do Until qrySeqChk_3.EOF
    ShipDay = ShipDay
    Set qrySeqChk_3 = dbsCurrent.QueryDefs("qry_Filter_Append")
    qrySeqChk_3.SQL = "SELECT * FROM tbl " & _
                      "WHERE qry_Filter_Append.Ship_Day = [ShipDay];"
    
    DoCmd.OpenQuery "qry_Append"
    ShipDay = ShipDay + 1
 
Loop
    
    Set dbsCurrent = Nothing
    Set ShipDay = Nothing
    Set qrySeqChk_3 = Nothing
 
 
End Function

Open in new window

wyatt_SmurfitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aesmikeCommented:
what's the qeustion here?
0
GRayLCommented:
You've told us what you 'want' to happen, what does?
0
wyatt_SmurfitAuthor Commented:
sorry I wasn't clear.

I get an error when trying to sort shipping on the shipping date.  Can somebondy help me correct the code?

Thanks
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ldunscombeCommented:
provided qry_Filter_Append.Ship_Day is numeric then try.

    qrySeqChk_3.SQL = "SELECT * FROM tbl " & _
                      "WHERE qry_Filter_Append.Ship_Day = " &  ShipDay  & ";"

Leigh
0
GRayLCommented:
See my remarks in the snippet
Function RunSeqCheck_Step3()
 
Dim ShipDay As Variant
Dim qrySeqChk_3 As QueryDef
Dim rst As DAO.Recordset
Dim dbsCurrent As Database
 
Set dbsCurrent = CurrentDb
 
ShipDay = 1
 
Do Until qrySeqChk_3.EOF          '<-- this has to be opened as a record set
    ShipDay = ShipDay             '<-- what's this supposed to do?
    Set qrySeqChk_3 = dbsCurrent.QueryDefs("qry_Filter_Append")
                                  '<-- you want to re-define it as a querydef?
    qrySeqChk_3.SQL = "SELECT * FROM tbl " & _
                      "WHERE qry_Filter_Append.Ship_Day = " & ShipDay & ";"
 '------------------------------------------note change-----^---------^--   
    DoCmd.OpenQuery "qry_Append"  '<-- do you mean qry_Filter_Append?
    ShipDay = ShipDay + 1
 
Loop
    
    Set dbsCurrent = Nothing
    Set ShipDay = Nothing
    Set qrySeqChk_3 = Nothing
 
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wyatt_SmurfitAuthor Commented:
To explain what I'm trying to do.  (I may have written the VB completely wrong.)

I have a data set to download each day that includes all records month to date.  Some records from previous days may have updated so we rerun the whole process each day for the entire m2d set. (we could do a update query on the existing data but decided to replace all the new data for several reasons)

So the issue I need help with is I have subquery that takes a long time to run as the month progresses.  I've found that if I limit the query to only 1 day's worth of data it runs much faster; which I'm doing manually today.  So I'm trying to automate using a select query to limit the dataset, run a 2nd query based on the results of the dataset(the 2nd query uses the subquery and also appends this data set to a table), then start the process over for the next day's data.  I also wanted the script to stop once we reached the end of the new data being imported(no need to keep running the script for 30 days of data if we only have 15. D'uh right).

Anyway, hopefully this makes sense.  Thanks for the help.
0
GRayLCommented:
Do you want to show us the whole process, including the subquery?  Did my previous post make any sense to you?  In your update, once you have updated data in a given day, does any of that ever need to be updated again as a result of later events?  If no, why not copy yesteday's data to a temp table, remove yesterday's data, process the temp table and append it back to the original table?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.