SUMMARY: Access lets you join two tables into an updateable (and insertable) view without any code. That's right, Access will let you insert two linked records into two linked tables without any code. The A5 grid control doesn't do this, and it's buggy if you try to import the access view. Here's an incredibly simple database that unambiguously illustrates this issue. What's the best way to handle this?
DETAIL:
I have a database in MSAccess with two linked tables, linked by a left join. For discussion purposes, I call this the 'Men's club' database, and it has two tables : tblMen and tblWives. The database records the members and, if they are married, their wives. I use two linked tables, one for the members, and another for their wives. My real app is more complex, but I've simplified it in order to unambiguously encapsulate the joined table issue.
Each member of the Men's Club has a record in tblMen (sorry ladies, I'm afraid they are all men), with fields:
mID integer autoincrement
mFirstname text
mLastname text
mWifeID integer ' external link to wife's record, if present
tblWives has 3 fields:
wID integer autoincrement
wFirstname text
wLastname text
(again my apologies to domestic partners who don't fit this oversimplified model)
I first created a view in MSAccess with a left join of tblMen and tblWives where tblMen.mWifeID = tblWives.wID. I immediately got a nice form where the member's name and his wife's name are all on one line. If I enter a member's name, but I do not enter a wife's name, then just a tblMen record is created. However, if I also enter his wife's name in the view's wFirstname and wLastname fields, then Access creates both a tblMen record for the man and a tblWives record for his wife, and stores the newly created Wives.wID in the man's mWifeID field. Access does this without any code. I'm not always a Microsoft fan, but this seems pretty intelligent on their part.
If I try to do this in an Alpha 5 grid control defined by the same SQL left outer join, it doesn't work. Alpha 5 will not create the wife record, so it either gives an error or just ignores the wife data, depending on which grid template I try.
If I do this with the Alpha 5 template for read-only grid with updateable detail, and base the grid's query on the joined MSAccess view, then A5 almost works. Basically, it inserts the data OK, but you have to close the grid and reopen it to see the data.
(After I save the new record, the new record is not displayed in the Alpha 5 grid. Instead, a random previous record is repeated in the last row of the grid. Also, in this instance, the alpha 5 insert causes the access view to add a wife record every time, even if no wife data is entered. And, there is another problem if there is no wife data -- it gives an error message, but again, if you open and close the grid then you do get to see the data).
Anyhow, my questions are these:
(1) what's the best way to get the grid component to do this -- i.e., update two tables from one line in the grid? It looks like the grid based on a sql query can't do this without code. I realize that inserts into 2 tables are not supported in the most rigorous interpretation of SQL (there is an INSTEAD OF clause), but Access seems to be designed to let you do this without writing any code. More specifically, what's the easiest way to get the wives' record inserted in A5?
(2) Is there any way to get the grid to correctly display after insert into an Access view? The view based grid seems to update the database OK, but you have to close the grid and repopen it to get it to display correctly. If this worked, I'd be able to import my codeless two table Access view into an A5 grid control and take the rest of the day off. This would be nice ;-)
I've seen a few other posts on this topic, but never any clear resolution. This example is quite obvious and trivially replicable, and I'd be glad to upload the test tables and grid components I've based it on.
Thanks,
Bruce
DETAIL:
I have a database in MSAccess with two linked tables, linked by a left join. For discussion purposes, I call this the 'Men's club' database, and it has two tables : tblMen and tblWives. The database records the members and, if they are married, their wives. I use two linked tables, one for the members, and another for their wives. My real app is more complex, but I've simplified it in order to unambiguously encapsulate the joined table issue.
Each member of the Men's Club has a record in tblMen (sorry ladies, I'm afraid they are all men), with fields:
mID integer autoincrement
mFirstname text
mLastname text
mWifeID integer ' external link to wife's record, if present
tblWives has 3 fields:
wID integer autoincrement
wFirstname text
wLastname text
(again my apologies to domestic partners who don't fit this oversimplified model)
I first created a view in MSAccess with a left join of tblMen and tblWives where tblMen.mWifeID = tblWives.wID. I immediately got a nice form where the member's name and his wife's name are all on one line. If I enter a member's name, but I do not enter a wife's name, then just a tblMen record is created. However, if I also enter his wife's name in the view's wFirstname and wLastname fields, then Access creates both a tblMen record for the man and a tblWives record for his wife, and stores the newly created Wives.wID in the man's mWifeID field. Access does this without any code. I'm not always a Microsoft fan, but this seems pretty intelligent on their part.
If I try to do this in an Alpha 5 grid control defined by the same SQL left outer join, it doesn't work. Alpha 5 will not create the wife record, so it either gives an error or just ignores the wife data, depending on which grid template I try.
If I do this with the Alpha 5 template for read-only grid with updateable detail, and base the grid's query on the joined MSAccess view, then A5 almost works. Basically, it inserts the data OK, but you have to close the grid and reopen it to see the data.
(After I save the new record, the new record is not displayed in the Alpha 5 grid. Instead, a random previous record is repeated in the last row of the grid. Also, in this instance, the alpha 5 insert causes the access view to add a wife record every time, even if no wife data is entered. And, there is another problem if there is no wife data -- it gives an error message, but again, if you open and close the grid then you do get to see the data).
Anyhow, my questions are these:
(1) what's the best way to get the grid component to do this -- i.e., update two tables from one line in the grid? It looks like the grid based on a sql query can't do this without code. I realize that inserts into 2 tables are not supported in the most rigorous interpretation of SQL (there is an INSTEAD OF clause), but Access seems to be designed to let you do this without writing any code. More specifically, what's the easiest way to get the wives' record inserted in A5?
(2) Is there any way to get the grid to correctly display after insert into an Access view? The view based grid seems to update the database OK, but you have to close the grid and repopen it to get it to display correctly. If this worked, I'd be able to import my codeless two table Access view into an A5 grid control and take the rest of the day off. This would be nice ;-)
I've seen a few other posts on this topic, but never any clear resolution. This example is quite obvious and trivially replicable, and I'd be glad to upload the test tables and grid components I've based it on.
Thanks,
Bruce
Comment