"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:9425D5E9Fdfentonbwaynetinvali@24.168.128.78.. .[color=blue]
>
rickbrandt2@hotmail.com (Rick Brandt) wrote in
> <bnul1e$1404hj$1@ID-98015.news.uni-berlin.de>:
>[color=green]
> >There may in fact be some advantages to ADO in NEW applications
> >(still waiting for some published data on this), but for a mature
> >app already fulfilling its requirements using DAO it just seems
> >like an incredible waste of time to attempt converting it.[/color]
>
> Well, that's certainly my thought.
>
> But the reason to use ADO is partly to avoid having to do a lot of
> server-side stuff. I'll be administering this SQL Server app, so
> that won't really be an issue, but if I can do through ADO what
> would take a complex stored procedure in ODBC, maybe it would be
> worth it.[/color]
But you could also just create a DAO Recordset from your ODBC link (or a
Pass-Through or an SP) and run your existing process on it. If it were a very
complex process that needed to be run against a LOT of records so that the
result of a few records could be returned, then it would make sense to build it
as an SP on the server.
Remember that you will never pull *more* records from a SS back end than you
have been with your Jet back end. If it's been a managable amount of traffic
with Jet it still will be with ODBC. That isn't to say that one shouldn't
optimize where it's possible, but that doesn't mean that you have to do it all
at once. You see where the most needs are and pick your battles.
[color=blue]
> In other words, where I'm contemplating replacing the existing
> mechanisms with ones appropriate to SQL Server, should I not
> contemplate the ADO methods for doing so?[/color]
Possibly. As stated previously, I have never used ADO. I read a lot of posts
stating that I should be, but I never see any tangible reasons. Mostly just
people who use it "because". Which is perfectly fine. If people use it only
because they are familiar with its use and not with DAO, that's a perfectly good
reason. Many such posts state either implicitly or explicitly however that ADO
is so much *better* than DAO. What is never explicit in those posts is why.
[color=blue]
> Is there a problem mixing the two in an app, using linked
> tables/ODBC in some places and ADO in places where it makes things
> easier?[/color]
Other than the need to disambiguate a few object types I don't know of any
reasons not to use both.
[color=blue]
> What kind of tasks would those likely be?
>
> I've got a number of issues here:
>
> 1a. adding new people currently uses an unbound form that collects
> a little data and presents a list of possible matches (to reduce
> creation of duplicates; if they don't check for dupes they can't
> add the record). My algorithm for evaluating closeness of the match
> to what the user puts in is complex and belongs serverside, no
> doubt. In thinking it through, I guess I'd really only pass the
> WHERE clause that limits results to the close matches and then do
> the calculation in Access using my algorithm for evaluating
> closeness. I shudder to think about doing that server-side, as the
> code is fairly complex. It will only be operating on small numbers
> of records (always fewer than 100 or so), so I don't see an issue.[/color]
Well, writing SPs in T-SQL _is_ harder (IMO) than using VBA code, but I can also
attest that it is way easier on SS than on other platforms. I also have to
write SPs on our AS400 which uses IBM's Universal Database (close to DB2). It
is a lot closer to ANSI SQL and is not nearly as intuitive. It also doesn't
provide the nice tools the SS has for the task. After I've been working in that
for a while T-SQL and SS feels like child's play.
[color=blue]
> 1b. the other part of adding a person is actually doing it. In
> Access I open an AppendOnly recordset, add the record, plug in the
> values, cache the PK value, save the record, close the recordset,
> then open a form with the newly added record loaded. A stored
> procedure with input parameters and an output value of the PK would
> do this just fine, and will replace the existing code quite easily
> (it's already a function that adds the record and returns the PK
> value)[/color]
Yes, but again a plain ol' DAO Recordset with zero rows returned (just for
adding records) would be imperceptibly different in terms of performance,
[color=blue]
> 2. the main data person entry form is populated through a FIND
> function that is based on name, and returns small groups of records
> (1-100 or so, usually in the range of 3-10 records). It allows
> wildcards so it's not always exact matching (and therefore, often
> returning more than just 1 record). I just change the recordsource
> for each search. This could surely be done with a passthrough, and
> would easily replace the functionality I've already got in place,
> simply by altering the SQL that is generated to be a passthrough.
> Oops, no a passthrough doesn't work as a form recordsource, no? So,
> it would be better as a stored procedure, I guess.[/color]
You can't use Pass-Throughs for sub-reports and sub-forms. They work fine for
top level forms and reports though.
[color=blue]
> 3. in the main data entry form there is a summary calculation of
> monies donated that appears at the bottom of the form. Presently
> I'm using an in-memory summary recordset that has totals for each
> person precalculated and then do some complicated work to update
> that (having a custom collection of PKs that have totals that need
> to be updated, and requerying the recordset after N additions --
> messy, yes, but it improved performance a lot). This could all be
> replaced with a stored procedure and would be vastly more efficient
> and easy to maintain. I'd have to look into whether it makes more
> sense to have return values for the subtotals, or return a
> resultset and walk through that to get the values for display.
> Since the number of values changes over time, it's probably better
> to walk through records (unless a return value can be an array).
>
> 4. the main data entry process involves creation of (but hardly any
> updating of) child records of the main form. This is presently done
> through a bound continuous form list of the child records linked to
> an unbound subform that displays the detail. This works just fine,
> as the list is noneditable, and the editable form is unbound. It
> was complicated to program, though. I could easily rewrite the
> retrieval and saving subroutines to operate server-side. I'd have
> to decide, though, if I want to operate as I have, with Add doing
> nothing but creating a new blank record and then Save updating the
> existing record. I could handle that with a single stored
> procedure, I guess, simply assigning the input values and if the PK
> input value is null, the stored procedure will insert instead of
> update. But that leads to a problem, because the OnCurrent of the
> list form drives the display of the unbound detail form. I might
> have to delink that. It would definitely require some rethinking of
> that part of the process.
>
> 5. the main form runs a test in the OnCurrent event to check for
> possible duplications. If there are possible duplicates, a
> collection of the PKs of the possible duplicate records is
> populated and a button RECONCILE DUPLICATES is set visible. When
> that button is clicked, a form to reconcile the duplicates is
> displayed, with two subforms, on the left the main record, on the
> right an empty subform that is populated by a listbox at the top
> which lists the PKs of the possible dups. It's OnUpdate event
> populates the other subform. You can then move all the data from
> one to the other (or on individual fields and subforms). I see no
> reason why this duplicate reconciliation would not work as is,
> since it's loading one record at a time, based on a PK. However,
> the code running in the OnCurrent of the main data form would
> probably be more efficient with SQL Server, though I'm not sure it
> would benefit from anything server-side (could work as a stored
> procedure, but probably just as easy to simply not bother).
>
> 6. the app includes a complex query-by-form interface that
> currently writes data to a temp table. I see no reason to change
> that, since the users click on checkmarks in the resultset to make
> selections for export, etc. This QBF interface has a complex class
> module that constructs the FROM and WHERE clauses dynamically based
> on the criteria, and then uses that to construct the SELECT used
> for the insert. How would that work in this case? Would I just pass
> the dynamic SQL and assume Jet will tell the server to do the
> selection and then Jet will do the insert into the temp table? This
> is one where I think I'd mostly leave it alone and see if Jet is
> smart enough to get it right. I see no reason why it wouldn't as
> all the SQL strings are as efficient as they can be in terms of
> joins and WHERE clauses. And the criteria are never based on
> functions, though a few of them have wildcards (though that's no
> issue except for changing the wildcard character).
>
> OK, any comments on those things? They are the ones I can think of
> off the top of my head, as somebody who plays around with SQL
> Server and reads a lot about it, but hasn't been called upon to
> build a full-fledged app with it.[/color]
Looks pretty complicated, but I still see nothing that on the face of it would
have to be rewritten. If some of these areas perform badly after the switch I
would look at them, but I wouldn't be surprised if most of it just worked.
If there any areas that are somewhat modular meaning that you could split them
out into a copy of the database with relative ease then I would take one that
you think might be a good text case, put it in a separate file and redo the
whole thing using ADO and then you can make a fair comparison to see what
benefits were realized.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
