I am new to using sql server. I am coverting an access97 application. The existing app uses a front end and backend. I Upsized the backend and pointed the links to the sql server. It was very slow and some features did not work. The application has one main table and three related tables. I have created views and Stored Procs, and have been been rewriting the front end with pass through queries, with a lot of success, but I need some advice on updating and creating new records.
The only way I can see to do this is to obtain one main record and its associated records using passthrough queries, copy them to temporary local tables where they can be the recordsouce of forms and subforms. (I can not update the records through the passthrough queries). Saving the main record back to the SQL server is the reverse process, saving the subform records is a little more complex. I would have to cycle through each record of the (Access) RecordsetClones, changing the SQL of the passthrough queries and then executing them.
Somewhere I think I am going off the rails.
Note that it is DAO. There are many pcs on the network that only use Access97. I can't upgrade them.
Any suggestions will be appreciated.
The only way I can see to do this is to obtain one main record and its associated records using passthrough queries, copy them to temporary local tables where they can be the recordsouce of forms and subforms. (I can not update the records through the passthrough queries). Saving the main record back to the SQL server is the reverse process, saving the subform records is a little more complex. I would have to cycle through each record of the (Access) RecordsetClones, changing the SQL of the passthrough queries and then executing them.
Somewhere I think I am going off the rails.
Note that it is DAO. There are many pcs on the network that only use Access97. I can't upgrade them.
Any suggestions will be appreciated.