By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Upsize to SQL Server

P: n/a
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

Nov 14 '05 #1
Share this Question
Share on Google+
32 Replies


P: n/a
Br
dr**********@hotmail.com wrote:
I have a client who wishes to upsize an A97 system from Jet backend to
SQL Server 2000.
What is their reasoning for doing this?
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

Why do you need to use local tables "extensively" ? 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
Nov 14 '05 #2

P: n/a
<dr**********@hotmail.com> wrote
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?
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.
The system uses local tables extensively and I would not
want to disturb this.
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
Enterprise manager would be a requirement.


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
Nov 14 '05 #3

P: n/a
On Mon, 14 Nov 2005 04:11:48 GMT, "Br@dley" <br**@usenet.org> wrote:
dr**********@hotmail.com wrote:
I have a client who wishes to upsize an A97 system from Jet backend to
SQL Server 2000.


What is their reasoning for doing this?
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

Why do you need to use local tables "extensively" ? 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).


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.
Nov 14 '05 #4

P: n/a
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)

<dr**********@hotmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.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

Nov 14 '05 #5

P: n/a
(assuming A2K2/Jet4 is same behavior as A2K/Jet4)

david epsom dot com dot au wrote:
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.
Except in the case see below...
A2K/Jet 4 allows you to append to SQL Server identity
fields,
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.
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).


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.
Nov 14 '05 #6

P: n/a
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.

Nov 14 '05 #7

P: n/a
In message <11**********************@g14g2000cwa.googlegroups .com>,
lylefair <ly***********@aim.com> writes
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.


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.

Nov 14 '05 #8

P: n/a
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" <no****@localhost.invalid> wrote in message
news:43***********************@news.zen.co.uk...
(assuming A2K2/Jet4 is same behavior as A2K/Jet4)

david epsom dot com dot au wrote:
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.


Except in the case see below...
A2K/Jet 4 allows you to append to SQL Server identity
fields,


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


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.

Nov 14 '05 #9

P: n/a

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 "disconnected" 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" <ly***********@aim.com>
wrote:
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.

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

Nov 14 '05 #10

P: n/a
Chuck Grimsby wrote:
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 "disconnected" forms (based on the views from SQL Server), but
perhaps that's just my "style" of programming more then anything else.


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

Nov 14 '05 #11

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:zCVdf.11071$Mr4.9373@trnddc08:
IIRC, a version of MSDE also came with Access 97, but I do not
know which version of SQL Server it was based on.


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
Nov 15 '05 #12

P: n/a
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
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. . . .
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.
. . . 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.


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 "interjecting 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
Nov 15 '05 #13

P: n/a
David W. Fenton wrote:
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
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. . . .
Before you post any further categorical condemnations of Access
used with SQL Server, I suggest you try it out.


Probably I have used Access with SQL Server more than most.
Lacking that experience means that your declaration above really
doesn't mean anything at all.
I didn't make a categorical condemnation nor a declaration; look
carefully: I ventured an opinion. (Are you into the sauce again, David?).
. . . 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.

What's stopping you from using T-SQL? Views and passthrough queries
seem to me to be perfectly valid methods for accomplishing things.
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.
As to "interjecting 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.
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.
If your worry is about Jet being involved,


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
Nov 15 '05 #14

P: n/a
Thanks Bradley and others for your responses.
I have a client who wishes to upsize an A97 system from Jet backend to SQL Server 2000.
What is their reasoning for doing this? 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.Why do you need to use local tables "extensively" ?

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

Nov 15 '05 #15

P: n/a
dr**********@hotmail.com wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:
Why do you need to use local tables "extensively" ?
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.


Why not just have a local MDB separate from the front end, and use
that for the temp tables? I do that all the time. Indeed, I have
very few applications in production use that *don't* have at
minimum
a back end on the server, a front end on each workstation, and a
temp MDB on each workstation.

This should not be any consideration at all blocking upsizing to
SQL
Server.

And something to consider as a possibility for hot backup with MDB
back ends is to replicate your back end, and then synchronize on a
regular schedule. The backup replica(s) would not be open except
during the scheduled synchronization, so they are always available
for full copying/backup, and if you set your synchronization
schedule reasonably, you lose only a small amount of data.

But I wouldn't recommend that as a starting point, or a way to
avoid
upsizing to SQL Server if the client already wants to do so. I'd
only use it to extend reliability when the client does *not* want
the expense and difficulty of upsizing (and I have clients who just
won't do it, despite my recommendation that we start planning for
it; amazingly, one such large application with 100s of thousands of
records, has been running reliably for 3 years now since I first
recommended that they start looking at upsizing; no data losses,
ever).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 15 '05 #16

P: n/a
I'm OK with mdbs accessing local tables, linked SQL Server, and linked
other-mdb tables - I used this in a previous Upsize project. And I
have functions to manage the definition and linking of temp tables as
you do, and do not want to re-engineer them unless necessary.
I thought (probably incorrectly) that there was a post-97 alternative
(ADP?) being suggested that did not allow local or linked mdb tables
and I did not want to have this restriction. Maybe you can clear me up
on that.
I hadn't considered Replication, no experience. Is it reliable? Ifd
not, it's made me think that I might be able to do a "programmed"
replication of critical data only ... to another MDB on another drive
or something. It seems the entry level for SQL Server with Enterprise
Manager (not counting Developer Edition) is very high - 25 seats, $20k
AUD which will be out of the question with the customer I am sure.
Thanks for the feedback.
Terry Bell

Nov 15 '05 #17

P: n/a
dr**********@hotmail.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I'm OK with mdbs accessing local tables, linked SQL Server, and
linked other-mdb tables - I used this in a previous Upsize
project. And I have functions to manage the definition and
linking of temp tables as you do, and do not want to re-engineer
them unless necessary. I thought (probably incorrectly) that there was a post-97 alternative (ADP?) being suggested that did not
allow local or linked mdb tables and I did not want to have this
restriction. Maybe you can clear me up on that.
ADPs don't have local storage (though apparently you can do
something with XML streams written to file, but sounds messy). But
an ADP would be a complete rewrite of the front end, which I don't
believe anyone in their right mind was suggesting. Lyle wasn't
suggesting it, either.
I hadn't considered Replication, no experience. Is it reliable?
Replication is reliable if used properly, but it does bring with it
certain restrictions. But none of those apply in the scenario I was
describing, where it would be used only as a backup method.
Ifd not, it's made me think that I might be able to do a
"programmed" replication of critical data only ... to another MDB
on another drive or something. It seems the entry level for SQL
Server with Enterprise Manager (not counting Developer Edition) is very high - 25 seats, $20k AUD which will be out of the question
with the customer I am sure.


That sounds like it can't be the right number, as the Small
Business
Server version of Windows, which includes SQL Server and Exchange,
sells for substantially less than that in US dollars (i.e., well
under $1K when purchased installed on a new Dell server). For
instance, on CDW.com, I see this:

Microsoft SQL Server 2000 Workgroup Edition - complete package
http://www.cdw.com/shop/products/def...spx?EDC=779930

That includes the server license and 5 client licenses. And the
price is USD729.11.

You might want to take the SKU from that page and see if you can
acquire it locally.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 15 '05 #18

P: 2
[quote=Bernard Peek]In message <1131968189.797295.285650@g14g2000cwa.googlegroups .com>,
lylefair <lylefairfield@aim.com> writes[color=blue]
>IMO there is no good way to use MS-Access with MS-SQL Server.[/color]

At my firm there are far more requests for database applications than we can fulfill. The departments often commission a "tech savvy" employee to create an Access solution for their needs; a couple of departments have actually hired their own Access programmers.

Where I intersect with this trend is when they want to have their data stored on SQL Server. I have converted several applications to run on SQL, some even built on Access v. 2! In general, the applications continue to work well as long as the basic design was reasonable to begin with. The v. 2 application was built with unbound forms and, when we tried to update it to 2003, the entire Main module was blown away and was our most troublesome conversion.

I have done conversions both ways, with me building the tables in SQL and with using the upsizing wizard. The wizard, I am sorry to say, works remarkably well. :D I inherited a project several weeks ago on the day it was to go to production that had not been migrated properly through QA, etc. In the course of a couple of hours I was able to rebuid the DEV and QA instances as well as build the PROD instance and migrate all the data using the upsizing wizard. That application has been running flawlessly ever since. I was even joking yesterday with the Access developer that I had not heard from him for weeks.

My point simply is this, that Access applications can serve some users well and that those applications can communicate seamlessly with SQL Server.
Nov 15 '05 #19

P: n/a
Bri
Lyle Fairfield wrote:
David W. Fenton wrote:
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I've not worked with MDB-ODBC-MS-SQL Server.
Perhaps, I should. . . .

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

Probably I have used Access with SQL Server more than most.


I think David (although I'm prepared to be corrected if I
misinterpreted) was suggesting you try MDB-ODBC-MS-SQL Server before you
comment on it, not that you haven't used other methods of working with
SQL Server from Access.
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.
As I understand it, a passthrough query is treated by SQL Server exactly
the same as a SP (although there is no saving of the stats and execute
plan). I have done passthroughs with a lot more code than just a Select
statement, but not with any UDF's so I can't verify they also work
(although I don't see why they wouldn't). My issue with recordsets
returned via a Passthrough is that Access treats it as read-only, so
bound editing is not possible. I get around that by creating a View and
then linking to it as if it was a Table, then it is editable (assuming
that it would be editable in the first place, eg. can't edit a Group BY
query by design). As far as I can see the only real difference between a
Passthrough and a SP is where the T-SQL is stored, in the FE or the BE.
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.


I would think that the number of layers is less important than the real
world efficiency of getting from A to B. If the extra layer (yes, I've
read that there is an additional layer too) doesn't slow things down by
a meaningful amount or decrease the stability then why worry about it.

--
Bri

Nov 16 '05 #20

P: n/a
Bri <no*@here.com> wrote in news:0pvef.508544$oW2.487537@pd7tw1no:
Lyle Fairfield wrote:
David W. Fenton wrote:
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I've not worked with MDB-ODBC-MS-SQL Server.
Perhaps, I should. . . .
Before you post any further categorical condemnations of Access
used with SQL Server, I suggest you try it out.

Probably I have used Access with SQL Server more than most.


I think David (although I'm prepared to be corrected if I
misinterpreted) was suggesting you try MDB-ODBC-MS-SQL Server
before you comment on it, not that you haven't used other methods
of working with SQL Server from Access.


You are correct in your interpretation. I know perfectly well that
Lyle uses SQL Server on a daily basis with Access, but he doesn't
use ODBC, so he can't really say anything at all about how well it
works.

He's clearly rejected it on the basis of his own superstitions,
rather than on the basis of actual comparative performance tests.

Because of that, I don't really think his opinions should carry much
weight on this subject.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 16 '05 #21

P: n/a
Could you post several of your passthrough queries that use cursors,
loops, conditionals, variable declarations, return values and call UDFs?

Nov 16 '05 #22

P: n/a
Could you post several of your passthrough queries that use cursors,
loops, conditionals, variable declarations, return values and call
UDFs?

Nov 17 '05 #23

P: n/a
"lylefair" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Could you post several of your passthrough queries that use cursors,
loops, conditionals, variable declarations, return values and call
UDFs?


Okay I'll play...

Why would anyone need to do those things in a passthrough query?

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

P: n/a
OK, let me try to trace my thoughts here:

1. T-SQL is powerful;
2. Some of its power rests on its ability to do use cursors, loops,
conditionals, variable declarations and to return values and to both
create and call UDFs;
3. In MDB-JET-ODBC-MS-SQL we effect this power with PassThrough
Queries?

So we would need to use these things in PassThroughQueries?

OR

I suppose we could create the "action" procedures in some way other
than Access (EM or ADO?) and just call them from the Application? (How
do we do this; are the MS-SQL procedures visible?)

And I suppose we could create the record-returning procedures as Views
in some way other than Access (EM or ADO?) and just use them as
RecordSources?

But then we would not have any interactive integration with MS-SQL. We
don't need to know anything about the Procedures and Views we are using
and we have no immediate relationship with them?

I think I implied at the beginning that I am confused; I still am.
I'll try to make a clear question.
When we use ODBC do we get all the power of MS-SQL (including those
capabilities I mentioned such as loops, and the handy little things
such as the sp_***** procedures) and if so, how?

Nov 17 '05 #25

P: n/a
lylefair wrote:
OK, let me try to trace my thoughts here:

1. T-SQL is powerful;
2. Some of its power rests on its ability to do use cursors, loops,
conditionals, variable declarations and to return values and to both
create and call UDFs;
3. In MDB-JET-ODBC-MS-SQL we effect this power with PassThrough
Queries?

So we would need to use these things in PassThroughQueries? [snip]


Well, what *I* do in those situations is write a StoredProcedure using the
SQL Server tools (Enterprise Manager and Query Analyzer) and then use a one
line passthrough query to call the SP.

Sure it would be nice if we could natively call T-SQL stuff like that
directly from Access, but using a passthrough with dynamic SQL takes a
couple lines of code and accomplishes the same thing.

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


Nov 17 '05 #26

P: n/a
Thanks; that helps my understanding of how it works.

Nov 17 '05 #27

P: n/a
lylefair wrote:
OK, let me try to trace my thoughts here:

1. T-SQL is powerful;
2. Some of its power rests on its ability to do use cursors, loops,
conditionals, variable declarations and to return values and to both
create and call UDFs;
I wouldn't equate cursors to power, using a cursor in T-SQL is one of
the most inefficient things you can do with it. It's optmised for batch
processing.

UDFs can be hairy if used like Access functions, consider upsizing this
query to a view:

select * from table where projectid = CurrentProjectID()

where CurrentProjectID() is a VBA function that returns a value
(probably from a global variable so nice a quick)

Access looks at this and says OK, CurrentProjectID() takes no parameters
so I'll call it once and use its return value to select the records.

In T-SQL it would be tempting to keep the code the same, there's no
global variables so the value your current project ID would be in a
table so:

Create function CurrentProjectID()
returns int as
begin
declare @intP int
set @intP = (select ProjectID from tblCurrentProject
where xUsr=system_user and Xhost=host_name())
return @intP
end

then create the view similar to the query...

select * from table where projectid = dbo.CurrentProjectID()

Gotcha: SQL Server will execute dbo.CurrentProjectID() for every row in
the table.

What I do is to join that current project table into my view and
restrict on user and host, I recently had a view go down from 25 seconds
to 5 seconds with that change so there 20 seconds worth of executing
that UDF.

Of course another way would be to use a SP, e.g.

create procedure ShowMyTable as
declare @intP int
set nocount on
set @intP = dbo.CurrentProjectID()
set nocount off
select * from table where projectid = @intP
GO

But that SP could not be linked as a table like a view can, you can of
course use a passthrough query with "exec ShowMyTable" in it, set its
"reuturnsrecords" property to true and voilla but then it's only
possible to use that like a table from within Access.
I suppose we could create the "action" procedures in some way other
than Access (EM or ADO?) and just call them from the Application? (How
do we do this; are the MS-SQL procedures visible?)
Not to the UI but to the connection/database object (if pointed to the
SQL Server db) and to pass-through queries.
And I suppose we could create the record-returning procedures as Views
in some way other than Access (EM or ADO?) and just use them as
RecordSources?

But then we would not have any interactive integration with MS-SQL. We
don't need to know anything about the Procedures and Views we are using
and we have no immediate relationship with them?
I think Rick answered this.
I think I implied at the beginning that I am confused; I still am.
I'll try to make a clear question.
When we use ODBC do we get all the power of MS-SQL (including those
capabilities I mentioned such as loops, and the handy little things
such as the sp_***** procedures) and if so, how?


Just a note to say that naming stored procedures beginning with sp_ is
not advised as SQL Server looks at that prefix and searches the master
database for them first, this adds a little overhead at runtime and also
if your sp had the same name as a system one, yours would never get
executed.

Yes, I'm guilty of that one but can't be arsed to rename 100 procedures
and all the code that calls them now :-)
Nov 17 '05 #28

P: n/a
Thanks for replying Trevor. It was, of course, to the system stored
procedures that I referred, not to user defined procedures the names of
which begin with "sp_"; in fact I have never seen any of those, much
less created them.

Nov 17 '05 #29

P: n/a
Bri

lylefair wrote:
Could you post several of your passthrough queries that use cursors,
loops, conditionals, variable declarations, return values and call UDFs?


I've not done anything that fancy. I am assuming that if you can do the
simple stuff that the more advanced stuff should work too. Could be I'm
making a bad assumption, but if some non query T-SQL works why shouldn't
all of it?

Simplist example (execute an existing SP):
Execute test_sp 2001112

Create an SP (the one run above actually):
CREATE PROCEDURE test_sp
@ProjectNumber int
AS
SELECT * FROM Projects WHERE ProjectNumber=@ProjectNumber

In keeping with my philosophy of keeping the logic in the FE (as I don't
always get direct access to the BE for adding SPs and such) most of my
passthroughs are are esentually queries, sometimes with extra bits like:
SET NOCOUNT ON
INSERT INTO Signout ( InventoryID )
SELECT InventoryAvailable.InventoryID
FROM InventoryAvailable
SELECT @@RowCount as MyRowCount

Anything that requires code I continue to do in VBA.

--
Bri

Nov 18 '05 #30

P: n/a
Yes, I suspect that it will all work, that is that you can pass any
valid SQL string as a PassThrough query, or that you could create any
procedure with the string using (from your example) CREATE PROCEDURE
and then call the PROCEDURE with a simple PassThrough calling EXEC
procedure. Of course the string could be executed, or the procedure
executed directly through ADO as well.
Thank you for your examples.

Nov 18 '05 #31

P: n/a
Bri
lylefair wrote:
Yes, I suspect that it will all work, that is that you can pass any
valid SQL string as a PassThrough query, or that you could create any
procedure with the string using (from your example) CREATE PROCEDURE
and then call the PROCEDURE with a simple PassThrough calling EXEC
procedure. Of course the string could be executed, or the procedure
executed directly through ADO as well.
Agreed, but I am currently an only use ADO when I can't use DAO kinda
guy. I still do most of my development in AC97 (and then convert if
needed, but mostly not) and although I could add the ADO library (and
have done so to test if it would work) I still do mostly DAO.
</ramblemode> I do use ADO for ASP pages, but I'm still learning.
Similar to my T-SQL skills, I learn what I need to get the job done,
then leave it until the next time I need to learn more.
Thank you for your examples.


No problem. I've used many of your examples (even some of your Sunday
morning musings) as learning tools, so its good to return the favour.

--
Bri
Nov 19 '05 #32

P: n/a
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
Could you post several of your passthrough queries that use
cursors, loops, conditionals, variable declarations, return values
and call UDFs?


Why are you asking me this question? You're the person making a
blanket statement about Access with ODBC, something that you,
yourself, admit you haven't used.

I"m making no claims for either one. I've got nothing to prove here.

But you *do*.

Except that you can't.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 20 '05 #33

This discussion thread is closed

Replies have been disabled for this discussion.