Subform one-to-many relationship

[Jacopo]

Dear ladies and gents,
I am quite disoriented while trying to do something.
I have developed a database in access, creating only unbound forms that on opening retrieve recordsources of the controls from the tag and connect to the tables through ADO recordsets created (where in editing mode) as keysets with an optimistic lock.
Now the problem i have is with a form like an invoice. The main form binds to the data table containing the information of the invoice itself. Then there should be a subform where you insert the items connected to that specific invoice.

Problem: how to allow the user to edit items in the list?

One condition is that for no reason i would create a subform with parent child connection since this would mean that when you open the invoice form, the subform gets from the remore database all the items connected to all the invoices created since the stone age, and then graciously shows just the ones related to the present invoice (it would take some hours I guess).

So what i did is populating the subform with a parameter query. But the point is that a subform populated with a parameter query doesn't accept any update (at least this is what happens to me).

I have a workaround which is creating an interface that let the user believe he is working on the data of the subform but in reality the program, when the user presses update, goes back to the back end database, deletes the old row and insert a new one with the new/corrected data (no matter if the user just changed the quantity for an already present item).

Am I too far from the correct way to approach the problem?
I will be grateful for any piece of advice. Thank you
Jacopo

[nico5038]

I seldomly use unbound forms, as Access does quite a good job in preventing concurrent update and the chance of two users accessing the same data in general is very small.
The Mainform/subform when linked is effectively performing your parameterized query already by collecting only the invoicelines of the mainform's invoice :-)

In your case I would probably create a temp table that's filled with the rows linked to the mainform's image. When the user presses [Save] a delete * from tblInvoice where InvoiceID = Mainform.InvoiceID and an insert of the table can be used.
For a multiple user situation you'll need to add the UserName to the unique ID of this temp table. You'llalso need to check or there has been no other user that has updated the Invoive and/or lines before executing this operation.
Getting the idea ?
Guess you understand now why I try to leave this to Access :-)

Nic;o)

[Jacopo]

Dear Nico,
thank you very much for your kind and competent reply.
I understand what you mean and you are right, if Access can take care of all the concurrency problems is quite better.
You are giving me a precious information that I didn't find on any of the Access books I have or on any website I visited so I want to make sure I got it right because it is crucial.

What I thought it was the process related to filling a subform:
1)The front-end application asks to the back-end database all the records of the many side table, in this case the items related to the invoices.
2)Once received ALL the records of all the invoices (several KB, maybe MB of info) the front-end form filters them according to the main form ID.

What instead you are telling me instead is that:
1)The front -end application implicitly compiles a parameter query and sends this to the back-end thus retrieving just the few related records (few KB to be transmitted through the net).
2)Once received the records the subform shows them without any further filtering.

Did I get it right? If so do you have any document or link where i can better understand the process of communicating between backend and frontend when any request of information is sent?
Thank you for any help you can give me.
Jacopo

[nico5038]

Dear Nico,
thank you very much for your kind and competent reply.
I understand what you mean and you are right, if Access can take care of all the concurrency problems is quite better.
You are giving me a precious information that I didn't find on any of the Access books I have or on any website I visited so I want to make sure I got it right because it is crucial.

What I thought it was the process related to filling a subform:
1)The front-end application asks to the back-end database all the records of the many side table, in this case the items related to the invoices.
2)Once received ALL the records of all the invoices (several KB, maybe MB of info) the front-end form filters them according to the main form ID.

What instead you are telling me instead is that:
1)The front -end application implicitly compiles a parameter query and sends this to the back-end thus retrieving just the few related records (few KB to be transmitted through the net).
2)Once received the records the subform shows them without any further filtering.

Did I get it right? If so do you have any document or link where i can better understand the process of communicating between backend and frontend when any request of information is sent?
Thank you for any help you can give me.
Jacopo

Access normaly will fetch all tablerows for filtering the rows needed for the subform, there's no "network load" difference with issuing a parameter query, as that too will trigger Access to do this "table fetch".
The way to minimize network trafic is to use an ODBC database (e.g. the free MySQL) and use a PassThrough query. Such a query will be set as "text string" accross the network and will only return the selected rows.
Access isn't really suited to act as a "true" ODBC database :-(

I guess the description in the helpfile about the PassThrough query will reveal most info about the way a backend database is processed by Access.

Personally I would have started with a "plain" database with bound forms, as that's the fastest way for development. When the performance proves to be too slow I would switch to rebuild the database with unbound forms. Just makea small test database with the amount of data expected and see how good/bad it performs.

Nic;o)

[Jacopo]

Dear Nico,
again ty for your time. First of all it makes sense developing the db in the easiest way and then fix it later on. I am thinking about it, though I personally prefer to struggle on one single form and then when i am sure it works well, easily use it as a template for all the others, rather than going back and changing 50 forms especially after your customer has complained for a slow application.
Also in this case, we are talking about a "parts" table which likely will have up to 20000 records. Imagine that at least 2000 of them have a bill of materials with on average 50 components. The middle many to many relationship table will have likely 100000 records in the long run.

I must admit that you undermined some of my certainties. After your message I read again about the pass through queries. As I remembered they normally apply to back end database other than jet.
Furthermore (maybe I am wrong), your answer looked like denying something that I took for granted, that is that stored queries in access should work like stored procedures in SQL Server. I mean that when you call from the client a stored query on the server side, the stored query processes the rows on the server and passes back just the selected sets of records.
This idea of mine I think that is supported by the fact that any web page, which of course doesn't have any way to post process recordsets, is able to get subsets of data from a remote source.
If this is true I don't see the point of using pass through queries since:
a) there is no difference in sql dialect between client and server, both jet.
b) a stored query on server anyway ensures a pre processing of records.

I will be grateful if you let me know if this idea of mine is wrong and for what reasons, being better to learn from others' experience than from our mistakes.
Thank you again,
Jacopo