SQL server ecords not showing up (Access XP)

[deekatz]

I created an SQL query which give over 9000 records when run from the SQL server query analyser. But if I run the same query from Ms Access query grid, it is only returning 156 records. I have tweaked the Tools >. Options > Advanced tab to set the default max record to 10000 but this does not seems to have any effect on the number of records pooled from the server. Please help.

[HansV]

Does MSKB article Results do not appear when you run a query that references Linked SQL Server tables apply to your situation?

[deekatz]

I have created an Access project database that is using records stored on the SQL server. On one of the tables, there is more than 300000 records. But when I tried to see the table from Access project, I can only see 5000 records.

[HansV]

I hope someone else can help you, this is outside my field.

[JulesG]

Deekatz,

you haven't actually said if Hans' suggestion was any help. Instead, you've launched into a second question. Sticking to your first question, if the KB article didn't cover your situation, more information is needed. I use Access and SQL Server 2000 together all the time, and there's normally an explanation for such symptoms (quite often the two queries are actually slightly different, or dates are not being formatted correctly, or nulls are handled differently). If you'd like to post the SQL that you are using, I'll see if I can come up with any ideas.

[deekatz]

Sorry. But no, the knowledge base wasn't of any help. I found a solution though. Instead of the creating the query within MS Access project I just created a view of base on the same select statement on the server. This seems to work. Thanks anyway.