N
Neil Moss
Hi group.
I'm having to convert an Access 97 forms app so that the tables are held on
a SQL server. The requirements I have been given state that base tables
should not be directly modified from the client - stored procedures on the
SQL server must be used instead so that permissions can be granted via SQL
role groups.
Is is possible to still use databound forms with this requirement? I am new
to Access forms, but I am totally happy with VBA and ADO. I guess what I am
missing is the in-depth knowledge of the Access object model.
I have all the (now SQL) tables linked via ODBC using a trusted connection,
with Select permissions on the sql server to the "public" role. Thus the
forms can present their data without any coding changes. I have intercepted
the BeforeUpdate event on the form to invoke insert/update stored procedures
via ADO. However, the crunch is that the UI just doesn't like me doing
this -
If I Cancel the event, you cannot navigate the cursor off the current
record.
If I call Me.Undo, the changes are apparently lost to the user, and a
"record modified" warning is shown if the user returns to the updated
record.
If I call Requery the cursor is repositioned back to the first record and
not the record they have moved on to.
All in all, not a great experience for the user. Is there a way of
refreshing the data in a form whilst leaving the cursor/focus in whatever
position the user tried to put it? Can I 'spoof' Access into thinking that I
have handled the updates and that it doesn't need to do anything?
Elaborations on demand...
Thanks,
Neil Moss.
I'm having to convert an Access 97 forms app so that the tables are held on
a SQL server. The requirements I have been given state that base tables
should not be directly modified from the client - stored procedures on the
SQL server must be used instead so that permissions can be granted via SQL
role groups.
Is is possible to still use databound forms with this requirement? I am new
to Access forms, but I am totally happy with VBA and ADO. I guess what I am
missing is the in-depth knowledge of the Access object model.
I have all the (now SQL) tables linked via ODBC using a trusted connection,
with Select permissions on the sql server to the "public" role. Thus the
forms can present their data without any coding changes. I have intercepted
the BeforeUpdate event on the form to invoke insert/update stored procedures
via ADO. However, the crunch is that the UI just doesn't like me doing
this -
If I Cancel the event, you cannot navigate the cursor off the current
record.
If I call Me.Undo, the changes are apparently lost to the user, and a
"record modified" warning is shown if the user returns to the updated
record.
If I call Requery the cursor is repositioned back to the first record and
not the record they have moved on to.
All in all, not a great experience for the user. Is there a way of
refreshing the data in a form whilst leaving the cursor/focus in whatever
position the user tried to put it? Can I 'spoof' Access into thinking that I
have handled the updates and that it doesn't need to do anything?
Elaborations on demand...
Thanks,
Neil Moss.