Upgrading from Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lewisvictoria@hotmail.com - 30 May 2004 11:50 GMT

HI there,

Can any one give me advice on moving from Access 2000 to
MSDN, Mysql, Small Business Server or Sql Server?
my company under 5 employees/users of the access
database. This database is used to drive a large
information member based website. It was largely for
just "reading" from the database but now there will be a
great deal of clients writing to the database and I need
to improve security, performance etc.
At the moment I have 3 PCs using Access and then we
upload to the server held by a third party. What do we
need to do and how much will it cost to move my business
up to the next level?

Please help

Victoria

Reply to this Message

Henry Craven - 30 May 2004 15:20 GMT

Victoria,
MSDN, Mysql and SQL Server are all Databases.
Small Business Server is an integrated collection of Servers which can
Host MSDN Databases and in SBS Premium Includes SQL Server.

If you are currently only using Peer to Peer networking then Small
Business Server will probably be an ideal introduction to a Managed
Networking environment.

As far as Upgrading the Access Application/Database goes, that's about
equivalent to asking "how long is a piece of string". It Depends.

At it's simplest there is a wizard with MS Access 2000 that will
"Upgrade" your Jet Database to MSDE . There's also a Wizard that will
slit the Database Application into a client Server Application Front-end
and a Database ( Datastore ) Backend. But that's the easy bit. How
effective the Application will be in a read/write concurrent multi-user
environment will depend upon how well designed the Relational Database
is, how "clean" the Data is, and how well written the Application
Front-end ( Queries, Forms and Code ) is. In reality Jet ( with a well
written Application Front-end ) should handle 5 concurrent users without
issue even if the Datastore gets over a Gigabyte.

SQL Server also includes a Transformation service that enables you to
"Upgrade" a Database from Jet ( MS Access ) MSDE or other forms.Not that
Both the use of MSDE SQL Server mySQL PostgreSQL etc will all require
the Queries and code behind the MS Access forms to be re-written to
comply with the SQL Language and the Connection Types relevant to the
Database used.

For more information on Converting/upgrading the Access Application I'd
suggest you post to the MS Access Database Newsgroups:
comp.databases.ms-access
microsoft.public.access
microsoft.public.access.multiuser
microsoft.public.access.formscoding

...and say high to the Access folks for me they are fine bunch of
gurus - in the true sense of the word..

If you want more information re setting up a small network with full
server and Active Directory functionality then by all means post back
here.

Signature

Henry Craven {SBS-MVP}
Melbourne Australia

> HI there,
>
[quoted text clipped - 14 lines]
>
> Victoria

Reply to this Message

Victoria - 30 May 2004 16:55 GMT

Thanks Henry that is a great help, I will post a revised
request for individual requirements in the places you
suggest. At the moment we design and develop on 3
networked pcs using Access, ASP & Javascript code,
Windows XP pro IIS, there will be over 1,000 client users
of our database from the web browser/website side -
mimimum - at any one time - this is what we need to
prepare for, the most stable system at the least cost. I
beleive our coding is quiet good - clean and clear to
upgrade without too many problems - I know this by my
coding but also running a test on a local personal sql
server. I was simply scared off by the potential cost and
need to get the right system set up for my business.

Thanks again!

Victoria

>-----Original Message-----
>Victoria,
[quoted text clipped - 61 lines]
>
>.

Reply to this Message

Henry Craven - 30 May 2004 22:03 GMT

1,000 users doing read/write to a web enabled Data App, even if only 10%
are going to be concurrent you'll definitely want to go SQL Server. Jet
and MySQL are fine for feeding Static Pages but won't cut it in that
environment. MSDE is Crimped / "Optimised" for 5 concurrent threads
only, and PostgreSQL is magnitudes slower than SQL Server in that sort
of scenario. ( Built one a Year ago that they had started out with Cold
Fusion and PostgreSQL on a Linux base. - Ran like molasses in an
Alaskan winter until we moved it to SQL Server. Many Magnitudes of
improvement, but it cost them heaps due to the re-coding required. )

As in SBS 2003 you are now authorised to use the SQL Server to back-end
a public website ( effectively per-processor ), it may be a good
platform for the app as long as you do the user authentication ( if
there is any ) in your code, and not have them authenticated to SBS and
the SQL Server. You will want to put the app on a Web Server in a DMZ
though and not have it running on the SBS Box (Domain Controller).

Signature

Henry Craven {SBS-MVP}
Melbourne Australia

> Thanks Henry that is a great help, I will post a revised
> request for individual requirements in the places you
[quoted text clipped - 13 lines]
>
> Victoria

Reply to this Message