473,881 Members | 1,545 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Upsize to SQL Server

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
32 2542
Br
dr**********@ho tmail.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 "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
Nov 14 '05 #2
<dr**********@h otmail.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
On Mon, 14 Nov 2005 04:11:48 GMT, "Br@dley" <br**@usenet.or g> wrote:
dr**********@h otmail.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 "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).


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
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**********@h otmail.com> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.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
(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
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
In message <11************ **********@g14g 2000cwa.googleg roups.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
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****@localho st.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

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
1855
by: Lapchien | last post by:
Can anyone suggest a good utility for this? Thanks, Lap
1
1530
by: Randy | last post by:
When I create tables in SQL I can specify dbo as the owner using the syntax below. Can ownship be changed when using the upsize wizard in Access 2000 so that everything being upsized will be owned by DBO? CREATE TABLE dbo.mytable (c1 int not null) Thank You, Randy K wawork@hotmail.com
3
5587
by: Karen | last post by:
I am attempting to upsize a 60MB database in Access 2000 to sequel server using the upsizing wizard. I go thru all the steps that it mentions in the book, but none of the tables are actually upsized/converted. The error report says "Table was skipped or export failed" for every table. When I attempt to add a new table to this new project, I get an error that says "Table cannot be opened due to a dropped server connection". I have no...
2
2038
by: David C. Barber | last post by:
upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code that I've changed to ADO code is working fine, HOWEVER the form Record Source itself does not seem willing to return data. I've set the Record Source to both the query, and the SQL contained within the query, and although the system pauses long enough to have gone out and retrieved the data, I can't see it. The form itself remains gray. In addition: ...
1
2275
by: rcmail14872 | last post by:
I have a standard Access database with standard Forms and it is not split. I am going to run the upsizing wizard to change the data tables to SQL. I also need to split the database and I am going to change the Forms to be Data Access Pages so that records can be added and edited through Internet Explorer. What order should I take these steps? Can I just save the forms as Data Access Pages and they will work? I think maybe I should get...
1
2086
by: Terry | last post by:
Hello, Has anyone experienced the following problem following an Upsize from Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see what the problem might be. Before Upsize (Access 97 front end and Access 97 tables): A MainForm loads up correctly and then I enter a ReferenceNumber value in a MainForm field control. I then click on the first field of a SubForm, the ReferenceNumber then shows at the top of the SubForm and...
4
2627
by: R.E.V. | last post by:
Hello I've seen a number of posts concerning the above type of conversion but have not been able to locate anything that gives a clear set of instructions to accomplish this. Here is my problem, I have an application to be converted from access 97 to SQL Server Express (backend) and VIsual Basic.NET (frontend). I have the upsize wizard installed and visible in access as an add-in. SSE has been downloaded and installed as well.
4
2410
by: EiEiO | last post by:
Hello, I have a front end back end application created in Access. I need to get it into SQL Server. I used the upsize wizard to create the SQL Server database and the adp front end. The front end is complaining often when I try to execute code. I tried using the mdb front end and linking tables to SQL. After renaming the table (links) from dbo_tbl_Customers to tbl_Customers, the app runs great.
5
2214
by: Yoda | last post by:
Hi, i'm working on a project for convert from a mdb AccessXP(2002) application backend to SQL Server Express 2005. In the past i convertend a similar backend to SQL Server 2000. My problem is: I have many fields on mdb backend with spaces in field name (i know bad thing) With SQL Server 2000 I had no problem because it recognize the field
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11096
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10400
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.