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********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .86...

ri*********@hot mail.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.13 3.1.4>:

dX********@bw ay.net.invalid (David W. Fenton) wrote in
news:94****** *************** ******@24.168.1 28.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*********@hot mail.com (Rick Brandt) wrote in
<bo************ *@ID-98015.news.uni-berlin.de>:

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******* *************** *****@24.168.12 8.86...

ri*********@hot mail.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********@bwa y.net.invalid (David W. Fenton) wrote in
news:94******* *************** *****@24.168.12 8.86:

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

dX********@b way.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".)

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********@bwa y.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************* *****@btinterne t.com (Mike MacSween) wrote in
<3f************ ***********@new s.aaisp.net.uk> :

"David W. Fenton" <dX********@bwa y.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********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .86...

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

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******* *************** *****@24.168.12 8.86...

ri*********@hot mail.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********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .86...

mi************* *****@btinterne t.com (Mike MacSween) wrote in
<3f************ ***********@new s.aaisp.net.uk> :

"David W. Fenton" <dX********@bwa y.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.12 8.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?