SQL Server and Datasheet View Autofilter

[Porisius]

All,

I just recently discovered and learned a bit about Access and a MS SQL Server 2008 R2. Primary Keys and Indexes, yeah... I learned that I need to be more stringent on those. There have been some learning pains, but hey, no pain no gain. Well, I got a pain that I think I better call the calvary for... Haven't found much on Google to assist me this time, unless I misworded or am blind.

I have a Non-Conformance Logging accdb front-end that I had written and that had an Access accdb backend, (Excuse complete lack of understanding of SQL Server at that time). Recently, I migrated the tables all up to our SQL Server, (that I so happened to have the pleasure to learning how to install), and for the most part, everything worked.

The main form, frmMain, is a split view form, half form and half datasheet. Before the migration, the Autofilter allowed them to Select All, Blanks, etc... Since the data has been migrated, that functionally has been lost. They can still sort, but they can't get crazy with the way they like to filter/sort things. The query sql script (which just has WeekNum and Target Date expressions added) and the linked SQL ODBC Connection, in a straight up datasheet view, show the same.

Now, if I convert the table back to a local table, I regain the autofilter ability in all 3 (Table, Query SQL Script, Form). Is there something in the SQL Server I am missing?

Thanks in advance,
Chris

 

[mdlueck]

I had never heard of that feature, however it appears to be supported by SQL Server 2008 R2:

"AutoFilter Menu Overview"
http://technet.microsoft.com/en-us/library/gg413461(v=sql.105).aspx

 

[Porisius]

Thanks for the reply, and I probably should have been more clear.

It's the Field List that is not showing up, with only 500+ records and the fact it works locally, there should be no issues. I'm not even getting the "Too many items to show."

Thanks,
Chris

 

[AccessMSSQL]

@mdluek: the link you provided is related to autofilter in PowerPivot not sure if it applies the same way in Access.

Chris: It might be related to the fact that SQL Server syntax for filtering could be a bit different than Access. Not sure - would have to see the data you are filtering. Example, Access uses asterik as wildcard, SQL users percent sign. Your blank data might be converted differently in SQL Server. It might have leading or trailing spaces or empty strings so it's not evaluating as NULL or Blank. These are just guesses at this point. Would have to do a test myself. Are you using Access 2010?

 

[Porisius]

AccessMSSQL: Okay, that's new to me... I know I use a lot of *s in my work. I'll have to keep the % in the back of my head if I get further in SQL.

I looked at the data through the Microsoft SQL Server Management Studio and there are no spaces before or after the data in the non-null fields and NULL on the others. What does that mean to my problem? Honestly, I don't have a clue.

Yes, I am using MS Access 2010.

 

[Porisius]

Out of curiousity, would it matter if I am using a DAO, ADO, or ODBC (currently using) to connect the front-end to the SQL Server?

 

[Porisius]

Okay, scratch what I said there... This was a simple fix, and I do mean simple. The source is here, but in Access 2010, go to File > Options > Current Database, and check the ODBC fields in the Filter lookup options.

Thanks to you both, mdluek got me thinking a different way, and AccessMSSQL, you still provided valuable information that very may well come useful in the future.

Source: http://stackoverflow.com/questions/...x-list-filters-missing-on-sql-server-back-end