Solved

Access 2007 null values in query appear as #Deleted#

Posted on 2011-03-06
13
618 Views
Last Modified: 2013-11-29
In an Access 2007 application that links SQL Server 2000 tables through ODBC I have created the following query:

SELECT OrderItems.ItemQuantity, OrderItems.ItemID, ItemComponents.ComponentQty, ItemComponents.ComponentID
FROM ((Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID) INNER JOIN BatchControl ON (Orders.ShipDate = BatchControl.ProcDate) AND (Orders.DateBatchID = BatchControl.ProcBatchID)) LEFT JOIN ItemComponents ON OrderItems.ItemID = ItemComponents.ItemID;

Some OrderItems do not have components, and one would expect that in rows where that is the case that the values for ComponentID and ComponentQty would be null.  However, they show as #Deleted#.  The foreign key ItemComponents.ItemID is indexed, but not unique.

As a check I created the following simple query to see just Items and their components, and it displayed null values as expected.

SELECT ItemMaster.ItemID, ItemComponents.ComponentQty, ItemComponents.ComponentID
FROM ItemMaster LEFT JOIN ItemComponents ON ItemMaster.ItemID = ItemComponents.ItemID;

It should be mentioned that I'm using Windows 7.  I don't remember ever having this type of problem with XP or Vista, but I also don't know that I ever attempted to do this exact sort of thing.  It seems like I must have at one time or another, but I can't be sure.  

Any help would be appreciated.
0
Comment
Question by:Smart_Systems
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
13 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35051574
21.1.7.3.4: Access reports records as #DELETED# when inserting or updating records in linked tables.

If the inserted or updated records are shown as #DELETED# in the access, then:

If you are using Access 2000, you should get and install the newest (version 2.6 or higher) Microsoft MDAC (Microsoft Data Access Components) from http://support.microsoft.com/kb/110093. This fixes a bug in Access that when you export data to MySQL, the table and column names aren't specified.

You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This fixes some cases where columns are marked as #DELETED# in Access.

For all versions of Access, you should enable the Connector/ODBC Return matching rows option. For Access 2.0, you should additionally enable the Simulate ODBC 1.0 option.

You should have a timestamp in all tables that you want to be able to update.

You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.

Use only DOUBLE float fields. Access fails when comparing with single-precision floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you cannot find or update rows.

If you are using Connector/ODBC to link to a table that has a BIGINT column, the results are displayed as #DELETED. The work around solution is:

Have one more dummy column with TIMESTAMP as the data type.

Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator.

Delete the table link from Access and re-create it.

Old records still display as #DELETED#, but newly added/updated records are displayed properly.
0
 

Author Comment

by:Smart_Systems
ID: 35053369
derekkromm,

Thank you for your answer, but none of the above apply.

It is a select query (i.e. a read operation).
I'm using Access 2007.
There are no bigint or float fields.
Alll tables involved have primary keys.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35057083
The #Deleted# (most of the time) means that the underlying dataset need to be refreshed.

First simply try refreshing the dataset.
Press Shift+F9

See this MS link as well:
http://support.microsoft.com/kb/128809
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Smart_Systems
ID: 35057375
boag2000,

Thanks for the response, but that's not it either.  The data involved is all static.  The issue appears to be with the combination of joins, but I know I've done such things before without this problem.  The only thing that is different now is Windows 7, but I can't imagine what that has to do with it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35059491
Is this the case for all computers on Win7?
Is the Database "Split"?

All the Updates installed for Windows and Office?
0
 

Author Comment

by:Smart_Systems
ID: 35060105
The only computer running on Win 7 is mine.  I did get a chance to run the query on a couple of XP computers and got the same result (i.e. #Deleted# where nulls should appear).

The application is Access 2007 linking SQL Server 2000 tables using ODBC, so yes, it is split.

Windows and Office are current on updates.

Thanks again for your ideas.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
ID: 35061151
Then double check the info in the links derekkromm posted.

Also try importing the data into a static table in Access and see if this issue goes away...
0
 

Author Comment

by:Smart_Systems
ID: 35116190
Sorry about the absence.  I was dealing with an urgent situation.

boag2000,
I have pretty much come to the same conclusion - move the data to a working table in Access, or I can change the structure (require that there be at least one component per item) so the need for the left join is eliminated.  As I mentioned in my initial question, that does work.  I will be at the db's site tomorrow and will test both ideas.  I'll report back.

0
 

Accepted Solution

by:
Smart_Systems earned 0 total points
ID: 35143683
boag2000,

I implemented my solution (change the system to require at least one component per item, thereby eliminating the need for a left join in the process of exploding items into their components) and everything works fine.  However, your idea of moving the data to a local Access table and working with it from there also works.  I think you deserve at least some of the points if not all.  What would be the protocol for awarding points in a situation like this?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35143896
Click the "request Attention" link and explain what you would like to do.

;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35178090
OK, glad I could be of some assistance...

Congratulations on your solution as well...
;-)

Jeff
0
 

Author Closing Comment

by:Smart_Systems
ID: 35213347
I accepted my comment as part of the solution because it is the method I actually used.  The Expert (Boag2000) proposed a method that would work, but I didn't use that method.  

I gave the solution(s) a B grade because it was more of a workaround than a direct solution for the original issue.  If there was a direct solution, I feel confident that one of the Experts would have known about it.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 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