MSDE vs Access

[John]

Hi

I have a relatively small number of records that can be more or less handled
by access. In this case, are there any advantages to moving to MSDE in terms
of development speed/ease? Would any performance gains be achieved, using
MSDE over access?

I am using vb.net/ado.net.

Thanks

Regards

 

[Miha Markic]

Hi John,

It really depends on your app.
If you don't mind installing msde on the target computer then you should go
with it.
As replied in previous thread, MSDE is Sql server without some features
while Access is well... Access.

 

[William Ryan]

If you are on a desktop and no for a fact that your DB will always be small
and never be accessed over a networ, then Access is probably OK. Otherwise,
go with MSDE. 1) It's faster b/c its a real client/server db. It doesn't
have to push all of the data back and forth over the network, it can work
with just sets of it 2) It supports Stored Procs and T-SQL - that's huge in
terms of development options and separation of logic 3) It's newew
technology 4) It has much better security, you can do a lot with built in
roles and all sorts of other features 5) You can do a lot more regarding
tuning and optimization. 6) Backup and Recovery in MSDE are lightyears
ahaed of Access - there's no comparison here 7) Native SQLClient Library in
..NET which means much better performance. 9) IF your database grows to
multiple users, Access will kill you, MSDE won't and you can upsize much
eaiser. 10) MSDE supports real SQL, not that #09-03-2003#, InNull that's
not a boolean etc 11) Real stored procs, views and UserDefined Functions 11)
Clustered and Non-Clustered Indexes 12) Many many many functions to monitor
and maintain your db 13)Replication/SqlServer CE etc.

So, if you know it won't grow and you only have one or two users, Access
probably isn't that 'deficient' of a DB. But with all due respect to Access
programmers, Access is old as heck, and hasn't gotten much better over the
years. MSDE is light years ahead of it in any regard I can think of, other
than maybe it's easier to set up.

HTH,

Bill

 

[Jason]

I am in a similar position and currently I am using Access in the beta
version. I know in the future we will make the move to SQL Server because
we are going to do some system integration (will use DTS...). I was going
to use MSDE but I heard setup and installing it is a pain. How is MSDE
installation/Setup?

Thanks

 

[Joe Fallon]

1) It's faster ...

Actually, based on my experience and reading these newsgroups for years:

SQL Server (or MSDE) versions of Access applications are almost always
slower.

JET on a desktop is extremely fast. (I think Foxpro might be the fastest
ISAM database.)

A well desigend Access app could meet your needs.

But moving to MSDE has numerous advantages (as listed in the previous
message).
If the app grows, then the switch to a "real" SQL Server is as simple as
backup/restore and there will be NO code changes required.

So a lot depends on your skill level, and future needs etc...

 

[Joe Fallon]

It is a pain!
(You saw that coming, right?)

Actually, what is painful is trying to make a package for it so you can
install it with your app - out of the box.

There are many other options that are less painful:
1. Ask the client if they have SQL Server and share a server.
2. Have the client set up a server and install MSDE before you get there.
3. Bring the MSDE install disk and just run setup manually - if you take the
defaults it is only a 5 -10 minute job.

(Practice #3 above a few times and you can breeze through it.)

 

[Miha Markic]

Hi Joe,

4. MSDE Deployment kit (see few threads below) which is in Release Candidate
phase and not supported by MS yet and do not install it on production
machines (did I said it right this time? )

 

[William \(Bill\) Vaughn]

I agree with Joe. Access/Jet is very fast in small, single-user databases
where it does not have to move the raw data over the wire. That's the rub.
It does not scale. Your skills and code don't either. The skills you learn
and the techniques you use with JET/Access aren't the same ones you use with
SQL Server--neither are the applications. That's the real reason to move
your database and skills to MSDE. The installation/deployment issues are
getting addressed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[John]

I am too having the same problem in whether to choose MSDE or Access. My
application will have 2 to 4 users. The DB will be on a server and the
frontend on the client. Currently, we are using an Access DB in developing
it. Access db in my case will work however, down the road will be
integrating this system with another software and we will be using MS SQL
Server to integrate the two systems by using DTS packages and triggers.
Right now we do not have SQL Server 2000 up on a server and will not do this
to latter. This project is in a time crunch and needs to be getting out as
soon as possible. So, I am guessing MSDE would be perfect for my situation.
However, I really do not need the headaches of installation. I would like
to have the same architecture with the DB on a server and have the frontend
on the client. Do I need to install the MSDE on the client as well? So, is
it worth the switch or do I have more work down the road when I switch to
SQL Server?

Thanks

 

[William \(Bill\) Vaughn]

No, the client (in this case) does not need MSDE. In a connected (intranet)
environment, you install SQL Server (any version including MSDE) on a
central server and the clients connect to that server. Remember, by default
MSDE has this functionality switched off, but it can be easily turned back
on during setup. I would suggest using replication to keep the systems (the
other SQL Server) in sync. It's a lot easier.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Joe Fallon]

"However, I really do not need the headaches of installation."

I think this install "headache" is getting blown out of proportion.
My understanding is that adding MSDE to an automated installer programmer is
the headache.
If you have an MSDE disk and double click Install you can step through the
defaults in under 10 minutes.
It is not a big deal.

Also, the Develop edition of SQL Server is not $449 it is now only $49 so
you can do all your development in testing in a real SQL Server environment.
Then do a Backup and Restore to MSDE for deployment.

Based on your long term goal of using SQL Server it makes sense to do the
current app in MSDE.
Unless the time crunch is SO sever that you need to use Access because you
have not climbed the learning curve yet for MSDE programming. Then you could
build and deploy V1 in Access, and take your time getting V2 ready in MSDE
when the pressure is off. Depends how much time you have between and your
skill level and.....