[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

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!
0
dooner1
Asked:
dooner1
1 Solution
 
bknouseCommented:
It usually is something funny about the underlying data source, not Access.  Can you give us more specifics about the query and the data?

Thanks.

Brent
0
 
TrygveCommented:
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???
0
 
rauchsCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
brewdogCommented:
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?
0
 
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

0
 
TrygveCommented:
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.
0
 
bruintjeCommented:
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.

:O)Bruintje
0
 
NetminderCommented:
Per recommendation, force-accepted by
Netminder
CS Moderator
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now