MS ACcess SQL query

Posted on 2009-04-15
Last Modified: 2013-11-28
This is my Sql coding
  SET [newlocation-ID] = DMin("IdLocation","Book1","Address LIKE '" & Address & "*' OR '" & Address  & "' LIKE (Address + '*'")
 EXISTS (Select T2.IdLocation FROM Table2 T2 WHERE (T1.Address LIKE (T2.Address + '*') OR  T2.Address LIKE (T1.Address + '*'))AND T1.PC = T2.PC AND T1.IdLocation <> T2.IdLocation)

Please find the attachement folder for Erors and Table format.

Errors showing i can't update the records. the error in the attachment folder
Question by:lankapala
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Errors similar to the one you are getting are the result of you trying to paste the same key field value into the table more than once.
    This keyfield is either a Primary Key or an indexed field with it's AllowDuplicates property set to No.

    Check you tables and their properties carefully, then check the query string just as carefully.

    A test would be to create a test database with no Primary keys or indexes and then try the code/SQL

    Let's see if another Expert has more insight...

    LVL 74

    Accepted Solution

    Either that or you are trying to add a child record that does not have a corresponding Foreign key value.

    For example: You are trying to add an Order (Child Record) for a customer that does not exist yet in the Customer table.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    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…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    746 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