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!
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.

It usually is something funny about the underlying data source, not Access.  Can you give us more specifics about the query and the data?


Try relinking the tables. Sometime we can get strange results from "bad" links.

I am a bit puzzled by the dynaset/snapshot version of the query. This is normally terminology used when opening recordsets from VBA???

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

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.

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!

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?
dooner1Author Commented:
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

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.
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. http://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. http://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.

Per recommendation, force-accepted by
CS Moderator
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.