MS Access Update Query

Question

Hi Guys.

I need to extract BookID from a table of nearly 90k records, based on the alphabet codes of the books. Extracting the book codes was quite easy. The original records are in the form of ABC 91; XYZ 47, ABC or XYZ being the alphabet code for the book, and the numbers representing the page number references. The Book codes extracted as expected.

But I have not been able to update the BookID field:

UPDATE conBookRefs1 SET conBookRefs1.BookID = Nz(DLookUp("[BookID]","conBookList","[BookCode]=" & Left([conBookRef],InStr([conBookRef]," ")-1)));

The conBookRefs1 table's BookID has a many-to-one relationship with the BookID field (primary) of the conBookList table. The BookCode field in the conBookList table is also unique like the BookID field so two books cannot have the same code. When I tried using a sub-query (SELECT BookID FROM conBookList WHERE...), I got an error message: Operation must use an Updateable Query. I later read somewhere that Access does not accept a subquery for the update operation. So, I changed it to DLookup.

When I run the update query as outlined above, it comes up with: "You are about to update xxx rows. Once you click YES, you can't undo the command..." I click "Yes", only for this error message to appear: "Microsoft Access can't update all the records in the update query. Microsoft Access didn't update xxx field(s), 0 record(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations and 0 record(s) due to validation rule violations....

I don't understand what is going on

Answer 1

The code looks OK, but I would do some things differently

Dim DB As Database

Dim rsBookRef As Recordset

Dim strBookCode As String

Dim lngBookID as Long

Set DB = CurrentDb()

Set rsBookRef = DB.OpenRecordset("conBookRefs", dbOpenDynaset)

    rsBookRef.MoveFirst

    While (Not rsBookRef.EOF)

            With rsBookRef

                    .Edit

                    strBookCode = Left(!BookCode,InStr([!BookCode]," ")-1)

                    lngBookID = Nz(DLookup("[BookID]", "conBookList", "[BookCode]= " & strBookCode,"")

                   !BookID = lngBookID

                    .Update

            End With

        rsBookRef.MoveNext

    Wend

    MsgBox "Update completed."

Then I would step through the code nd check the value of strBookCode and lngBookID before the Update.

Answer 2

Scott. Maybe I misread the original post, but I had the impression this was an attempt to add the BookID to the child table after the fact. Re-reading it, I might be wrong.

Answer 3

Hi Glint, I'm an independent adviser and will try to help.

My first question here is why? Why would you be updating the BookID field in a child table. From your description, that BookID field is a foreign key that relates the child record back to the conBookList table. So the reason to change the foreign key is a big question.

My second question is whether referential integrity is imposed on the relationship.

Third question is have you tried running the Update as a SELECT query to see if the Dlookup returns the correct values?

Answer 4

Your NZ() is trying to set the value of conBookRefs1.BookID to -1 when the DLookup does not return a valid BookID  from conBookList.

Is that a valid value for conBookRefs1.BookID?

If there are MULTIPLE records in conBookList which return -1 because there is no matching BookID from the DLookup, is it valid to use that same -1 value for multiple records in conBookRefs1?