SQL Server Conversion

[David W. Fenton]

Re: SQL Server Conversion

mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
<3fa56e5e$0$528 82$5a6aecb4@new s.aaisp.net.uk> :
[color=blue]
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>[color=green]
>> Rewriting as an ADP means starting from scratch in an
>> environment I know zilch about, and one that is buggy and
>> inconsistent.[/color]
>
>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.[/color]

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]

Re: SQL Server Conversion

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:9427AFC1Ed fentonbwaynetin vali@24.168.128 .86...[color=blue]
> rickbrandt2@hot mail.com (Rick Brandt) wrote in
> <bo1cnr$16vni9$ 1@ID-98015.news.uni-berlin.de>:
>[color=green]
> >"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> >news:9426BD745 dfentonbwayneti nvali@24.168.12 8.86...[color=darkred]
> >> rickbrandt2@hot mail.com (Rick Brandt) wrote in
> >> <bnv6gg$15vf47$ 1@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?[/color]
> >
> >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.[/color]
>
> 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.[/color]

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]

Re: SQL Server Conversion

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in
news:9427A7F2Ad fentonbwaynetin vali@24.168.128 .86:
[color=blue]
> Tell me about ADO in reports -- what can be accomplished there that
> can't be with ODBC linked tables?[/color]

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]

Re: SQL Server Conversion

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:9427E4CEAd fentonbwaynetin vali@24.168.128 .86...[color=blue]
> mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
> <3fa56e5e$0$528 82$5a6aecb4@new s.aaisp.net.uk> :
>[color=green]
> >"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> >[color=darkred]
> >> Rewriting as an ADP means starting from scratch in an
> >> environment I know zilch about, and one that is buggy and
> >> inconsistent.[/color]
> >
> >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.[/color]
>
> 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.[/color]

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]

Re: SQL Server Conversion

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.

dXXXfenton@bway .net.invalid (David W. Fenton) wrote in message news:<942596C8B dfentonbwayneti nvali@24.168.12 8.74>...[color=blue]
> 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?[/color]

[Lyle Fairfield]

Re: SQL Server Conversion

"Rick Brandt" <rickbrandt2@ho tmail.com> wrote in news:bo5lvh$17r aja$1@ID-
98015.news.uni-berlin.de:
[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> news:9427E4CEAd fentonbwaynetin vali@24.168.128 .86...[color=green]
>> mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
>> <3fa56e5e$0$528 82$5a6aecb4@new s.aaisp.net.uk> :
>>[color=darkred]
>> >"David W. Fenton" <dXXXfenton@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.[/color]
>>
>> 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.[/color]
>
> 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.[/color]

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.C ustomers AS Cst





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

[Lyle Fairfield]

Re: SQL Server Conversion

Lyle Fairfield <MissingAddress @Invalid.Com> wrote in
news:Xns942881D 7910C2FFDBA@130 .133.1.4:
[color=blue]
> 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.C ustomers AS Cst[/color]

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]

Re: SQL Server Conversion

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns9428822 A5306CFFDBA@130 .133.1.4...[color=blue]
> Lyle Fairfield <MissingAddress @Invalid.Com> wrote in
> news:Xns942881D 7910C2FFDBA@130 .133.1.4:
>[color=green]
> > 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.C ustomers AS Cst[/color]
>
> I neglected to point out the ADP Forms and Reports can be bound to Stored
> Procedures and Views.[/color]

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]

Re: SQL Server Conversion

rickbrandt2@hot mail.com (Rick Brandt) wrote in
<bo4b20$17qmuu$ 1@ID-98015.news.uni-berlin.de>:
[color=blue]
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>news:9427AFC1E dfentonbwayneti nvali@24.168.12 8.86...[color=green]
>> rickbrandt2@hot mail.com (Rick Brandt) wrote in
>> <bo1cnr$16vni9$ 1@ID-98015.news.uni-berlin.de>:
>>[color=darkred]
>> >"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in
>> >message news:9426BD745d fentonbwaynetin vali@24.168.128 .86...
>> >> rickbrandt2@hot mail.com (Rick Brandt) wrote in
>> >> <bnv6gg$15vf47$ 1@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.[/color]
>>
>> 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.[/color]
>
>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.[/color]

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]

Re: SQL Server Conversion

boston_77_4@hot mail.com (James Neumann) wrote in
<77809f1e.03110 30805.c63d303@p osting.google.c om>:
[color=blue]
>Is there any scope for presenting your client with a cost-benefit
>analysis to the various scenarios you and others here have
>mentioned?[/color]

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.
[color=blue]
>IMHO, based on what you have described, I really cannot see the
>justificatio n 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. . . .[/color]

Yes, but as a long-run investment, it may be worth something to
them.
[color=blue]
> . . . Also worth considering is the
>cost associated with setting up a dedicated SQL Server box, if the
>client is a small shop. . . .[/color]

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.
[color=blue]
> . . . 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.)[/color]

Online? Who said anything about online?

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

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]

Re: SQL Server Conversion

"Rick Brandt" <rickbrandt2@ho tmail.com> wrote in
news:bo6576$173 ate$1@ID-98015.news.uni-berlin.de:
[color=blue]
> "Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
> news:Xns9428822 A5306CFFDBA@130 .133.1.4...[color=green]
>> Lyle Fairfield <MissingAddress @Invalid.Com> wrote in
>> news:Xns942881D 7910C2FFDBA@130 .133.1.4:
>>[color=darkred]
>> > 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.C ustomers AS Cst[/color]
>>
>> I neglected to point out the ADP Forms and Reports can be bound to
>> Stored Procedures and Views.[/color]
>
> 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.[/color]

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]

Re: SQL Server Conversion

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" <rickbrandt2@ho tmail.com> wrote in message
news:bo5lvh$17r aja$1@ID-98015.news.uni-berlin.de...[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> news:9427E4CEAd fentonbwaynetin vali@24.168.128 .86...[color=green]
> > mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
> > <3fa56e5e$0$528 82$5a6aecb4@new s.aaisp.net.uk> :
> >[color=darkred]
> > >"David W. Fenton" <dXXXfenton@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.[/color]
> >
> > 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.[/color]
>
> 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
>
>[/color]