Thread: form bound to queries with joins
08-01-07, 06:02 #1Registered User
- Join Date
- Aug 2007
Unanswered: form bound to queries with joins
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
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.Source = "exec " & QueryConnection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
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)
IDItem (the id of the sent item)
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,
08-01-07, 06:30 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
08-01-07, 09:13 #3Registered User
- Join Date
- Aug 2007
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