Re: Help with Master/Detail UI in ADPs

Thanks Mark. Yes I would plan on dropping DAO use in adps.

The link about recordsets is quite informative.

You comments about delegating transactions to SQL Server are much
appreciated. That hadn't occured to me yet.

A re-writing of my app is certainly required. I'm trying to get a overview
of "best practices" and you've been helpful. I orderd a copy of Mary Chipman
and Andy Baron's upsizing book yesterday and hope to get a few more ideas
from there. I've been reading Russel Sinclair's book and Paul Litwin's
Access 2002 Enterprise.

Unfortunately I have never found a book that deals adequately with how to
handle OK and Cancel processes on Access forms displaying a one-to-many
relationship where the many is handled with a subform in data*** or
continuous forms view. It's quite astonishing to me as this kind of
relationship exists throughout databases. I've come up with my own approach
for mdb's by having local tables but local tables aren't available in adp's.
Tom Ellison has suggested an approach and I've been hoping that others would
too.

Patrick.

"Mark Shultz Jr" <mark.shultz@xxxxxxxx>
wrote in message news:9cOdndgVsNIvr2_eRVn-oA@xxxxxxxxxxxxxxx

I would look into dropping all DAO code in favor of ADO. This is because ADO
is more native to the way the ADP will access the SQL Server data. For
example, your connect string can always be "currentproject.connection" when
you're working with the same backend database.

As far as binding a form to a recordset...I've not used this myself, but
I've read in a few places that forms and reports have a RECORDSET property
that allows you to bind the form directly to the recordset.

Here is a link to a Microsoft knowledge base post on that subject...
http://support.microsoft.com/kb/281998

The thing to remember here is you are moving from a workstation based
architecture to a client/server architecture. The key here is to make the
server do as much work as possible, that's what it's there for, and to
transmit the smallest amount of data over the network as possible. Let the
server handle the transactions. If you use continue to process the majority
of your data logic on the workstation, you could potentially end up with a
performance drag because large amounts of data to the client.

The other thing I'm noticing here is that you seem to be wanting to keep
very tight control of the transaction, again from the workstation...SQL
server is completely transaction based, so if you tell it you're starting a
transaction and for some reason it doesn't complete (workstation looses
connection, etc) the ENTIRE transaction gets rolled back no matter what
happens to the workstation.

In order to get performance advantages out of using a SQL server, beyond
just getting a larger data store, you pretty much need to re-write your app
and it's logic to use the new "Server" layer that wasn't available before.

Just my two cents worth, of course.

Mark Shultz
Procurement Data Specialist
RAD, Inc. (dba Interstate All Battery Centers)

"Patrick Jackman" <pjackman@xxxxxxxxx> wrote in message
news:u51JmhYMGHA.3728@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Mark.

Acceptable performance to me is a screen open in under 2 seconds.

If I could choose between "staging" tables on the server and in-memory
tables on the client, I would prefer to bind subforms to disconnect-able
recordsets, or directly to business objects, but I haven't been
successful
with this yet. I was successful a few years ago using Rockford Lhotka's
ODSOLEDB approach from his "Professional Visual Basic 6 Distributed
Objects"
to bind a VB6 business object to an ADODB recordset. But there appears to
be
limits on the kind of recordsets that can be bound to ADP forms. Do you
know
where this is documented?

In my present architecture, each child screen in a parent/child
relationship
is self contained. It has public Create, Load, IsValid, ObjectIsDirty and
ApplyEdit methods that are called by equivalent methods in the parent
screen. The ApplyEdit methods have a DAO.Workspace parameter to manage
the
entire entity's transaction.

I'll watch out for mixing up 's and "s; thanks for the head's up.

Patrick.

"Mark Shultz Jr" <mark.shultz@xxxxxxxx> wrote in message
news:ppOdnQg1S9O-IW3enZ2dnUVZ_v-dnZ2d@xxxxxxxxxxxxxxx
I'm curious what "acceptable" performance is? I've seen systems
designed
this way with the same number of users, I've not even had acceptable
performance with a local MDB file of 700mb! I think you and your users
will be very impressed with the performance boost they'll get by going
to a
true client/server architecture.

I agree with Tom's suggestions, but will add the following:

One of the advantages of using a client/server architecture is the
availability of the server to process transactions. The ADP/SQL(or
MSDE)
will require a different style of coding in order to be effective. A
lot
of the functionality you used to get from VBA will need to be coded in
T-SQL.

You are used to doing your transaction processing from the local
workstation using vba code, what you would do in this situation is post
the data to
the database, possibly even into a "staging" set of tables for
processing or
even directly into your transaction tables.

If you use the "staging" method, you could use your "ok" button to
execute a
stored procedure to validate the data and transfer it to the
transaction
tables

If you choose to write directly to the transaction tables, you can
either
use a trigger to validate each row as it's entered, or use the
"tentative"
or "Pending" flag as Tom suggested and execute a stored procedure
against
the pending records for each transaction, OR you can execute a batch
update periodically (even automatically via SQL Server Agent) through
out the
day, depending on your needs.

One word of caution I tell everyone new to ADP/SQL server, watch your
use
of ' and "...vba likes " for strings and SQL server will use '. Getting
the two
mixed up can be nasty to track down!

Another suggestion would be to use temp tables, however sometimes
Access can
be flakey about using them.

Just my two cents worth here. I've pretty much exclusively used the
ADP/SQL combination since 2001 and I don't think I would ever go back
to mdb
files, even for the smallest/simplest apps since Microsoft has made
MSDE/SQL
Server express edition free, it just makes more sense to know that all
of my apps
are scaleable from both a data and concurrent user standpoint.

Good Luck!

Mark Shultz
Procurement Data Specialist
RAD, Inc. (dba Interstate All Battery Centers)

"Patrick Jackman" <pjackman@xxxxxxxxx> wrote in message
news:eiZNRjHMGHA.2216@xxxxxxxxxxxxxxxxxxxxxxx
In Access DAO/Jet applications, I handle Master/Detail relationships
by
using unbound fields for the Master table and a subform bound to a
local
temp table for the Detail rows with OK, Apply and Cancel buttons on
these
forms. If the user clicks OK or Apply, I do concurrency checks then I
write
from the unbound fields to the Master table on the server and from the
local
temp table to the Detail table on the server for rows that are new,
dirty
or deleted. If the user clicks Cancel, I just close the form.

In some applications I have up to 5 Detail subforms against a Master
without
any issues. Using temp tables allows me to validate business rules
between
the various M/D entities before saving any changes. I also use
multi-instance forms to allow users to have multiple views of M/D data
open
concurrently and I keep track of what data in a local temp table
belongs to
which form instance by loading the subform's hWnd along with its data.

Using local temp tables for Detail entities and local tables for
lookups
allows me to have acceptable performance with 20 - 25 concurrent users
against a 700 MB backend. This approach is code intensive but I've had
time
to streamline it over the last 12 years of working with Access
full-time.

I would like to start using SQL Server 2000. If I connect with ODBC I
can
continue with the same design approach using local temp Jet tables.
But I
would like to consider using ADPs to avoid the reported performance
penalty
of ODBC.

Is there a "best practice" for Master/Detail form design with ADPs?

I've tried several approaches without success:
1. Bind ADO adLockBatchOptimistic recordsets to the Master and Detail
forms
then set the ActiveConnection = Nothing. When I reconnect and issue
UpdateBatch, the M will update in certain situations but the D never
updates.

2. Create an in memory ADO recordset for the Detail, load it with data
and
bind it to the Detail subform. The binding fails with #Error in each
field
of the subform.

3. The "Access 2002 Enterprise Developer's Handbook" approach on p.
281:
"Using Transactions with Bound Forms". It works until I attempt to
open a
second form instance on the same row while the 1st is still in a
transaction. And I've read posts here suggesting this is not a best
practice.

What is the preferred approach for dealing with this issue in ADPs?
Has
anyone written about it from a real world perspective?

Patrick


.