Filtering a table does not work

[robrfos]

I'm trying to help someone out who is using tables linked to a sequel
server database in Access. There are multiple tables set up the exact
same way that are acting differently in Access. On two of the tables,
when she tries to filter a text field by a selection, it does not
filter anything. It does the same thing when trying to enter a
criteria in a query on the table. It yields all the results. But, in
other tables with the same text field the filtering works great.

So far I've tried a compact and repair which did nothing and I'm at a
complete loss of what else to try. Any suggestions?

 

[Minton M]

I'm trying to help someone out who is using tables linked to a sequel
server database in Access. There are multiple tables set up the exact
same way that are acting differently in Access. On two of the tables,
when she tries to filter a text field by a selection, it does not
filter anything. It does the same thing when trying to enter a
criteria in a query on the table. It yields all the results. But, in
other tables with the same text field the filtering works great.

So far I've tried a compact and repair which did nothing and I'm at a
complete loss of what else to try. Any suggestions?

I've had this problem before when linking to Sybase SQL Server. When
you filter, Access builds and runs a SQL statement against the server
and can screw this up depending on factors such as your ODBC driver
versions. I would recommend turning on SQL logging to check what is
actually being sent over the wire, and also checking the driver
versions you have (and that vendor's support site). If this doesn't
work, the next answer is to build your own filtering form so that
you're in control of the SQL query being sent.

So, in summary, I'd do this in the following order:
1) Turn on SQL logging.
2) Check driver versions.
3) Build custom filtering form so your weekend stays intact.

I hope this helps!
James

 

[robrfos]

I've had this problem before when linking to Sybase SQL Server. When
you filter, Access builds and runs a SQL statement against the server
and can screw this up depending on factors such as your ODBC driver
versions. I would recommend turning on SQL logging to check what is
actually being sent over the wire, and also checking the driver
versions you have (and that vendor's support site). If this doesn't
work, the next answer is to build your own filtering form so that
you're in control of the SQL query being sent.

So, in summary, I'd do this in the following order:
1) Turn on SQL logging.
2) Check driver versions.
3) Build custom filtering form so your weekend stays intact.

I hope this helps!
James

I tried number 2, she already had the latest drivers...

Are 1 and 3 handled in SQL? I haven't used SQL before and I am trying
to see if I can fix it from the Access side, if it is an Access
problem. Are you suggesting its an issue with the SQL Database?

If so, how do you turn on logging and how would I build custom
filtering?

 

[Minton M]

I tried number 2, she already had the latest drivers...

Are 1 and 3 handled in SQL? I haven't used SQL before and I am trying
to see if I can fix it from the Access side, if it is an Access
problem. Are you suggesting its an issue with the SQL Database?

If so, how do you turn on logging and how would I build custom
filtering?

(1) is handled by Access and the drivers, but basically a SQL
statement gets built to perform the filtering. If you're not familiar
with SQL, it probably isn't worth debugging any further and just
assume there's something not working properly in this layer.

To build your own custom filtering, check out this link:
http://support.microsoft.com/defaul...port/kb/articles/Q262/0/99.asp&NoWebContent=1.
It's relatively straightforward to have a control in the header
section of a form that filters records based upon user input. The good
news is that you won't run into this kind of problem if you do this.

 

[robrfos]

Thanks a lot, you have been very helpful!

Wish me luck!

 

[Keith Wilby]

Thanks a lot, you have been very helpful!

Wish me luck!

Have you considered using pass-through queries?

Keith.
www.keithwilby.com