SQL Server Conversion

[David W. Fenton]

A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can't conceive of them adding more than than
10s of thousands of records, which won't change the current
performance profile at all).

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

Obviously, #1 is going to be a lot easier. Yes, I'm aware of a
number of places where I'll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.

My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.

The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I've thought of that one for quite some
time).

Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rick Brandt]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .

A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can't conceive of them adding more than than
10s of thousands of records, which won't change the current
performance profile at all).

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

Obviously, #1 is going to be a lot easier. Yes, I'm aware of a
number of places where I'll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.

My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.

The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I've thought of that one for quite some
time).

Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?

I can only tell you that I have managed a large project with SQL Server
using ODBC/DAO for several years and find that I have no problems at all
with performance. I mostly use ODBC linked tables, but where I see a
performance advantage, I use Pass-Throughs and Stored Procedures. You'd be
amazed just how infrequently I need to do either one though.

I find that Jet/ODBC does a very good job at passing the SQL to the server
even when I don't go out of my way to force it. When I made the conversion
I did exactly as you described. I changed from linked Access tables to
ODBC linked SQL Server tables and then (without changing anything else) I
tested the app.

I did have to modify several things to "just make them work" and several
others to get performance up to par, but again, it was not so many things
as you might imagine. Once you achieve that position you can put the app
on-line and at a more leisurely pace make a closer examination of where you
might find some performance gains with additional changes.

My opinion is that you really don't need "all of the processing" on the
server so much as "all of the selecting". As long as the server is not
returning more rows/columns than necessary and bogging down the network,
any other processing will be about as fast on the client as on the server.
Nowadays, most client PCs have way more CPU than required anyway.

This strategy allows you to retain all DAO code (I've never written a line
of ADO) and stay with what's familiar. To me if this is an important app
that's been in long term use, then you will have more than enough to worry
about by introducing a new back end. Introducing a whole host of other
changes at the same time (that might be completely unnecessary) just clouds
the waters when you do need to chase a problem down.

About a year ago I went through my entire app and looked at every single
query to see which could be changed to Pass-Throughs or SPs. I found about
three (of 100 or so) that performed any better as a PT. And in my
experience SPs are more of an answer to security problems than performance
issues. They are great if you need to do a bunch of conditional
processing, use temp tables, and the like, but I just don't see many cases
where I can't pull in the required rows and do all of that same processing
in VBA just as quickly (while being much easier for me to write).

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.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rich P]

It just seems to me that you could import all the Access tables to Sql
Server, establish your relationships in sql Server, add some triggers
for the data entry, but I would definitely go with ADO for the data
entry. And for error handling you will need stored procedures to commit
or rollback stuff on the sql server end (depending on how user intensive
your project is). But the robustness and performance of sql server will
more than repay you for your efforts. People at my place had me connect
them with ODBC for adhoc stuff. This seems to work fine. But I
wouldn't automate anything with ODBC.

The rule of thumb that has worked for me between sql Server and Access
is that ODBC works pretty well for adhoc queries. But for serious data
entry through Access or Web forms, and data crunching and automation of
data reporting, I would stick with sp's and ADO.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ragnar Midtskogen]

We have had good experiences with just upsizing the back end
for Access 2000 applications then using ODBC linked tables.
We got the best results by using Access 2002 for the upsizing,
just upsizing the structure, then using the SQL Server Data
Transformation Service to port the data.
In one app there are some partially unbound forms where I used
ADO to move the data the data between the forms and the
tables, and occasionally VBA Boolean data (Checkboxes)
did not translate properly to SQL Server Bit data type.
I wrote some SQL Server Stored Procs to do the translation
explicitly and that appears to have fixed the problem.

Ragnar

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .

A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can't conceive of them adding more than than
10s of thousands of records, which won't change the current
performance profile at all).

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

Obviously, #1 is going to be a lot easier. Yes, I'm aware of a
number of places where I'll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.

My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.

The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I've thought of that one for quite some
time).

Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

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.

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.

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?

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?

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.

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).

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.

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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rp*****@aol.com (Rich P) wrote in
<3f*********************@news.frii.net>:

It just seems to me that you could import all the Access tables to
Sql Server, establish your relationships in sql Server, add some
triggers for the data entry, but I would definitely go with ADO
for the data entry. And for error handling you will need stored
procedures to commit or rollback stuff on the sql server end
(depending on how user intensive your project is). But the
robustness and performance of sql server will more than repay you
for your efforts. People at my place had me connect them with
ODBC for adhoc stuff. This seems to work fine. But I wouldn't
automate anything with ODBC.

The rule of thumb that has worked for me between sql Server and
Access is that ODBC works pretty well for adhoc queries. But for
serious data entry through Access or Web forms, and data crunching
and automation of data reporting, I would stick with sp's and ADO.

You are saying that you would take an elaborate app that has been
in production use for 3 years (though not in the latest version)
and rewrite everything in it from the ground up to use ADO?

You have got to be completely nuts.

You know, there was a time not very long ago when ODBC was the only
way to connect to SQL Server from Access and people wrote lots of
very efficient applications that weren't slow at all. Maybe you
just don't know how to do that.

I'm pretty sure I can get a working app with a SQL Server back end
with ODBC a whole lot quicker than if I decided to rewrite
everything from scratch.

That said, were I starting the app right now to work against SQL
Server, then I'd go with ADO.

As to data entry, that's the one area where I'd consider ADO, at
least in one of the data entry areas (the one that's already
unbound). But I'm not sure that a stored procedure with ODBC would
work any less well.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rick Brandt]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

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.
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.

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.
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?
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.
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?
Other than the need to disambiguate a few object types I don't know of any
reasons not to use both.

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.
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.

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)
Yes, but again a plain ol' DAO Recordset with zero rows returned (just for
adding records) would be imperceptibly different in terms of performance,
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.
You can't use Pass-Throughs for sub-reports and sub-forms. They work fine for
top level forms and reports though.

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.

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

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.74:

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

ADP ?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

>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.
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.

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.

That's the rule of thumb I've been using in my thinking about it in
the list of items below.

But can't ADO do quite a few things server-side that Jet/ODBC won't
do server-side?
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.
It should be more manageable with ODBC as the indexes won't have to
be dragged across the LAN. For tables with 350K and 500K records
(the main parent and child tables, respectively) the PK index alone
is a pretty significant chunk of data. I assume, though, that Jet
pulls only as many index pages as it needs, though, and caches what
it can. It is truly remarkable how efficient it really is.

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?

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.

Well, that's my gut feeling as well, but I don't have anything to
back it up, other than a few people I know who were able to build
perfectly efficient and stable ODBC apps before ADO was an option.

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?

Other than the need to disambiguate a few object types I don't
know of any reasons not to use both.

I've been disambiguating for a long time.

And I wouldn't create a reference to ADO, anyway -- I'd use late
binding.

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.

Well, writing SPs in T-SQL _is_ harder (IMO) than using VBA code,

I certainly understand that. But if it's going to be a SQL Server
back end, I need to know how to do it, sooner rather than later.
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.
I've looked at examples in various books and mucked around with it
myself and it doesn't look all that complicated, thought it's also
not a triviality.

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)

Yes, but again a plain ol' DAO Recordset with zero rows returned
(just for adding records) would be imperceptibly different in
terms of performance,

But it holds locks on the server side, no? And I'd like to avoid
that.

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.

You can't use Pass-Throughs for sub-reports and sub-forms. They
work fine for top level forms and reports though.

OK, then a passthrough for the main form's recordsource, set on
each FIND operation would work fine. I wasn't worrying about the
subforms, as I don't see any reason why those (which are filtered
on their main foreign key) wouldn't work straightaway with the ODBC
conversion.

[further examples deleted]

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.

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.

A couple of them perform badly already, so I'd like to hand those
off to the server to see if they get better.
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.

Well, none of the above examples was at all suggesting rewriting as
ADO -- I was just discussing where to convert to something
server-side (a passthrough, a stored procedure).

Sounds like your recommendation is to upsize and test and then work
from there.

That's definitely a philosophy that's completely inline with my
usual practices.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.7 4:

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

ADP ?

Why?

The app exists as an MDB.

It's been tested and in use for years, with only a few small parts
with anything in them that is less than a year old.

Rewriting as an ADP means starting from scratch in an environment I
know zilch about, and one that is buggy and inconsistent.

Why in the world would you suggest converting an existing complex
application to an ADP? The only scenario I can think of would be if
it was going from the current 15-user population to, say, 1500.
That simply isn't going to happen, ever. And I'm not even sure that
in that case it would justified, either.

You hate Jet so much that you'd suggest rebuilding the whole app
from scratch? You'd suggest that they spend another $50K on this
app to get something that I'm not even certain they need?

These are questions, so I don't know if you feel that way or not,
but if you do, well, I really don't think very highly of your
degree of intelligence if you believe such expenditures are
justified, in any case with an existing MDB of any degree of
complexity.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rick Brandt]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

But can't ADO do quite a few things server-side that Jet/ODBC won't
do server-side?

I've heard that it can do some things "closer to the bolts", but whether there
are things that it can do that ODBC/DAO cannot do at all, I don't know. If so,
then they're processes which I have yet to have a need for.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.86:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.74 :

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

ADP ?

Why?

The app exists as an MDB.

It's been tested and in use for years, with only a few small parts
with anything in them that is less than a year old.

Rewriting as an ADP means starting from scratch in an environment I
know zilch about, and one that is buggy and inconsistent.

Why in the world would you suggest converting an existing complex
application to an ADP? The only scenario I can think of would be if
it was going from the current 15-user population to, say, 1500.
That simply isn't going to happen, ever. And I'm not even sure that
in that case it would justified, either.

You hate Jet so much that you'd suggest rebuilding the whole app
from scratch? You'd suggest that they spend another $50K on this
app to get something that I'm not even certain they need?

These are questions, so I don't know if you feel that way or not,
but if you do, well, I really don't think very highly of your
degree of intelligence if you believe such expenditures are
justified, in any case with an existing MDB of any degree of
complexity.

I think it's always a good idea to encourage the client to spend as much as
possible.
I just don't understand how converting to ADO will handle the conversion of
the backend to MS-SQL. (And so I thought you had made a TYPO and had
intended to write "ADP".)
I realize that your main form is unbound and so ADO could be a solution
there. Are there other forms in the application? What about reports?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

ri*********@hotmail.com (Rick Brandt) wrote in
<bo*************@ID-98015.news.uni-berlin.de>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86. ..

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

But can't ADO do quite a few things server-side that Jet/ODBC
won't do server-side?

I've heard that it can do some things "closer to the bolts", but
whether there are things that it can do that ODBC/DAO cannot do at
all, I don't know. If so, then they're processes which I have yet
to have a need for.

What I meant was that you could write client-side SQL that would
get processed server-side in cases where the same SQL with DAO/ODBC
would get processed by Jet. ADO was, I thought, built with the idea
that it would be intelligent enough to do this kind of thing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.8 6:

Mi************@Invalid.Com (Lyle Fairfield) wrote in
<Xn*******************@130.133.1.4>:

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.7 4:

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

ADP ?
Why?

The app exists as an MDB.

It's been tested and in use for years, with only a few small
parts with anything in them that is less than a year old.

Rewriting as an ADP means starting from scratch in an
environment I know zilch about, and one that is buggy and
inconsistent.

Why in the world would you suggest converting an existing
complex application to an ADP? The only scenario I can think of
would be if it was going from the current 15-user population to,
say, 1500. That simply isn't going to happen, ever. And I'm not
even sure that in that case it would justified, either.

You hate Jet so much that you'd suggest rebuilding the whole app
from scratch? You'd suggest that they spend another $50K on this
app to get something that I'm not even certain they need?

These are questions, so I don't know if you feel that way or
not, but if you do, well, I really don't think very highly of
your degree of intelligence if you believe such expenditures are
justified, in any case with an existing MDB of any degree of
complexity.

I think it's always a good idea to encourage the client to spend
as much as possible.
I just don't understand how converting to ADO will handle the
conversion of the backend to MS-SQL. (And so I thought you had
made a TYPO and had intended to write "ADP".)

Well, I only meant using ADO where I'd use DAO, since ADO is not
necessary anywhere else.
I realize that your main form is unbound . . .
Er, no, it's not unbound. It has a recordsource that is determined
by whatever the user types into the FIND textbox.
. . . and so ADO could be a
solution there. . . .
Is ADO a "solution" for a form with an assigned recordsource? The
criteria are always on LastName [and FirstName], but can also be on
FirstName only (I allow them to type "LastName, FirstName" or even
", FirstName" or "Sm, J" for partial matches). I don't see why ODBC
tablelinks would be any less efficient in this case, as Jet is
going to hand off the filtering to the server with something so
simple as this.
. . . Are there other forms in the application? What
about reports?

Tell me about ADO in reports -- what can be accomplished there that
can't be with ODBC linked tables?

And my apologies for assuming the worst about your question and
getting snippy.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike MacSween]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message

Rewriting as an ADP means starting from scratch in an environment I
know zilch about, and one that is buggy and inconsistent.

I'm interested you say that David. Somewhere fairly high on my 'next thing
to learn more about' is ADPs. It _appears_ to be a logical move, for an
Access developer, towards client/server. What problems have you found/heard
about, with ADPs.

Yours, Mike

[David W. Fenton]

mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message

Rewriting as an ADP means starting from scratch in an
environment I know zilch about, and one that is buggy and
inconsistent.

I'm interested you say that David. Somewhere fairly high on my
'next thing to learn more about' is ADPs. It _appears_ to be a
logical move, for an Access developer, towards client/server. What
problems have you found/heard about, with ADPs.

Were it a brand-new application with a high seat count (50+) and
SQL Server guaranteed as the back end and heavy editing, then I'd
consider learning how to use an ADP.

So far as I can see, all an ADP gets you is a "Jetless"
environment, which is not worth too much, so far as I can see.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Rick Brandt]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

ri*********@hotmail.com (Rick Brandt) wrote in
<bo*************@ID-98015.news.uni-berlin.de>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86. ..

ri*********@hotmail.com (Rick Brandt) wrote in
<bn*************@ID-98015.news.uni-berlin.de>:

But can't ADO do quite a few things server-side that Jet/ODBC
won't do server-side?

I've heard that it can do some things "closer to the bolts", but
whether there are things that it can do that ODBC/DAO cannot do at
all, I don't know. If so, then they're processes which I have yet
to have a need for.

What I meant was that you could write client-side SQL that would
get processed server-side in cases where the same SQL with DAO/ODBC
would get processed by Jet. ADO was, I thought, built with the idea
that it would be intelligent enough to do this kind of thing.

Someone can correct me if I'm wrong, but my understanding is that there is SQL
that cannot be run on the server regardless of whether one uses DAO or ADO and
there is SQL that will "usually* be passed to the server for processing
regardless of whether one uses DAO or ADO. Examples of the former being a query
joining to a local table or which uses Functions or syntax that is incompatible
with the server's flavor of SQL and the latter being just about everything else.
With either library you should be able to force server-side processing by using
a pass-through.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Lyle Fairfield]

dX********@bway.net.invalid (David W. Fenton) wrote in
news:94***************************@24.168.128.86:

Tell me about ADO in reports -- what can be accomplished there that
can't be with ODBC linked tables?

I'm not so familiar with using ODBC linked tables and I expect that ADO has
no specific application to reports. In ACXP I was able to set a report's
recordset to an ADO recordset based on SQL UNIONs and do some things I
considered unusual via the Detail_Format and other procedures. This allowed
me to draw shapes and place and summarize data at runtime according to the
nature of the data. I used ADO because I had to write quite a complicated
Stored Procedure to get the Data. But I doubt there is any general need for
this kind of thing, and the coding is obscure enough that it could be called
"kludgey".
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Rick Brandt]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message

Rewriting as an ADP means starting from scratch in an
environment I know zilch about, and one that is buggy and
inconsistent.

I'm interested you say that David. Somewhere fairly high on my
'next thing to learn more about' is ADPs. It _appears_ to be a
logical move, for an Access developer, towards client/server. What
problems have you found/heard about, with ADPs.

Were it a brand-new application with a high seat count (50+) and
SQL Server guaranteed as the back end and heavy editing, then I'd
consider learning how to use an ADP.

So far as I can see, all an ADP gets you is a "Jetless"
environment, which is not worth too much, so far as I can see.

I've only dabbled with ADPs, but one thing I saw which was another "con"
was that apparently the rule is "one ADP = one Database". Can someone
correct me if I'm wrong here? The first thing you are as asked to do is
indicate "the" database to use. Well our SQL Server has about a dozen
databases on it and many apps require connecting to more than one of them.
Is this not possible with an ADP?

I mean I'm sure I can connect to other dbs in code and such, but as far as
the SQL Server objects being visible within the ADP project itself can
there be only one? This seems like a pretty big restriction if so.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[James Neumann]

Is there any scope for presenting your client with a cost-benefit
analysis to the various scenarios you and others here have mentioned?

IMHO, based on what you have described, I really cannot see the
justification to convert. Why? Regardless of the choice of
conversion method, there would be a material expenditure of time to do
the conversion, and test it. Also worth considering is the cost
associated with setting up a dedicated SQL Server box, if the client
is a small shop. If they are a department within a large company,
they may incur a significant overhead charge to have a SQL Server
database online. (In several cases, I've done projects in Access as
opposed to VB/SQL Server for this very reason.)

I haven't spoken to the technical aspects of this, for which I
apologize - others have covered this better than I could.

dX********@bway.net.invalid (David W. Fenton) wrote in message news:<94***************************@24.168.128.74> ...

A client is panicking about their large Access application, which
has been running smoothly with 100s of thousands of records for
quite some time. They have a big project in the next year that will
lead to a lot of use of the database and the adding of quite a lot
of new data (though I can't conceive of them adding more than than
10s of thousands of records, which won't change the current
performance profile at all).

If there is a SQL Server conversion, my question is this:

1. just do an ODBC upsizing, OR

2. convert the whole damned thing to ADO.

Obviously, #1 is going to be a lot easier. Yes, I'm aware of a
number of places where I'll need to drastically alter the way the
application works (though it nowhere loads any large number of
records, of course). And I can easily think of several areas where
server-side processing will vastly improve performance.

My gut says to change as little as necessary, and just go with ODBC
linked tables and then fix all the things that are inefficient when
converted. This means a lot of stored procedures.

The one unbound form (where the highest volume of data entry takes
place) is now all done with DAO. Perhaps it would benefit from
conversion to ADO? Indeed, it is part of a UI with a bound list
subform and an unbound detail subform, so I could use a single ADO
recordset for the recordsource of both (or are form recordsources
DAO by default?), perhaps (I've thought of that one for quite some
time).

Any suggestions as to how to answer this question for myself? What
specific things should I look at to help evaluate the question?

[Lyle Fairfield]

"Rick Brandt" <ri*********@hotmail.com> wrote in news:bo5lvh$17raja$1@ID-
98015.news.uni-berlin.de:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:

>"David W. Fenton" <dX********@bway.net.invalid> wrote in message
>
>> Rewriting as an ADP means starting from scratch in an
>> environment I know zilch about, and one that is buggy and
>> inconsistent.
>
>I'm interested you say that David. Somewhere fairly high on my
>'next thing to learn more about' is ADPs. It _appears_ to be a
>logical move, for an Access developer, towards client/server. What
>problems have you found/heard about, with ADPs.

Were it a brand-new application with a high seat count (50+) and
SQL Server guaranteed as the back end and heavy editing, then I'd
consider learning how to use an ADP.

So far as I can see, all an ADP gets you is a "Jetless"
environment, which is not worth too much, so far as I can see.

I've only dabbled with ADPs, but one thing I saw which was another "con"
was that apparently the rule is "one ADP = one Database". Can someone
correct me if I'm wrong here? The first thing you are as asked to do is
indicate "the" database to use. Well our SQL Server has about a dozen
databases on it and many apps require connecting to more than one of them.
Is this not possible with an ADP?

I mean I'm sure I can connect to other dbs in code and such, but as far as
the SQL Server objects being visible within the ADP project itself can
there be only one? This seems like a pretty big restriction if so.

Well Designed ADP-SQL Server Apps will edit, add and delete data via Stored
Procedures and/or Views. Stored Procedures and Views can access tables from
any database on the server (or linked server for that matter) given adequate
permissions.

Example from Books On Line:
SELECT Cst.FirstName, Cst.LastName
FROM Northwind.dbo.Customers AS Cst

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

Well Designed ADP-SQL Server Apps will edit, add and delete data via
Stored Procedures and/or Views. Stored Procedures and Views can access
tables from any database on the server (or linked server for that
matter) given adequate permissions.

Example from Books On Line:
SELECT Cst.FirstName, Cst.LastName
FROM Northwind.dbo.Customers AS Cst

I neglected to point out the ADP Forms and Reports can be bound to Stored
Procedures and Views.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Rick Brandt]

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

Well Designed ADP-SQL Server Apps will edit, add and delete data via
Stored Procedures and/or Views. Stored Procedures and Views can access
tables from any database on the server (or linked server for that
matter) given adequate permissions.

Example from Books On Line:
SELECT Cst.FirstName, Cst.LastName
FROM Northwind.dbo.Customers AS Cst

I neglected to point out the ADP Forms and Reports can be bound to Stored
Procedures and Views.

I see, so I guess if I used the "major" database for tables and then used
SPs and Views for those in the other databases I would be in good shape.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

ri*********@hotmail.com (Rick Brandt) wrote in
<bo*************@ID-98015.news.uni-berlin.de>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86. ..

ri*********@hotmail.com (Rick Brandt) wrote in
<bo*************@ID-98015.news.uni-berlin.de>:

>"David W. Fenton" <dX********@bway.net.invalid> wrote in
>message news:94***************************@24.168.128.86.. .
>> ri*********@hotmail.com (Rick Brandt) wrote in
>> <bn*************@ID-98015.news.uni-berlin.de>:
>>
>> But can't ADO do quite a few things server-side that Jet/ODBC
>> won't do server-side?
>
>I've heard that it can do some things "closer to the bolts",
>but whether there are things that it can do that ODBC/DAO
>cannot do at all, I don't know. If so, then they're processes
>which I have yet to have a need for.

What I meant was that you could write client-side SQL that would
get processed server-side in cases where the same SQL with
DAO/ODBC would get processed by Jet. ADO was, I thought, built
with the idea that it would be intelligent enough to do this
kind of thing.

Someone can correct me if I'm wrong, but my understanding is that
there is SQL that cannot be run on the server regardless of
whether one uses DAO or ADO and there is SQL that will "usually*
be passed to the server for processing regardless of whether one
uses DAO or ADO. Examples of the former being a query joining to
a local table or which uses Functions or syntax that is
incompatible with the server's flavor of SQL and the latter being
just about everything else. With either library you should be able
to force server-side processing by using a pass-through.

Well, my understanding is that ADO is very smart and in certain
kinds of nonupdatable statements, it will write individual new SQL
statements to update the rows that cannot be updated in the actual
SQL that you've given it. That's way beyond anything DAO can do.

On the other hand, it is also something that kind of scares me, as
you have little control over how it decides to do things behind the
scenes. If it guesses wrong, you've got a problem.

But if it makes the process transparent, it can be a useful tool.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

bo*********@hotmail.com (James Neumann) wrote in
<77*************************@posting.google.com> :

Is there any scope for presenting your client with a cost-benefit
analysis to the various scenarios you and others here have
mentioned?
Well, in the long run, I do think that it would be better for the
data to be in SQL Server, given the quantity of it and the kind of
use it gets. What happens is that it comes under very heavy use
during short periods of time. They have had a few corruptions of
the data file with A2K, but none since I got all workstations on
SR1a and Jet SP6 (that's well over a year now), and they never lost
even one byte of data even then -- it was just inconvenient.
IMHO, based on what you have described, I really cannot see the
justification to convert. Why? Regardless of the choice of
conversion method, there would be a material expenditure of time
to do the conversion, and test it. . . .
Yes, but as a long-run investment, it may be worth something to
them.
. . . Also worth considering is the
cost associated with setting up a dedicated SQL Server box, if the
client is a small shop. . . .
They have the equipment available. And SQL Server (and maybe even
new equipment) is likely to be donated by, well, let's just say by
one of the largest software companies in the world. Ahem.
. . . If they are a department within a large
company, they may incur a significant overhead charge to have a
SQL Server database online. (In several cases, I've done projects
in Access as opposed to VB/SQL Server for this very reason.)
Online? Who said anything about online?

And no, it's not a large company. It's a small consulting
organization.
I haven't spoken to the technical aspects of this, for which I
apologize - others have covered this better than I could.

I've got all these aspects in hand already. They wouldn't be
considering the SQL Server conversion at this point if a certain
large company had not made overtures about donating software (and
maybe equipment) for a certain large project taking place in New
York City in early September 2004. Ahem.

They have had Small Business Server as long as I've worked with
them (since May 2000), and I've spoken to them about spending time
($$$) evaluating the cost/benefit ratio of converting the back end
to SQL Server (by testing a raw upsizing and seeing what caused
problems and what gave benefits). They've recently abandoned the
Small Business Server package (they weren't using anything but
Exchange) for a new Win2K Server without SBS (just Exchange).

The offer of the software donation has raised the issue again.

I have told them that it would be a major cost and a large project
to convert. But I didn't tell them it would be without any benefits
at all, which I do not believe.

In a certain sense, it's one of those CYA situations. I can imagine
that if they brought in an outside database "expert" they'd be told
that they were in horrid danger of losing all their data because
they are depending on a toy database, etc., etc. They really are on
the threshold of the point at which it really becomes cost- and
performance-effective to switch over to a SQL Server back end.

But I wouldn't have any problems myself if they stay with the Jet
back end for a while. I see no reason why things should suddenly
become unreliable. The time when they switched servers (last June)
would have been the the likely point for things to go haywire and
nothing happened at all. Indeed, the migration of the database
application to the new server was the one thing that went off
without a hitch (I just told the sysadmins to put the database in a
top-level share named the same as on the old server and give the
new server the same name as the old one).

I'd be happy either way. Indeed, I'd love to see this project
converted to SQL Server as it would finance my training in using
SQL Server beyond my current at-home dabbling in it. But I'm
certainly not going to push it just because of that.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:bo*************@ID-98015.news.uni-berlin.de:

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

> Well Designed ADP-SQL Server Apps will edit, add and delete data via
> Stored Procedures and/or Views. Stored Procedures and Views can
> access tables from any database on the server (or linked server for
> that matter) given adequate permissions.
>
> Example from Books On Line:
> SELECT Cst.FirstName, Cst.LastName
> FROM Northwind.dbo.Customers AS Cst

I neglected to point out the ADP Forms and Reports can be bound to
Stored Procedures and Views.

I see, so I guess if I used the "major" database for tables and then
used SPs and Views for those in the other databases I would be in good
shape.

IMO it's a good idea to use SPs and Views for everything.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Bradley]

From my understanding each user must have an Access licence to use an ADP on
an Intranet?!? I've avoided ADPs. I've been using DAO for over 8 years and
see no compelling reason to change just yet (except ADO when I'm doing
ASP... got to love accronyms:)

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bo*************@ID-98015.news.uni-berlin.de...

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

mi******************@btinternet.com (Mike MacSween) wrote in
<3f***********************@news.aaisp.net.uk>:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message

> Rewriting as an ADP means starting from scratch in an
> environment I know zilch about, and one that is buggy and
> inconsistent.

I'm interested you say that David. Somewhere fairly high on my
'next thing to learn more about' is ADPs. It _appears_ to be a
logical move, for an Access developer, towards client/server. What
problems have you found/heard about, with ADPs.

Were it a brand-new application with a high seat count (50+) and
SQL Server guaranteed as the back end and heavy editing, then I'd
consider learning how to use an ADP.

So far as I can see, all an ADP gets you is a "Jetless"
environment, which is not worth too much, so far as I can see.

I've only dabbled with ADPs, but one thing I saw which was another "con"
was that apparently the rule is "one ADP = one Database". Can someone
correct me if I'm wrong here? The first thing you are as asked to do is
indicate "the" database to use. Well our SQL Server has about a dozen
databases on it and many apps require connecting to more than one of them.
Is this not possible with an ADP?

I mean I'm sure I can connect to other dbs in code and such, but as far as
the SQL Server objects being visible within the ADP project itself can
there be only one? This seems like a pretty big restriction if so.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com