dooner1
asked on
Dynaset and Snapshot show different data?!?
I have a query that is a dynaset. When I run it it returns the wrong information, i.e. I filtered for a field = value, but it returns extra values for that field! The recordcount is right with the dynaset. When I run the snapshot everything is visably fine on the datasheet. When I use VBA to TransferSpreadsheet with the dynaset I get the right results also. What's going on that the dynaset datasheet view is inaccurate? I've been working with Access for a number of years, but today it has me stumped. Could it be in the ODBC settings with SQL Server 7.0?
Many thanks in advance!
Many thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dooner,
Long time no speak.
Try using a parameter based query instead of filtering the recordset , I have had similar experiences and that definitley fixes it. If you don't want to use parameter queries try joining the underlying query to a temp table that stores the "value" you are filtering on. therefore if you change the value in the temp table the recordset displayed will change. make sure you do a requery on the form object.
steve
Long time no speak.
Try using a parameter based query instead of filtering the recordset , I have had similar experiences and that definitley fixes it. If you don't want to use parameter queries try joining the underlying query to a temp table that stores the "value" you are filtering on. therefore if you change the value in the temp table the recordset displayed will change. make sure you do a requery on the form object.
steve
wow. Talk about jumping the gun with an answer. :o)
dooner1, I'm with everyone else (except for rauchs): can you give us more information? Is this actually a query that you're running -- and if so, is it straight from the database window or through a macro or code? Or are you creating a recordset behind a form and passing different recordset options like dynaset and snapshot?
dooner1, I'm with everyone else (except for rauchs): can you give us more information? Is this actually a query that you're running -- and if so, is it straight from the database window or through a macro or code? Or are you creating a recordset behind a form and passing different recordset options like dynaset and snapshot?
ASKER
Thanks Steve for the comment, but Trygve hit it; I needed to relink the tables. I had added a field to the SQL table, and the access link didn't know about it and thus gave me unexpected results.
Thanks all, and Trygve should propose the answer
Thanks all, and Trygve should propose the answer
Thank you dooner1!
When you change your linked tables you need to relink them. This is because Access makes a "map" of the table when you link it holding the fields, indexes etc. It then uses this map when connecting to the table later on and if the map does not correspond with the terrain, then Access gets totally lost.
Normally, fields added to the end of the field list will not have greater effect than not appearing till you relink. Fields added somewhere before that will most likely result in a "defect" table.
If you have created Views on the table (Server stored "queries"). Then these not only needs to be relinked, but you will also need to open them on the server and then save them. This is to let the Server update its "map".
1) Save
2) Relink.
If you have views based on other views etc. then you will need to start with the inner most views and work your way out.
When you change your linked tables you need to relink them. This is because Access makes a "map" of the table when you link it holding the fields, indexes etc. It then uses this map when connecting to the table later on and if the map does not correspond with the terrain, then Access gets totally lost.
Normally, fields added to the end of the field list will not have greater effect than not appearing till you relink. Fields added somewhere before that will most likely result in a "defect" table.
If you have created Views on the table (Server stored "queries"). Then these not only needs to be relinked, but you will also need to open them on the server and then save them. This is to let the Server update its "map".
1) Save
2) Relink.
If you have views based on other views etc. then you will need to start with the inner most views and work your way out.
Hi dooner1,
Any update on this question?
in an effort to clean up old open questions
your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response. This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> You cannot delete a question with comments, special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below, include the question
QID/link. https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
------------> EXPERTS: Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.
:O)Bruintje
Any update on this question?
in an effort to clean up old open questions
your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response. This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information
outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> You cannot delete a question with comments, special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below, include the question
QID/link. https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
------------> EXPERTS: Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.
:O)Bruintje
Per recommendation, force-accepted by
Netminder
CS Moderator
Netminder
CS Moderator
Thanks.
Brent