Solved

Access query

Posted on 2013-12-02
9
619 Views
Last Modified: 2013-12-04
I have a query in Access 2007 that uses 2 linked SQL Server tables.  Both tables have primary keys.  They are joined by the primary key fields with a left join so the field from table 2 should be blank if there are no matching records.  Instead of being blank, the fields say #Deleted (if there is a match, they show the field contents.)  If I put a sort on the primary key field of Table 1 in Design View, no change.  If, however, I right click on the blank area to bring up the Properties box and I put a sort there of the same field in the Order By and then run the query, the #Deleted goes away and becomes blank.  The sort in Design View shows up in the SQL View, the sort in Properties does not.

I need to know what the sort in Properties is fixing, because I'm putting the query into my VB code and I can't use a Properties sort there.

Any ideas?   Thanks in advance.
0
Comment
Question by:loybob
9 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39693342
I don't have an answer but I wanted to tell you that you are not alone.  I ran into that same situation and haven't figured out why.  I have other right-joins to the same table that work as expected.  But one particular join returns #Deleted#.  I'll try adding a sort to the query to see if that solves the problem.  The #Deleted# field is far enough down the sort order to not actually change the sequence of the details.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39693351
That's a weirdy.
Now, many questions.
Do you have other units running Access?
Does the problem persist on other machines, or just one?
If you set up a query between two different tables, does the same problem occur?
They are joined on the primary key.  Is the primary key AutoNumber (yay!) or text (boo!)

If you create a postable sample by exporting the two tables to a new db (making them local tables) and creating the query, does the same problem manifest?

Finally:
"I need to know what the sort in Properties is fixing, because I'm putting the query into my VB code and I can't use a Properties sort there."

You also won't be SEEING the query in code, so you don't care --so long as the values are what you expect.
So build your recordset in code
dim db as database
dim rs as recordset
dim sql as string

sql = "select * from whatever from whatever left join thatone on whatever.pk = thatone.pk;"
set db = currentdb
set rs = db.opendynaset(sql, dbopendynaset ,dbseechanges)

rs.movelast
rs.movefirst

do until rs.eof
    msgbox nz(whatever.pk, "null")
    rs.movenext
loop

Open in new window


Run the code.  Are nulls being returned when required by the left join?  or some oddball value?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 39693363
try running the query in code to recordset and do not use the dbseechanges as it's telling the engine to track changes, which I don't think you want anyways.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39693378
@jadedata
If you DON'T give it dbSeeChanges and it involves SQL Server linked tables the code bombs.  It's a required element with dbOpenDynaset.
If you don't want to see changes you need to open with a different option (dbOpenSnapshot etc.)

@pathartman, @loybob
What field shows the #DELETED?
Are they text fields (or maybe memo fields!)?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:PatHartman
ID: 39693421
I think I may have resolved my problem.  It was intermittent and I hadn't really spent any time on it since it was a query I was using for myself.  The query was joining an error code (5 character text) to a lookup table to bring back a description (255 character text).  I think the problem was caused because some of the rows had ZLS in the error code when there was no error rather than null.  I just got rid of all the ZLS this morning because they were causing a problem in a report because they were keeping the error line from shrinking when there was no error and I haven't seen #Deleted# for the description since I did that.  I don't know why ZLS would cause such a problem in a right-join or if something else fixed the problem.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39693430
dbSeeChanges is used when a  table in the recordset has an identity column.  This allows Access to "see" the newly assigned identity column if a record is added.  It is not needed when you use a snapshot type recordset because snapshots are not updateable but it is needed when you use a dynaset since even if you have no intention of updating it, the implication is that you could.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39693442
There's a long-in-the-tooth kb article about it here.
http://support.microsoft.com/kb/128809

The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table).

 After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows:
•Having an update or insert trigger on the table, modifying the key value.
•Basing the unique index on a float value.
•Using a fixed-length text field that may be padded on the server with the correct amount of spaces.
•Having a linked ODBC table containing Null values in any of the fields making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed.

 Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted.

The following are some strategies that you can use to avoid this behavior:
•Avoid entering records that are exactly the same except for the unique index.
•Avoid an update that triggers updates of both the unique index and another field.
•Do not use a Float field as a unique index or as part of a unique index because of the inherent rounding problems of this data type.
•Do all the updates and inserts by using SQL pass-through queries so that you know exactly what is sent to the ODBC data source.
•Retrieve records with an SQL pass-through query. An SQL pass-through query is not updateable, and therefore does not cause "#Delete" errors.
•Avoid storing Null values within any field making up the unique index of your linked ODBC table.

So for @PatHartman, the text field and it's values were the proximate cause
0
 

Author Comment

by:loybob
ID: 39693456
I fixed the problem myself - I created a another query from scratch, and it didn't get the #Deleted, and it's joins were in a different order than the first query - same joins but different order.  

What I'd still like to know is where the Order By from the Properties is stored and why is that different than Sort in the Design View which adds an Order By in the SQL View.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 39693481
It is likely stored in the LvProp field of MSysObjects.
The ORDER BY clause in SQL tells the engine how to retrieve the records when the query is run.  The Properties Order by is how just the Query Editor is going to arrange the records you see and is the equivalent of doing Records | Sort in the GUI, but then persisting that.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now