Bill
|
| Posted: 01/05/2006, 4:37 AM |
|
Need help please, I cant figure this thing out.
In Access one can build forms/subforms based on multiple tables, and edit those table values from the form.
I understand same can be done in CCS (I hope so) but in order to do this, must set Custom Update and Custom Delete form properties. But there is no place to join mutiple tables when doing this. You can select either a table, an SQL statement or stored procedure. Where is the place where you can visually set the multiple tables?
Thanks
|
|
|
 |
magus
Posts: 98
|
| Posted: 01/05/2006, 2:21 PM |
|
Dear Bill,
I have experience with multiple table forms using PHP and MySQL. In that context at least the short answer is that there is no "visual" way to set up to insert, update and delete multiple tables through a single form in CCS. However, it can be done.
I found it quite a complicated business to figure out initially. You have to manually replicate parts of the generated code in events for the other table operations. (i.e. SetValues method and the specific Insert or Update method. Look at the generated code.) Then manually run Insert, Update or Delete on each of your other tables from events.
If you have stored procedures available in your database then process may be a lot simpler. If not and you are a CCS novice expect headaches and frustration. Also expect to learn a lot more about how CCS works.
I hope this is some help.
Regards,
Don A
|
 |
|
peterr
Posts: 5971
|
| Posted: 01/05/2006, 4:57 PM |
|
1. There are many logical reasons that this cannot be implemented "automatically". Even MS Access cannnot make all queries updateable (famous error "Operation must use an updateable query.") and some updateable queries are updated wrong, or at least the way MS Access think that you want to update them, but without giving users choice how to update them. So using updeatble queries in MS Access requires logical understanding of how this feature works and/or taking a risk of corrupting the data (I just tested this and Access updated wrong table and corrupted my data, while wasn't able to insert records at all). So relying on something automated is not always desired, just because it works in some way. In CCS it is better that you make own decisions on what to update and how, and that's what the Custom Insert/Update and events are for.
Although CCS can only execute one SQL Update or Insert statement, therefore any additional table updates would have to be handled via custom code anyway.
2. If you have an updetable query saved in MS Access that is tested and works the way you want then you could try using that query (instead of tables) in CCS and check if it works, either via DSN/ODBC or JET connection (as it may work with one or another). I'm not sure if this will work though, so it is only a suggestion to try.
3. You can visually specify multiple tables for retrieving data in your grid's data source. The Custom Insert/Update is used only to specify how to update data in one of those tables. You would specify the table that you want to update, and then the fields in that table.
If you like to update additional tables then you can use events with custom code for this. There are various examples of this in CCS documentation and sample solutions. For example the "Execute Custom SQL" section of the documentation, "ShoppingCart" page of the Store example, and Many-to-Many examples in CCS Example Pack.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Bill
|
| Posted: 01/05/2006, 8:09 PM |
|
Thanks for feedback Magus and Peter, appreciate it.
In Access I can easily build a form on three tables. The form is fully editable and the data is secure. One A has many B's and each B has many C's.
Build a form/subform based on B and C (query).
Then build another form based on A. Then place B/C on A, linked by fkA.
I will try work through your suggestions, thanks again.
Regards
|
|
|
|
|
