SQL Views show records, As Access linked tables they are EMPTY

I have an Access front end with linked tables and views to a SQL backend.

I'm trying to narrow the record set but "filtering" it with views.  Records have an "Active" or "Inactive" (or "Deleted") status and I have a form that gives me the option to view view Active, Inactive, or Deleted and upon selection, changes the form's record source to the appropriate SQL view.

If I open the views in SQL Management Studio, I have data.

If I open them in Access, they are empty.

I've refreshed the table links.

What am I missing?

(Access 2010 and 2013)
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Check if the bit flag on the column of view or table in sql server and chage it to accept null value.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Thanks for responding, but I'm not familiar with that setting location - can you post a screen shot of where I might find it?
0
Deepak ChauhanSQL Server DBACommented:
I mean to say check the SQL table or view if there is a bit flag clumn for the table which you are trying to open it in access. If bit flag column having null value replce all the null value by 0 (Zero). and set the default value 0 for this column.

BITValue-ACCDB.JPG
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Ah yes, that bugger!  I don't think I have a bit value of 0 (when I show "all records" it's fine, but let me double check.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Unfortunately, that did not solve the issue.  There WERE 4 bit fields with that on some records had null values.  I did an update to set default values on all and then a query to ensure there were no null bit fields left and the Access linked table/view is still empty while the view in SQL is still populated.
0
Deepak ChauhanSQL Server DBACommented:
Have you refreshed the linked table in access.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Yes, I did one better and dropped the linked table and reconnected it.  I also just refreshed it for giggles.

I know sometimes one can doubt you're getting all the facts, so here's a screen shot of the view in question and the query I ran to confirm no bit value is null.

View Field List
Query
0
Deepak ChauhanSQL Server DBACommented:
ok run the query like this

select * from activecustomers where deleted not in (0,1)
or primaryemailoutput not in (0,1) or so on (all the bit field)

and I guess you are using .accdb file.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post the Access query as well the SQL query?
Just want to see if there's any difference between them.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
then a query to ensure there were no null bit fields left
Your Access query explicitly looks for NULL values in the Bit fields, so resetting those to non-null values would result in no records being returned.

Try with this:

SELECT * FROM ActiveCustomers

And see what that gets you. From there, add your filtering options. For example, if you want records where Deleted is False:

SELECT * FROM ActiveCustomer WHERE Deleted=False

Or

SELECT * FROM ActiveCustomer WHERE Deleted=0
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Scott, I think you're misreading.

The query I posted is from SQL Management Studio and only used to confirm that no bit fields in the database have null values.  There is currently no Access query - I'm simply trying to open the linked table (view) in Access - when doing that I get no records.  When I open the view in Access, I get all records I expect.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Absent minded professor anyone?  

I had the ODBC connection pointing to the wrong server.  The data in that server WAS empty.

Sheesh.  Thanks for trying folks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Problem in chair, not in computer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.