[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-02-07
7
Medium Priority
?
277 Views
Last Modified: 2008-05-16
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

0
Comment
Question by:wyatt_Smurfit
7 Comments
 
LVL 10

Expert Comment

by:aesmike
ID: 20846969
what's the qeustion here?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20847431
You've told us what you 'want' to happen, what does?
0
 

Author Comment

by:wyatt_Smurfit
ID: 20848167
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 14

Expert Comment

by:ldunscombe
ID: 20848358
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
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 20851903
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
 

Author Comment

by:wyatt_Smurfit
ID: 20960495
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20961161
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question