Upsize to SQL Server

[dreadnought8@hotmail.com]

I have a client who wishes to upsize an A97 system from Jet backend to
SQL Server 2000.
They have up to 4 users.
Will they need to buy the minimal 5-seat SQL Server product, or would
it be cheaper to upsize the front end to A2K, A2K2 or A2K3 and use the
version of SQL Server provided with that?
The system uses local tables extensively and I would not want to
disturb this.
Enterprise manager would be a requirement.
TIA
Terry Bell

[Br@dley]

Re: Upsize to SQL Server

dreadnought8@ho tmail.com wrote:[color=blue]
> I have a client who wishes to upsize an A97 system from Jet backend to
> SQL Server 2000.[/color]

What is their reasoning for doing this?
[color=blue]
> They have up to 4 users.
> Will they need to buy the minimal 5-seat SQL Server product, or would
> it be cheaper to upsize the front end to A2K, A2K2 or A2K3 and use the
> version of SQL Server provided with that?
> The system uses local tables extensively and I would not want to
> disturb this.
> Enterprise manager would be a requirement.
> TIA
> Terry Bell[/color]


Why do you need to use local tables "extensivel y" ? I assume you'll be
going with a MDB front-end and attach to the SQL server
tables/views/etc?

Using MSDE may not work well enough as it allows 5 connections, not
users (as far as I understood it). Also, if you want Enterprise manager
it doesn't come with MSDE (again, as far as I know).
--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

[Larry Linson]

Re: Upsize to SQL Server

<dreadnought8@h otmail.com> wrote
[color=blue]
> I have a client who wishes to upsize an A97 system from
> Jet backend to SQL Server 2000. They have up to 4 users.
> Will they need to buy the minimal 5-seat SQL Server
> product, or would it be cheaper to upsize the front end to
> A2K, A2K2 or A2K3 and use the version of SQL Server
> provided with that?[/color]

I have to ask the same question that Br@dley asked: with so few users, why
do they want to move to SQL Server. It's not that there can't be valid
reasons, but that so many people have decided to make such a move for
invalid ones, based on misinformation.

IIRC, a version of MSDE also came with Access 97, but I do not know which
version of SQL Server it was based on.
[color=blue]
> The system uses local tables extensively and I would not
> want to disturb this.[/color]

The only approach that would make it difficult or impossible to use local
tables would be to reimplement into an ADP. But, current thinking at
Microsoft is that an MDB with ODBC connection is, generally, a better choice
than ADP with OleDB. Local tables can be very useful for the performance
they provide
[color=blue]
> Enterprise manager would be a requirement.[/color]

Although MSDE is a "stripped down" edition of MS SQL Server with some
built-in performance limits, no version of it comes with Enterprise Manager.
But, both the Office 2000 and Office 2002 Developer Editions come with the
Development Edition of MS SQL Server, which does have EM. The license does
not permit the Developer Edition to be used for production, but you can do
your development with it, and then run production on MSDE.

If, in fact, there is a valid reason for changing to an SQL Server back end,
you might want to consider SQL Server 2005 Express edition -- Microsoft just
committed that the Express editions would be available free of charge for at
least a year. I do not know just what additional software you'd have to get
to have Enterprise Manager or the equivalent.

Larry Linson
Microsoft Access MVP

[Steve Jorgensen]

Re: Upsize to SQL Server

On Mon, 14 Nov 2005 04:11:48 GMT, "Br@dley" <brad@usenet.or g> wrote:
[color=blue]
>dreadnought8@h otmail.com wrote:[color=green]
>> I have a client who wishes to upsize an A97 system from Jet backend to
>> SQL Server 2000.[/color]
>
>What is their reasoning for doing this?
>[color=green]
>> They have up to 4 users.
>> Will they need to buy the minimal 5-seat SQL Server product, or would
>> it be cheaper to upsize the front end to A2K, A2K2 or A2K3 and use the
>> version of SQL Server provided with that?
>> The system uses local tables extensively and I would not want to
>> disturb this.
>> Enterprise manager would be a requirement.
>> TIA
>> Terry Bell[/color]
>
>
>Why do you need to use local tables "extensivel y" ? I assume you'll be
>going with a MDB front-end and attach to the SQL server
>tables/views/etc?
>
>Using MSDE may not work well enough as it allows 5 connections, not
>users (as far as I understood it). Also, if you want Enterprise manager
>it doesn't come with MSDE (again, as far as I know).[/color]

I believe that's 5 concurrent tasks, which is not too bad a restriction since
tasks happen in short bursts. I think MSDE is supposed to perform well with
up to 10 users in typical usage scenarios.

I reiterate your quesiton though about why they want to move to SQL server.

[david epsom dot com dot au]

Re: Upsize to SQL Server

All of our complex Transactions (BeginTrans, CommitTrans)
are broken in A2K/SQL Server. Jet 4 makes multiple
connections inside the transactions, and they block
each other.

On the other hand, trouble with Jet 4/Windows 2000
Server was a major reason for upsizing for many people.
(A lot of those problems have been fixed or there are
work-arounds)

A2K/Jet 4 allows you to append to SQL Server identity
fields, like you can do in A97/mdb: that is much more
difficult with A97/SQL Server (and broken in A2K/mdb)
In general, 2003 is better than 2000 is better than
A97 with SQL Server, apart from the broken transactions.

The A97 upsizing wizard does not work with SQL Server
2000+ (because the version number jumped from 7 to 2000),
but it can be fixed. There are other small improvements
in the A2000+ upsizing wizards.

The A2000+ upsizing wizards try to create nVarChar
fields which don't work with A97, but I think that can
be fixed (at least in some versions I think it is an
option).

A97 Developer Edition did not come with a version of
MSDE, and is unavailable.

MSDE all versions (including 2005 xpress) can generally
handle 4 users, but notice that the governor is different
in different versions. The governor may cause additional
problems with transactions, but see above anyway.

As I write, my co-worker is bitching about the fact that
A2000 IDE has closed unexpectedly and lost his work for
the third time today, and every day for the last two weeks.

My advice: If using complex transactions stick to MDB. If
you want to try transactions stick with A97. If you want to
try complex transactions with Jet 4, budget for a complete
re-write of transactions as T-SQL stored procedures.

If going to SQL Server, buy the cheapest product that will
give you the copy of Enterprise Manager that you want,
(?large download if you only want the development copy?).

If you want to go with SQL Server Express and "SQL Server
Management Studio Express", just note that these are
..NET 2.0. You may have problems using or installing any
..NET 1.1 products you may have, and my problems reached
back to Visual 6 product installation as well

(david)

<dreadnought8@h otmail.com> wrote in message
news:1131940018 .335406.89540@g 44g2000cwa.goog legroups.com...[color=blue]
>I have a client who wishes to upsize an A97 system from Jet backend to
> SQL Server 2000.
> They have up to 4 users.
> Will they need to buy the minimal 5-seat SQL Server product, or would
> it be cheaper to upsize the front end to A2K, A2K2 or A2K3 and use the
> version of SQL Server provided with that?
> The system uses local tables extensively and I would not want to
> disturb this.
> Enterprise manager would be a requirement.
> TIA
> Terry Bell
>[/color]

[Trevor Best]

Re: Upsize to SQL Server

(assuming A2K2/Jet4 is same behavior as A2K/Jet4)

david epsom dot com dot au wrote:[color=blue]
> All of our complex Transactions (BeginTrans, CommitTrans)
> are broken in A2K/SQL Server. Jet 4 makes multiple
> connections inside the transactions, and they block
> each other.[/color]

Except in the case see below...
[color=blue]
> A2K/Jet 4 allows you to append to SQL Server identity
> fields,[/color]

Once. It turns IDENTITY_INSERT ON but forgets to turn it off again
afterwards so if try a second table it will fail. the second table
appended unfortunately gets the same connection as the first and issuing
a pass through "set identity_insert off" statement doesn't work as that
goes on another connection where it isn't turned on resulting in an error.
[color=blue]
> The A2000+ upsizing wizards try to create nVarChar
> fields which don't work with A97, but I think that can
> be fixed (at least in some versions I think it is an
> option).[/color]

Not that they don't work, just that A97 hasn't a clue what they are so
treats them as memo so sorting/grouping won't work.

[lylefair]

Re: Upsize to SQL Server

IMO there is no good way to use MS-Access with MS-SQL Server.
A satisfactory way might be to use a disconnected ADP (a disconnected
MDB is unlikely to be a lot different) and unbound Forms and Reports,
or Forms and Reports bound to ADO recordsets (often disconnected). But
this method does not fit with the RAD notion of Access.
I've not worked with MDB-ODBC-MS-SQL Server. Perhaps, I should. I feel
that with such a model, it will not be easy for me to use the power of
T-SQL; I feel uneasy about interjecting an additional connection layer
between the app and the db; I feel uneasy about the many posts here
about problems associated with this model.

[Bernard Peek]

Re: Upsize to SQL Server

In message <1131968189.797 295.285650@g14g 2000cwa.googleg roups.com>,
lylefair <lylefairfield@ aim.com> writes[color=blue]
>IMO there is no good way to use MS-Access with MS-SQL Server.
>A satisfactory way might be to use a disconnected ADP (a disconnected
>MDB is unlikely to be a lot different) and unbound Forms and Reports,
>or Forms and Reports bound to ADO recordsets (often disconnected). But
>this method does not fit with the RAD notion of Access.
>I've not worked with MDB-ODBC-MS-SQL Server. Perhaps, I should. I feel
>that with such a model, it will not be easy for me to use the power of
>T-SQL; I feel uneasy about interjecting an additional connection layer
>between the app and the db; I feel uneasy about the many posts here
>about problems associated with this model.[/color]

Access is a very popular program, lots of people use it therefore lots
of people can be expected to have problems with it.

Personally, I have used MDB-ODBC-SQL for a lot of small projects and I
haven't had any real problems with it. It's quite easy to develop an
application using local Access tables then switch it to attached SQL
tables. I use T-SQL when it gives me some positive advantage over
Access, enough of an advantage to justify the extra work. I can either
use T-SQL on the server to create a view or I can write a pass-through
query in Access and send raw SQL up to the server.



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[david epsom dot com dot au]

Re: Upsize to SQL Server

I have never had a problem doing IDENTITY_INSERT on
multiple tables in A2K, but, thank God, I only tried
that in tests. I don't understand your explanation,
but I assume that it means that the behaviour would be
different depending on which connection I got.

If A97 treats nVarChar as Memo (I don't remember),
that would have broken big chunks of our application,
because the (natural) primary keys on some of the
critical tables were text fields.

(david)

"Trevor Best" <nospam@localho st.invalid> wrote in message
news:43784932$0 $23294$db0fefd9 @news.zen.co.uk ...[color=blue]
> (assuming A2K2/Jet4 is same behavior as A2K/Jet4)
>
> david epsom dot com dot au wrote:[color=green]
>> All of our complex Transactions (BeginTrans, CommitTrans)
>> are broken in A2K/SQL Server. Jet 4 makes multiple
>> connections inside the transactions, and they block
>> each other.[/color]
>
> Except in the case see below...
>[color=green]
>> A2K/Jet 4 allows you to append to SQL Server identity
>> fields,[/color]
>
> Once. It turns IDENTITY_INSERT ON but forgets to turn it off again
> afterwards so if try a second table it will fail. the second table
> appended unfortunately gets the same connection as the first and issuing a
> pass through "set identity_insert off" statement doesn't work as that goes
> on another connection where it isn't turned on resulting in an error.
>[color=green]
>> The A2000+ upsizing wizards try to create nVarChar
>> fields which don't work with A97, but I think that can
>> be fixed (at least in some versions I think it is an
>> option).[/color]
>
> Not that they don't work, just that A97 hasn't a clue what they are so
> treats them as memo so sorting/grouping won't work.[/color]

[Chuck Grimsby]

Re: Upsize to SQL Server


I can't say as I agree with you on this, Lyle. I can not say I've had
too many problems using Access as a front-end to SQL Server at all.

Using Pass-Thru queries in Access gives you the "power" of T-SQL in
Access, however I must say I rather prefer to establish views in SQL
Server and then use them as "tables" in Access. Granted, I tend to
use "disconnect ed" forms (based on the views from SQL Server), but
perhaps that's just my "style" of programming more then anything else.


On 14 Nov 2005 03:36:29 -0800, "lylefair" <lylefairfield@ aim.com>
wrote:[color=blue]
>IMO there is no good way to use MS-Access with MS-SQL Server.
>A satisfactory way might be to use a disconnected ADP (a disconnected
>MDB is unlikely to be a lot different) and unbound Forms and Reports,
>or Forms and Reports bound to ADO recordsets (often disconnected). But
>this method does not fit with the RAD notion of Access.
>I've not worked with MDB-ODBC-MS-SQL Server. Perhaps, I should. I feel
>that with such a model, it will not be easy for me to use the power of
>T-SQL; I feel uneasy about interjecting an additional connection layer
>between the app and the db; I feel uneasy about the many posts here
>about problems associated with this model.[/color]


--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[Rick Brandt]

Re: Upsize to SQL Server

Chuck Grimsby wrote:[color=blue]
> I can't say as I agree with you on this, Lyle. I can not say I've had
> too many problems using Access as a front-end to SQL Server at all.
>
> Using Pass-Thru queries in Access gives you the "power" of T-SQL in
> Access, however I must say I rather prefer to establish views in SQL
> Server and then use them as "tables" in Access. Granted, I tend to
> use "disconnect ed" forms (based on the views from SQL Server), but
> perhaps that's just my "style" of programming more then anything else.[/color]

Agreed. With an MDB-ODBC-SQLServer setup you can write as much T-SQL as you
want (or as little). I have never seen any limitations to doing this nor
had any problems that I could say were caused by the setup per-se.

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

[David W. Fenton]

Re: Upsize to SQL Server

"Larry Linson" <bouncer@localh ost.not> wrote in
news:zCVdf.1107 1$Mr4.9373@trnd dc08:
[color=blue]
> IIRC, a version of MSDE also came with Access 97, but I do not
> know which version of SQL Server it was based on.[/color]

That is incorrect.

But there's no reason you couldn't use the MSDE from A2K and connect
to it from A97, since it's just ODBC. It's only if you are really
dedicated to using ADO that upgrading from A97 would probably be a
good idea (A97 can use ADO, but it's not got as much native support
as A2K+).

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

[David W. Fenton]

Re: Upsize to SQL Server

"lylefair" <lylefairfield@ aim.com> wrote in
news:1131968189 .797295.285650@ g14g2000cwa.goo glegroups.com:
[color=blue]
> IMO there is no good way to use MS-Access with MS-SQL Server.
> A satisfactory way might be to use a disconnected ADP (a
> disconnected MDB is unlikely to be a lot different) and unbound
> Forms and Reports, or Forms and Reports bound to ADO recordsets
> (often disconnected). But this method does not fit with the RAD
> notion of Access. I've not worked with MDB-ODBC-MS-SQL Server.
> Perhaps, I should. . . .[/color]

Before you post any further categorical condemnations of Access
used
with SQL Server, I suggest you try it out.

Lacking that experience means that your declaration above really
doesn't mean anything at all.
[color=blue]
> . . . I feel that with such a model, it will not be
> easy for me to use the power of T-SQL; I feel uneasy about
> interjecting an additional connection layer between the app and
> the db; I feel uneasy about the many posts here about problems
> associated with this model.[/color]

What's stopping you from using T-SQL? Views and passthrough queries
seem to me to be perfectly valid methods for accomplishing things.

As to "interjecti ng an additional connection layer" what the hell
do
you think OLEDB is? It's no different from ODBC, just more
elaborate
and more up-to-date in its support of features found in modern
database engines.

If your worry is about Jet being involved, then it seems to me you
haven't been paying attention, as it's pretty clear that Jet does
just as well (or better) than ADO at guessing what the best way to
handle remote data will be in any particular instance. And when
either Jet or ADO guesses wrong, you have the option of changing
your methods to make them more efficient.

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

[Lyle Fairfield]

Re: Upsize to SQL Server

David W. Fenton wrote:[color=blue]
> "lylefair" <lylefairfield@ aim.com> wrote in
> news:1131968189 .797295.285650@ g14g2000cwa.goo glegroups.com:
>[color=green]
>> IMO there is no good way to use MS-Access with MS-SQL Server.
>> A satisfactory way might be to use a disconnected ADP (a
>> disconnected MDB is unlikely to be a lot different) and unbound
>> Forms and Reports, or Forms and Reports bound to ADO recordsets
>> (often disconnected). But this method does not fit with the RAD
>> notion of Access. I've not worked with MDB-ODBC-MS-SQL Server.
>> Perhaps, I should. . . .[/color]
>
> Before you post any further categorical condemnations of Access
> used with SQL Server, I suggest you try it out.[/color]

Probably I have used Access with SQL Server more than most.
[color=blue]
> Lacking that experience means that your declaration above really
> doesn't mean anything at all.[/color]

I didn't make a categorical condemnation nor a declaration; look
carefully: I ventured an opinion. (Are you into the sauce again, David?).
[color=blue][color=green]
>> . . . I feel that with such a model, it will not be
>> easy for me to use the power of T-SQL; I feel uneasy about
>> interjecting an additional connection layer between the app and
>> the db; I feel uneasy about the many posts here about problems
>> associated with this model.[/color][/color]
[color=blue]
> What's stopping you from using T-SQL? Views and passthrough queries
> seem to me to be perfectly valid methods for accomplishing things.[/color]

Perhaps, they are. I've written tons of T-SQL. Could you post several of
your passthrough queries that use cursors, loops, conditionals, variable
declarations, return values and call UDFs? Can passthrough queries
substitute for UDFS? I would like to see the mechanics of how this is
accomplished. Lest anyone think I believe this cannot be done, let me
assure you that I really have no idea.
[color=blue]
> As to "interjecti ng an additional connection layer" what the hell
> do
> you think OLEDB is? It's no different from ODBC, just more
> elaborate
> and more up-to-date in its support of features found in modern
> database engines.[/color]

Somewhere I've seen a diagram that shows ODBC as having one layer more
than ADO, but I can't find it now. Perhaps, I imagined it. Perhaps, not.
[color=blue]
> If your worry is about Jet being involved,[/color]

I would rather just worry about things that I said rather than things
you might postulate. Jet is officially dead; I'm not worried about
something that is dead.


--
Lyle Fairfield

[dreadnought8@hotmail.com]

Re: Upsize to SQL Server

Thanks Bradley and others for your responses.[color=blue]
> I have a client who wishes to upsize an A97 system from Jet backend[/color]
to[color=blue]
> SQL Server 2000.[color=green]
>>What is their reasoning for doing this?[/color][/color]
They've been told by their hardware man that SQL Server is a more
robust database. In the seven years the system has been running there
have been 3 or 4 occasions when they've lost up to 4 hours work because
the backend mdb has gone corrupt and they've had to regress to the most
recent backup copy. It's an online booking system and they insist on
minimising this possibility. I assume SQL Server is more robust in this
respect. And I understand there is a hot backup capability in SQL
Server not available in Access. Nonetheless I recommended they
should stick with mdb and suggested other non-upsizing strategies but
they're not interested. Maybe when they find out the cost (once off
and ongoing) they'll reconsider.[color=blue][color=green]
>>Why do you need to use local tables "extensivel y" ?[/color][/color]
The system uses temporary local tables for certain complex reports that
cannot be easily produced directly from simple or nested queries.
These local tables could be transferred to SQL Server but there would
be a fair bit of rewriting. The temporary tables on the server would
have to have a distinguishing code so two users running the same report
were isolated. This is just the way it is done, any change to this
would cost.

Looking throught the other posts, it looks like I'll stay with A97 FE
and SQL Server BE. I've done a (huge) conversion of another A97 app to
SQL Server and am familiar with all the problems that come up. I'm
familiar with TSQL, passthrough queries and fixing blocking problems.
There are complex transactions that I don't want to reengineer by going
to A2K or later (don't like the sound of Jet4 complex transactions not
working!). The only feature of A2K or later that I'd like to be able to
offer is conditional formatting (to make clear different status of
bookings). The main reason for my post was to see if there was a cheap
licensing option for max 5 users including Enterprise Manager. I have
the Developer version including Enterprise Mgr but would not be
interested in not having it installed on the production machine.
Therefore MSDE not an option. I'll look a bit closer at SQL Server
Express, haven't heard of that. Thanks again all
Terry Bell