Very serious bug in Access with Linked tables?

[NaderC]

Hi, There seem to be a serious bug in Access 97, and I want to know if anyone has encountered this before.
I tried to describe the problem as best as I can, I also included a flash video to describe everything, here it is:
http://naderchehab.googlepages.com/linkedtablebug.swf (~1 MB)


My database has two tables:
- dbo_InventoryLocation which is linking to SQL Server 2000 via ODBC which contains inventory quantities for each product, and
- tblPRODUCTS_BUSINESS which is linking to another access database on the network and contains product information
(item number, title, description, and a field called "InvCategory" which describes item categories). I wrote a very simple query that links these two tables together:

SELECT tblPRODUCTS_BUSINESS.InvCategory, dbo_InventoryLocation.ItemNumber
FROM tblPRODUCTS_BUSINESS INNER JOIN dbo_InventoryLocation
ON tblPRODUCTS_BUSINESS.Item_Number = dbo_InventoryLocation.ItemNumber
GROUP BY tblPRODUCTS_BUSINESS.InvCategory, dbo_InventoryLocation.ItemNumber
ORDER BY tblPRODUCTS_BUSINESS.InvCategory;

The query really doesn't matter, it's just a test. Now here's the weird part:
when I execute this query, and I scroll down to a specific category and count the number of items in that category, I see about 50 of them.
But if I specify that category as a criteria, I see more than 200!

I also noticed that this problem does not happen when I use non-linked tables.

I hope anyone has an idea what that problem might be, and I hope there is a way around it.

Thanks,

Nader

 

[pdx_man]

Not a bug. Design, actually. To optimize performance and minimize network traffic, Access is not going to pull all of the data across for every single request. It is only going to pull pages. Ever notice when you are using the page up/down key going through a table that is cruises along for awhile and then takes a break, then resumes cruising along? Access is requesting more of the recordset during those breaks. Now, when you run a query, depending on the amount of data, same thing. Paging through the results may or may not require the pulling of additional data. If you are doing an action query (aggregate), then you will get results from the entire recordset.

If you think about it, it really makes sense. 85% of the time I am opening a table, it is not to get reliable metrics. I am just popping it open maybe to filter for a specific record. When I open it, I do not want the entire recordset to be transferred to my machine. I work with very large tables with millions of rows. It just doesn't make sense to transfer the entire table for these types of requests.

Try this. Open your table, go to the last record, do your sort, then find and count your records.

 

[NaderC]

Hi pdx_man, thank you for the reply. This makes sense in some ways,
but the problem is, I am generating an Excel report out of this table, and I obviously need all the records to be there. I am using an append query that will take all the records in that query and append them to a table. It seems like it's only appending some of the records, and not all, and therefore the report is wrong. To solve this, I imported the table (instead of linking it) and the result was correct. My question is, do I have to re-import the table every time I need that report?

Thanks again,

Nader

 

[odin1701]

Are you trying to base a report off of an append query?

Why can't you just use a select query, and why do you need to append the results of a query to a table before running a report? You could just run it off of a query.

 

[NaderC]

The append query is needed because I am appending to a table that contains the history of inventory over time. So every week a dated 'snapshot' of inventory is added to that history table. And as I mentioned before, the problem is in that append query - it doesn't append everything, because it's a linked table. I'm wondering if we can override that somehow.

 

[neileg]

I'm a bit supiscious about the action of the Group By clause in the query with no aggregate function. Is the behaviour replicated if you omit the Group By?

 

[NaderC]

Yep, the same thing happens without the Group By. A larger number of records are returned by the query and therefore a larger number is missing from the result.

 

[NaderC]

Something even more confusing: It seems that this happens only for item numbers that start with 'DIGI'. No other records seem to be affected.

 

[odin1701]

What are the data types set to for each of your fields?

 

[NaderC]

For the fields used by the query, everything is Text.
Otherwise all is text except the inventory quantities, which are Number.