Subforms connected to SQL Server
Michael Beck
Server as the back end. My data entry forms are all unbound. I use code
to read/write the data to or from the database.
My problem is with forms that have subforms. For example, the customer
form includes the shipping addresses that could number 1 to x. I can
control updateing data in the main form becuase it is not bound. All
inserts or updates are inside transactions that call stored prodecures.
But the subforms need a recordset and I loose control of when and how to
update data.
I have worked out several possible solutions, but none of them is
entirely satisactory.
Someone else must have encountered this problem. In genral terms, what
was the methodology you used? Or can anyone point me to a succint
article or white paper that covers this topic.
All help is appreciated.
Mike
Steve Jorgensen
wrote:
Assuming your whole point is to have a disconnected application - one
that connects only briefly, during actual updates, and assuming you
are using Access 2000 or 2002, your answer will have to be binding
forms to disconnected recordsets. This works with either an MDB or
and ADP, but I see no good reason to use an ADP for this type of app.
Unfortunately, in Access 2000, these recordsets are not updateable,
and in Access 2002, they're updateable, but it's awfully quirky. If
you are using Access 2000, to edit the rows in the recordset, you'll
need a button on each row to pop open an umbound form to edit the
data, then use code to write the data back into the unbound recordset.
Here are some of the quirks you may want to know about:
1. You may or may not be able to update controls bound to a
disconnected recordset using code. You may have to do it through the
recordset object.
2. Applying and removing what should be client-side filters and sorts
may sometime make Access reconnect the recordset. You can make sure
this doesn't happen by making sure the table the recordset is based on
is a temporary table that no longer exists (recommended), but you'll
still have an error/problem when Access tries this.
3. Trying to manipulate the recordset in any way (updating data,
applying filters/sorts, doing .MoveNext, doing .Find, etc.) while it
is bound to the form may cause problems including crashing Access.
Instead, turn off form painting, unbind the recordset, manipulate it
from code, then bind it again and turn form painting back on. This is
suprisingly fast, actually.
4. You may thing it's a good idea to use a disconnected recordset
with a lock type of OptimisticBatch so you can reconnect the recordset
and apply all the updates as a batch. There are 2 problems with this.
First, Access mangles the metadata in the recordset when you edit it
to a form in such a way that batch update becomes useless (it updates
the underlying value property, not the current value - something
that's not even possible through the ADO api). Second, if you
followed number 2 above, you won't have queried the table directly.
To solve number 4 takes some work. If you're doing data entry, just
copy all the rows into another OptimisticBatch recordset that can then
be posted, but if you're editing, you'll need to make 2 copies of the
disconnected recordset (save to a stream, then recreate another
recordset from that), compare them after the session to see what has
been changed, added, and deleted, then do the batch update from that
copy.
Eventually, I indent to write code to encapsulate this process, but
that's very difficult to do. Better would be a service pack that
makes Access capable of editing a disconnected Batch Optimistic
recordset properly including updating the proper field attribute and
knowing it should never try to requery/reconnect.
Larry Linson
consider using bound forms and subforms. Plain old Access - Jet - ODBC -
server database has performed well for many of us with user audiences in the
low hundreds using all or mostly bound forms, even if the databases weren't
"100% wonderful" -- I worked on one that had a lot of people work on it over
time, of varying capabilities, and it still supported just under 200 users.
"Michael Beck" <mik...@pacbell.net> wrote in message
news:3DE598EB...@pacbell.net...
John Winterbottom
news:aavcuuc4ekdhv73vc...@4ax.com...
>
> To solve number 4 takes some work. If you're doing data entry, just
> copy all the rows into another OptimisticBatch recordset that can then
> be posted, but if you're editing, you'll need to make 2 copies of the
> disconnected recordset (save to a stream, then recreate another
> recordset from that), compare them after the session to see what has
> been changed, added, and deleted, then do the batch update from that
> copy.
>
> Eventually, I indent to write code to encapsulate this process, but
> that's very difficult to do. Better would be a service pack that
> makes Access capable of editing a disconnected Batch Optimistic
> recordset properly including updating the proper field attribute and
> knowing it should never try to requery/reconnect.
Steve, I'm not sure if you've had a chance to work with ADO.NET yet, but it
seems to me this is exactly the sort of thing that the .NET dataset is
designed to do. They are very powerful, with all sorts of mechanisms to
control how updates are batched back to the server.
My guess, (just a guess), is that when Access.NET comes around these sorts
of problems will be addressed.
Steve Jorgensen
I know. ADO.Net datasets sound really nice. Of course, it would be
nice if, first, Access would get around to supporting the very nice
disconnected recordset capabilities ADO already has. Batch Optimistic
locking and batch updates are already there, it's just that Access
mangles it if you edit one of these through a bound form.
Andy
> Unless you have some _compelling_ need to use unbound forms, you should
> consider using bound forms and subforms. Plain old Access - Jet - ODBC -
> server database has performed well for many of us with user audiences in the
> low hundreds using all or mostly bound forms, even if the databases weren't
> "100% wonderful" -- I worked on one that had a lot of people work on it over
> time, of varying capabilities, and it still supported just under 200 users.
Access is pretty good at what it does.
It's best when your app can fit in with access rather than when you
have very fixed requirements of the sort described.
This is one of the instances when my recommendation would be to work
in vb.net and forget access.
Or
change your design.
If you must stick with access....
Consider whether you can get away with copying the data to a local
table.
That way you can have either or both forms bound to something concrete
and control when you write it back to the main sql server database.
Potentially, you need soft locking to ensure nobody goes and changes
the data inbetween copying over and writing back.
Matthew Sullivan
as a back end, and it works great.
And since I have no bound tables, home-grown locking, and only a
single ADO connection for transaction processing, the guys who
maintain the server like me because the app uses server resources
pretty sparingly.
(I had originally tried to use bound forms, but had too many table
relations to get acceptable performance.)
We're still using A97. When my company moves to XP I intend to
experiment with the new features I keep reading about in this group.
-Matt
Dimitri Furman
news:aavcuuc4ekdhv73vc...@4ax.com:
> 2. Applying and removing what should be client-side filters and sorts
> may sometime make Access reconnect the recordset. You can make sure
> this doesn't happen by making sure the table the recordset is based on
> is a temporary table that no longer exists (recommended), but you'll
> still have an error/problem when Access tries this.
>
Steve,
From your experience, is this still an issue when the recordset was
originally based on a SP as opposed to a table? What can be expected in
that case? Thanks.
--
(remove a 9 to reply by email)
Steve Jorgensen
wrote:
Generally, the problem will exist for a stored procedure because ADO
tries to be clever and get the SP to tell it what the actual query and
underlying tables were for the records returned. That is how it is
possible to update a recordset returned from a stored procedure when
using ADO.
Dimitri Furman
in news:pgcguughvlh6dfuq8...@4ax.com:
I wonder what would happen in that case if no permissions are granted
directly on tables. Will it still try and raise a corresponding error, or
is it smart enough to give up at that point?
Steve Jorgensen
wrote:
I haven't tried yet. I'll post on CDMA if/when I have info on this.
Andy
<<>>
> We're still using A97. When my company moves to XP I intend to
> experiment with the new features I keep reading about in this group.
Disconnected recordsets I'm using in vb.net are nice.
The checksum business does away with the need for any softlocking....
which can be a bit of a pain...
To an access developer, the amount of work to get a combo box on a
grid would be mind blowing though.
Essentially... you add a control to the grid control and move it over
cells you're editing..... write a bunch of event handlers.