Access 2007 query (large IN statement), alternate approaches
in [General]
|
From: Mark Andrews on 4 May 2010 18:53 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: Tom van Stiphout on 4 May 2010 22:45 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: a a r o n . k e m p f on 5 May 2010 08:09 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
From: Mark Andrews on 5 May 2010 08:34 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 08:35
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.kempf(a)gmail.com> wrote in message news: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 > |