Stored Procedures and ADP

[gazzippy]

I am using an Access 2000 ADP that has a form that has its RecordSource as a stored procedure in SQL Server 7. The stored procedure is using 2 tables, does anybody know how I can edit the data in both the underlying tables (This worked fine when the RecordSource was a query in an Access 2000 MDB using ODBC to connect to the same tables).

 

[neecie]

The only way that I know of is to use unbound controls, write code and stored procedures to do the update after specific events, then refresh the control you just updated.

Depending on the complexity of your updates, you can write a function that takes the stored procedure name and any parameters in a array of variants

 

[gazzippy]

Thanks neecie, I was hoping to avoid this route, but if its the only way then back to the grindstone I go!!

Thanks again.

 

[birgitd]

Can you use a view instead of a stored procedure as the record source? You can typically update a view without writing custom extra code.

Thanks,
Birgit

 

[neecie]

Birgit,
If you base a form on a view, you still need to define a unique table for the form. This would not allow both of the tables to be updated.

I prefer to use only unbound forms and write code for any changes to the data.

For me, it is the best way to control who puts what data into the system, and if anything goes wrong (records not updated, data lost, etc.) I know who to blame (myself), and that I can address the error in code.

The few times that I have allowed MS Access to control data input/updates, I have regretted it. Access does not report internal errors in a comprehensive fashion!

 

[Databaseguy]

I prefer to use only unbound forms and write code for any changes to the data.

Ditto. Having a form bound directly to a table is asking for trouble.

If you set your relationships up correcty, and enable cascade update and delete, any changes made to the one side will automatically propagate to the table with the many records...