Access 2007 query (large IN statement), alternate approaches
in [General]
|
From: Tom van Stiphout on 5 May 2010 10:09 On Wed, 5 May 2010 08:34:03 -0400, "Mark Andrews" <mandrews___NOSPAM___(a)rptsoftware.com> wrote: 4000 rows * 4 bytes = 16K so the bloat will be manageable. But if you are very concerned about that, you can keep this table in an external database, and even compact that db occasionally. Indeed it's not very fast, but better than a crash. Yes, I have thought about grabbing the actual filter clause as well, but in the general case this is hard to do, since Access uses temporary objects to assist with this: the ~sq_XXX queries you sometimes see in a database that has crashed. -Tom. Microsoft Access MVP >Tom, > >So temp table instead of temp query, add records to table instead of build >IN clause in query. >Interesting, I'm on the fence on whether I like that one better or not? > >Pros: end query probably runs faster >Cons: front-end database will grow, adding 4000 records to table might be >slower than building a query with a big WHERE clause. > >Concept is very similar. > >I guess I was thinking maybe there is a way to build the query using the >actual filter that is on the datasheet (and deal with the comboboxes in some >way). > >The current process I have to build the query does work fine, only if I try >to look at the query that was built do I get that error. > >Thanks for the feedback, >Mark > > >"Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message >news:tql1u51kndvm6geqbp06p4lrkc6rndtfis(a)4ax.com... >> On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews" >> <mandrews___NOSPAM___(a)rptsoftware.com> wrote: >> >> Yes I have tackled this one. >> Btw, if you can repeat this crash in A2010, report it to MSFT, >> especially if you can repeat it with a sample db like Northwind - >> maybe they'll fix it. It's a bit embarrasing to have this internal >> error come up. >> >> I have an extra table IN THE FRONT-END with just one field: PKValue. >> Typically it's a long integer because most PKs are long int. I write a >> delete query to get rid of all the records. Then I add all records in >> the selection. Note that this also works great if someone uses >> QueryByForm and creates a really complicated selection. To add the >> records, write some VBA code like this (off the cuff, excuse typos): >> dim rs as dao.recordset >> dim rsPK as dao.recordset >> set rs=me.recordsetclone >> set rsPK = currentdb.openrecordset("tblPK", dbOpenTable) >> if rs.recordcount > 0 then >> rs.movefirst >> do while not rs.eof >> rsPK.addnew >> rsPK!PKValue = rs!myPKField >> rsPK.update >> rs.movenext >> loop >> end if >> rs.close >> rsto.close >> >> Then I inner-join this table with the report query. For example if the >> report was about all customers the new query would be: >> select * from Customers >> inner join tblPK on Customers.CustomerID = tblPK.PKValue >> This would restrict the customers to the ones in the PK table. >> >> -Tom. >> Microsoft Access MVP >> >> >>>I have an Access 2007 database where I let users use the built-in >>>filtering >>>from the datasheet portion of a split screen form. >>> >>>Example: The user might have 10,000 accounts and they filter the list to >>>show 4,000 from 5 different cities. >>> >>>I give the user and option to for example print labels for these 4,000 >>>filtered accounts. >>> >>>My first approach was as follows: >>>- build a where clause string as a giant IN clause for all 4,000 >>>- pass this to the report as a where clause using open report >>> >>>works fine until the where clause gets to be too many characters then >>>fails. >>> >>>Second and current approach is as follows: >>>- build a query via code (querydef etc..) that returns accountid and uses >>>a >>>giant IN clause >>>- join this query to the query which drives the report >>>- NO where clause is passed in on opening of the report >>> >>>works fine. The only weird behavior is if I try and open the "qryFilter" >>>in >>>design mode Access 2007 gives an error: >>>Assertion failed line 49 of safeops.cpp >>>So basically crashing the Access program itself >>>Also might be a tab slow (due to the giant IN clause). >>> >>> >>>My issue with building the filter for the report (based on the filter >>>specified on the datasheet) is if you use combo boxes >>>in the datasheet with the first column hidden (such as a normal combo for >>>a >>>parent record (ID, ParentName)) and then look at the filter it >>>kind of makes up it's own sql "queryname.[visible field in combo] (event >>>though that field is not in the query itself). >>>So for my initial example I am making labels for all accounts in 5 >>>different >>>states and the filtering criteria is simple and changing it to >>>an IN with 4000 account ids doesn't seem to be the right solution. >>>However >>>I might have 7 comboboxes that really represent >>>additional tables that are related and I didn't want to have to write a >>>bunch of code to transform the filter (especially if I do this on multiple >>>datasheets). >>> >>>Note: My datasheet can also be used for entering data so I need to keep >>>the >>>comboboxes etc.... >>> >>> >>>Question is what is the right approach to allow users to do things like >>>make >>>reports off a filtered list of items >>>While Relying on the built-in Access datasheet filtering options? >>> >>>Hoping someone has tackled this one. >>> >>>Thanks, >>>Mark >>> >>> >>> >>>
From: Mark Andrews on 5 May 2010 16:48 I was thinking more along the lines of grabbing the Filter text from the datasheet and massaging it with code to work with this new filter query that is being built. Also the current process works fine for the user, crash is only if I try and view the query in design view. The query runs fine. 4000 rows done 10 times a day every day could add up. I hate resorting to temp tables! I'm not sure what you are referring to when you mention the temp objects? I thought the filter property on the form would always indicate how the datasheet is being filtered, only comboboxes cause complexity. So in theory vba code could be used to transform a filter on a datasheet into a where clause on a query if you deal with the combobox complexity right? Perhaps a mapping table used to replace text. Example: qryContactList is used for datasheet - filter on Lastname = "Smith" and - filter on CompanyName = "ACME Corp" Filter is: qryContactList.Lastname = "Smith" AND qryContactList.CompanyName = "ACME CORP" New qryFilter uses tblContact joined with tblCompany by CompanyID Select tblContact.ContactID FROM tblContact Left Joined with tblCompany .... WHERE (tblContact.LastName = "Smith") AND (tblCompany.CompanyName = "ACME CORP") Instead of: Select tblContact.ContactID FROM tblContact WHERE tblContact.ContactID IN (3,7,12,45,56,78,98,.... 4000 in total) See any flaws other than it's more work than building the IN clause by looping through the records. Thanks, Mark "Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message news:9qu2u516stt75m1a7trrerd9rke48q53ar(a)4ax.com... > On Wed, 5 May 2010 08:34:03 -0400, "Mark Andrews" > <mandrews___NOSPAM___(a)rptsoftware.com> wrote: > > 4000 rows * 4 bytes = 16K so the bloat will be manageable. But if you > are very concerned about that, you can keep this table in an external > database, and even compact that db occasionally. Indeed it's not very > fast, but better than a crash. > Yes, I have thought about grabbing the actual filter clause as well, > but in the general case this is hard to do, since Access uses > temporary objects to assist with this: the ~sq_XXX queries you > sometimes see in a database that has crashed. > > -Tom. > Microsoft Access MVP > > >>Tom, >> >>So temp table instead of temp query, add records to table instead of build >>IN clause in query. >>Interesting, I'm on the fence on whether I like that one better or not? >> >>Pros: end query probably runs faster >>Cons: front-end database will grow, adding 4000 records to table might be >>slower than building a query with a big WHERE clause. >> >>Concept is very similar. >> >>I guess I was thinking maybe there is a way to build the query using the >>actual filter that is on the datasheet (and deal with the comboboxes in >>some >>way). >> >>The current process I have to build the query does work fine, only if I >>try >>to look at the query that was built do I get that error. >> >>Thanks for the feedback, >>Mark >> >> >>"Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message >>news:tql1u51kndvm6geqbp06p4lrkc6rndtfis(a)4ax.com... >>> On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews" >>> <mandrews___NOSPAM___(a)rptsoftware.com> wrote: >>> >>> Yes I have tackled this one. >>> Btw, if you can repeat this crash in A2010, report it to MSFT, >>> especially if you can repeat it with a sample db like Northwind - >>> maybe they'll fix it. It's a bit embarrasing to have this internal >>> error come up. >>> >>> I have an extra table IN THE FRONT-END with just one field: PKValue. >>> Typically it's a long integer because most PKs are long int. I write a >>> delete query to get rid of all the records. Then I add all records in >>> the selection. Note that this also works great if someone uses >>> QueryByForm and creates a really complicated selection. To add the >>> records, write some VBA code like this (off the cuff, excuse typos): >>> dim rs as dao.recordset >>> dim rsPK as dao.recordset >>> set rs=me.recordsetclone >>> set rsPK = currentdb.openrecordset("tblPK", dbOpenTable) >>> if rs.recordcount > 0 then >>> rs.movefirst >>> do while not rs.eof >>> rsPK.addnew >>> rsPK!PKValue = rs!myPKField >>> rsPK.update >>> rs.movenext >>> loop >>> end if >>> rs.close >>> rsto.close >>> >>> Then I inner-join this table with the report query. For example if the >>> report was about all customers the new query would be: >>> select * from Customers >>> inner join tblPK on Customers.CustomerID = tblPK.PKValue >>> This would restrict the customers to the ones in the PK table. >>> >>> -Tom. >>> Microsoft Access MVP >>> >>> >>>>I have an Access 2007 database where I let users use the built-in >>>>filtering >>>>from the datasheet portion of a split screen form. >>>> >>>>Example: The user might have 10,000 accounts and they filter the list to >>>>show 4,000 from 5 different cities. >>>> >>>>I give the user and option to for example print labels for these 4,000 >>>>filtered accounts. >>>> >>>>My first approach was as follows: >>>>- build a where clause string as a giant IN clause for all 4,000 >>>>- pass this to the report as a where clause using open report >>>> >>>>works fine until the where clause gets to be too many characters then >>>>fails. >>>> >>>>Second and current approach is as follows: >>>>- build a query via code (querydef etc..) that returns accountid and >>>>uses >>>>a >>>>giant IN clause >>>>- join this query to the query which drives the report >>>>- NO where clause is passed in on opening of the report >>>> >>>>works fine. The only weird behavior is if I try and open the >>>>"qryFilter" >>>>in >>>>design mode Access 2007 gives an error: >>>>Assertion failed line 49 of safeops.cpp >>>>So basically crashing the Access program itself >>>>Also might be a tab slow (due to the giant IN clause). >>>> >>>> >>>>My issue with building the filter for the report (based on the filter >>>>specified on the datasheet) is if you use combo boxes >>>>in the datasheet with the first column hidden (such as a normal combo >>>>for >>>>a >>>>parent record (ID, ParentName)) and then look at the filter it >>>>kind of makes up it's own sql "queryname.[visible field in combo] (event >>>>though that field is not in the query itself). >>>>So for my initial example I am making labels for all accounts in 5 >>>>different >>>>states and the filtering criteria is simple and changing it to >>>>an IN with 4000 account ids doesn't seem to be the right solution. >>>>However >>>>I might have 7 comboboxes that really represent >>>>additional tables that are related and I didn't want to have to write a >>>>bunch of code to transform the filter (especially if I do this on >>>>multiple >>>>datasheets). >>>> >>>>Note: My datasheet can also be used for entering data so I need to keep >>>>the >>>>comboboxes etc.... >>>> >>>> >>>>Question is what is the right approach to allow users to do things like >>>>make >>>>reports off a filtered list of items >>>>While Relying on the built-in Access datasheet filtering options? >>>> >>>>Hoping someone has tackled this one. >>>> >>>>Thanks, >>>>Mark >>>> >>>> >>>> >>>>
From: Tony Toews [MVP] on 5 May 2010 21:24 "Mark Andrews" <mandrews___NOSPAM___(a)rptsoftware.com> wrote: >Yes I know SQL Server is great. However I need to use Access for this one. >It's an app that gets installed on lots of users computers and they do not >have sql server. Please ignore Aaron Kempf's posting as Aaron's answer to just about every question is SQL Server and ADPs. No matter how appropriate his response. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/
From: a a r o n . k e m p f on 6 May 2010 13:03
Access Data Projects don't require SQL to be installed on desktops.. just on a single server with ADP, all your performance / stability problems go out the window On May 5, 5:35 am, "Mark Andrews" <mandrews___NOSPAM...(a)rptsoftware.com> wrote: > Yes I know SQL Server is great. However I need to use Access for this one. > It's an app that gets installed on lots of users computers and they do not > have sql server. > > Mark > > "a a r o n . k e m p f @ g m a i l . c o m" <aaron.ke...(a)gmail.com> wrote in > messagenews:cb1dfec6-ea98-499f-973d-42c1a7688d86(a)v12g2000prb.googlegroups..com... > > > large IN clauses work great for me when I use SQL Server > > > perhaps Jet isn't the right answer for you? > > > I'd just try moving everything to ADP to see if your crash magically > > goes away.. I'm pretty sure that SQL Server supports SQL statements > > that are one heck of a lot more complex than Jet does > > > On May 4, 3:53 pm, "Mark Andrews" > > <mandrews___NOSPAM...(a)rptsoftware.com> wrote: > >> I have an Access 2007 database where I let users use the built-in > >> filtering > >> from the datasheet portion of a split screen form. > > >> Example: The user might have 10,000 accounts and they filter the list to > >> show 4,000 from 5 different cities. > > >> I give the user and option to for example print labels for these 4,000 > >> filtered accounts. > > >> My first approach was as follows: > >> - build a where clause string as a giant IN clause for all 4,000 > >> - pass this to the report as a where clause using open report > > >> works fine until the where clause gets to be too many characters then > >> fails. > > >> Second and current approach is as follows: > >> - build a query via code (querydef etc..) that returns accountid and uses > >> a > >> giant IN clause > >> - join this query to the query which drives the report > >> - NO where clause is passed in on opening of the report > > >> works fine. The only weird behavior is if I try and open the "qryFilter" > >> in > >> design mode Access 2007 gives an error: > >> Assertion failed line 49 of safeops.cpp > >> So basically crashing the Access program itself > >> Also might be a tab slow (due to the giant IN clause). > > >> My issue with building the filter for the report (based on the filter > >> specified on the datasheet) is if you use combo boxes > >> in the datasheet with the first column hidden (such as a normal combo for > >> a > >> parent record (ID, ParentName)) and then look at the filter it > >> kind of makes up it's own sql "queryname.[visible field in combo] (event > >> though that field is not in the query itself). > >> So for my initial example I am making labels for all accounts in 5 > >> different > >> states and the filtering criteria is simple and changing it to > >> an IN with 4000 account ids doesn't seem to be the right solution. > >> However > >> I might have 7 comboboxes that really represent > >> additional tables that are related and I didn't want to have to write a > >> bunch of code to transform the filter (especially if I do this on > >> multiple > >> datasheets). > > >> Note: My datasheet can also be used for entering data so I need to keep > >> the > >> comboboxes etc.... > > >> Question is what is the right approach to allow users to do things like > >> make > >> reports off a filtered list of items > >> While Relying on the built-in Access datasheet filtering options? > > >> Hoping someone has tackled this one. > > >> Thanks, > >> Mark |