Access 2007 query (large IN statement), alternate approaches

[Mark Andrews]

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

 

[Tom van Stiphout]

On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews"

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

 

[a a r o n . k e m p f]

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

 

[Mark Andrews]

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

On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews"

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

 

[Mark Andrews]

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

message

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

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

 

[Tom van Stiphout]

On Wed, 5 May 2010 08:34:03 -0400, "Mark Andrews"

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

On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews"

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

 

[Mark Andrews]

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

On Wed, 5 May 2010 08:34:03 -0400, "Mark Andrews"

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

On Tue, 4 May 2010 18:53:29 -0400, "Mark Andrews"

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

 

[Tony Toews [MVP]]

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/

 

[a a r o n . k e m p f]

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

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

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

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