Dynamic RecordSource using VBA? 1

[Trevahaha]

I have a union query that contains around 760,000 records when run. Because of this, it takes some time.

I would like to make this more efficient because we will most likely never run this for everyone in the database, but rather using a select list of users.
i.e.
WHERE (UserID IN (id1,id2,id3...))

When I run a report my current VBA code is:
DoCmd.OpenReport rptName, , , SQLqry, , strArguments

the SQLqry code will have something like UserID IN (xxx)
but the query for the report has to run all 760,000 records then filter out those relevant.

Is there a way to actually change the reports SQL using VBA so that it initially draws only those USERID's relevant?

something like MyReport.RecordSource = BLAH BLAH

Thanks!

 

[scriverb]

You can modify the actual SQL of a saved query so that your initial query will perform the select for you and not bring in all of the records.

Dim db as DAO.database
Set db = CurrentDB
db.QueryDefs("qryYourQuery&quot.SQL = Mid$(db.QueryDefs("qryYourQuery&quot.SQL ,1,InStr(1,db.QueryDefs("qryYourQuery&quot.SQL, "WHERE &quot-1) & "WHERE (UserID IN (id1,id2,id3...))"
db.close

Now your query will be updated with the current selection criteria and you do not need to include it in the parameters of the command to open the report.

Is this what you were thinking of doing? You just have to make sure that there is a "WHERE " clause in the initial query so that the code will strip it off and add a new one.


Bob Scriver

 

[DonQuichote]

If there are no other users that can open the report, you could also store the userIds temporarily in a separate table each time you run the query. You can then refer to the table in the IN clause.

Best regards

 

[Trevahaha]

Bob -

Thanks - this is exactly what I need. I'm only worried if multiple people people try to run this at once, but realistically, it won't happen. I gave you a star because it answered exactly what I asked.. and I know I'll be able to use it many many times.

Also thanks about the multiple posting, I wasn't sure if that was customary to post in two places or not - since it could fall into two categories. I'll make sure I don't in the future!

Trevor

 

[scriverb]

Trevahaha- Thanks for the star. It is appreciated.

Your comment about more than one person using the query at a time throws up red flags. You must be using a shared database without it being split into a front-end and back-end. Are you aware of what I mean when I use those terms?

You see a front-end/back-end database design consists of multiple users each having a copy of the front-end application(static tables, queries, forms, reports, modules etc) and there is a back-end database application that has the shared data. This database is stored on the common server location. Each of the front-ends are linked to the data in the back-end through linked table connections. But, the significant part as it pertains to you is the each has their own set of queries. It is loaded onto their PC and there will never be a conflict with other users due to the dynamic changing of the SQL code.

You should look into the FE/BE design technique because it is the accepted and proper design of a shared database system. Search ACCESS forums for other threads to this design designation. Good luck.

Bob Scriver

 

[Trevahaha]

Oh - good point. I just wasn't thinking through. I do have them setup as split front and backends and thus, it won't be an issue. Thanks for catching my "oh duh" statement!

Trevor