Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Question Unanswered: form bound to queries with joins

    Dear users,
    I need help to troubleshoot an issue that I believe typical of databases, but I can’t find any answer either n books or on websites.
    Mine is a client/server access application. I use forms connected to ADO recordsets and the instruction set I use to bind the formi s the following:
    Dim cn As ADODB.Connection
    Dim rsMain As ADODB.Recordset

    Set cn = CurrentProject.AccessConnection
    Set rsMain = New ADODB.Recordset
    With rsMain
    Set .ActiveConnection = cn
    .CursorLocation = adUseClient
    .Source = "exec " & QueryConnection
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    End With
    Set Me.Recordset = rsMain
    Set Me![ThisSubForm].Form.Recordset = rsMain
    Set rsMain = Nothing
    Everything’s ok until the records to be showed in the form doesn’t need any join to other tables. The typical example is the following:
    1) a table containing products. Every product has a unique ID (counter), a customer code and a description.
    2) a table containing for example the data of invoices.
    3) a table tblDDTInvoiceItems that realizes a many-to-many relationship, with the list of items sent with the invoices, and that contains the following fields:
    IDRow (counter, primary key)
    IDInvoice
    IDItem (the id of the sent item)
    Sequence
    Quantity

    As you can see the table tblDDTInvoiceItems contains just the item ID which is not meaningful to the user who expects company code and description.
    To do so I create a connection query with joins to the item table and I get the recordset I want.

    THE PROBLEM IS THAT SUCH A RECORDSET IS NOT UPDATABLE THROUGH THE FORM.

    So I need to find another way but I don’t know how. I tried with disconnected recordsets and batch update but when I save the modifications I get back an error message like “too little information about the index blah blah to update the table”. It means that it can’t go back through the joins (the update batch works fine with queries with no join).
    When instead I go to a new record in the disconnected recordset the textboxes of the form are locked like when you set the property Allowadditions to false.

    Since I truely believe these are standard circumstances in the development of databases I am sure that a solution exists, yet I can’t find it.
    I will be grateful to anyone for any help he/she can give me.
    Have a nice day,
    Jacopo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a recordset using a join will not be updateable unless the query parser has all the primary keys for all the tables contaning rows to be updated.

    you may know that a column included in your query is a foreign key to another table.. but the SQL parser doesn't know that. so if you want to use a JOINed query as the source to an updateable query then make sure ALL the primary keys are included in the select.

    be very carefull that you don't inadvertantly update the parent table column when you intended to update the child table column(s)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    I followed your advice and included in the form all the primary keys of all the tables. No luck.
    It tells me something like:
    "Impossible to identify the correct row for update".
    Sorry if i am not precise about the error message but I use the italian version of Access do the language of messages is consistent.

    Maybe I am doing something wrong.
    This is the sql of the query:

    PARAMETERS IDP Long;
    SELECT tblDODDTUscitaArticoli.IDRiga, tblDODDTUscita.IDDDTUscita, tblDODDTUscitaArticoli.IDPadre, tblDODDTUscitaArticoli.Posizione, tblPPArticoli.IDArticolo, tblDODDTUscitaArticoli.IDCodiceParte, tblPPArticoli.Codice, tblPPArticoli.Descrizione, tblDODDTUscitaArticoli.Quantita
    FROM tblPPArticoli INNER JOIN (tblDODDTUscita INNER JOIN tblDODDTUscitaArticoli ON tblDODDTUscita.IDDDTUscita = tblDODDTUscitaArticoli.IDPadre) ON tblPPArticoli.IDArticolo = tblDODDTUscitaArticoli.IDCodiceParte
    WHERE (((tblDODDTUscitaArticoli.IDPadre)=[IDP]));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •