Updateable queries in adp

[Darrell Cyphers]

I have designed queries to provide data to a subfrom for
some time in mdb files structured like this:

Main form provided data by ONE side of a many-to-many
relationship;

Subform provided data by a QUERY consisting of the two
foreign keys in a junction table and selected fields from
the OTHER side of the mant-to-many relationship.

The query structured this way has always been updateable in
mdb. However, the exact same structure is not updateable in
adp. Can anybody tell me if this is fixable, and, if so, how?

 

[Steve Jorgensen]

I have designed queries to provide data to a subfrom for
some time in mdb files structured like this:

Main form provided data by ONE side of a many-to-many
relationship;

Subform provided data by a QUERY consisting of the two
foreign keys in a junction table and selected fields from
the OTHER side of the mant-to-many relationship.

The query structured this way has always been updateable in
mdb. However, the exact same structure is not updateable in
adp. Can anybody tell me if this is fixable, and, if so, how?

Queries like this are supposed to be updateable in ADPs, and when they're not
by default, the solution is supposed to be to select the correct Unique Table
setting. In practice, I've found that it can be hard to force all the stars
to align properly to make multi-table queries editable in ADPs, and harder to
keep them that way over time, so I gave up.

The trick I use now for editing tables and displaying related information is
to use combo boxes with extra hidden columns for all the related data I might
need, and making calculated controls that refer to the combo box columns to
display the appropriate values. It's a kludge, but it always works on the
first try, and it doesn't break the next time you make a minor change to the
query.

 

[Darrell Cyphers]

Okay.... At least I see my frustration is understood...
Well, you're right, it is a kludge, but... it's perhaps
better than something that doesn't work at all! Thanks for
the feedback!

 

[Marshall Smith]

First, make sure each of your tables has a primary key defined. You must
define a primary key on each and every table you want to edit in an ADP,
unlike an MDB.

Second, are you actually talking forms and subforms? Because that's a
slightly different animal than making, say, a view updateable.

Third, review your query very carefully. Make sure that all of your joins
make sense. And, note that you will only be able to make changes in the
"most-many" table in the query. Also, make sure that your query is an
"updateable snapshot."