SQL or Access DB

[DaveG]

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[Marina]

If you say you will have large databases, then Access isn't too great, it
tends to get corrupted, etc. But I wouldn't consider 1000 items to be a
large number of items. So you should re-examine, if this database will truly
end up being large.

I think MSDE (or its next version, SQL Express) is probably your best bet if
you really will have a large database. I don't think you need to go all out
and buy SQL Server.

"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[W.G. Ryan MVP]

Sql Server is soooo much better than access in just about every conceivable
way. If you are going to have multiple users hitting the db, then rule
access out right off of the bat. Access is a nightmare in networked
scenarios b/c it's not meant to be a network db (although it can function as
one).
Sql Server is light years better in security and as far as back up the same
holds. If you need point in time backup and recovery, then Sql Server is
the only choice.
Considering that Sql Server MSDE is free and has the same capacity as
Access- I'd forget that access even existed.
"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[Phil]

Hi Dave,

I have to agree wholeheartedly with our MVP expert. When you have more than
one client then you shouldn't really be considering Access. You are correct
regarding MSDE. It is free and I have been using it myself for development
for a few months. I recently downloaded the newer sql express but haven't
had the time to look at it properly.

Yes, you can not only create your db using the tools within MSDE (such as
osql) and appropriate scripts, you can also fully run your db applications
with MSDE as your sql server. Go for it, it's fun!! :-)

Rgds,

Phil
"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[Cor Ligthert [MVP]]

DaveG,

Not disagreeing with the others who wrote an answer before me, if this
application is for your own personal use, than take that MSDE (or better SQL
Express).

If you want to sell your program, keep than in mind that the Jet engine
(sometimes named Access db) is very easy and free to deploy. To overcome
confusions; Not the product of Microsoft named Access is free, only the use
of the Jet engine deployed by people who have a developer licence where that
is in their products.

(Sql Express should be easy to deploy as well, however that is not free so
you will than have to deal with licenses, MSDE is in my opinion a hell to
deploy)

Just as addition

Cor

[Earl]

If the database design is normalized, then it's less likely that size will
become an issue. A non-normalized database will grow exponentially.

JET ("Access") could be used, as I have in the past for a small system with
less than 10 users, but in time, you will sooner or later hit a locking
issue and/or possible corruption.

As noted by Cor, MSDE is a real bitch to deploy (the cynic in me says that
was by design), so the best "free" choice for a networked solution would be
SQL Express 2005. I'm not sure about the licensing issues in your scenario.

Otherwise, for a small business, if you don't already have a server
installed and plan to build a small network, you might consider buying Small
Business Server Premium. It comes with SQL Server 2000 Standard.

"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[m.posseth]

Well ,,,,,,,

sorry to throw some gassoline on this firy thread

But as far as i know ,,,, ms Access , MSDE and SQL express are all free for
a Visual studio programmer

file security lets me stick to MS Access 2000

MSDE , SQL express , firebird , interbase etc etc all great databases
however they lack one key functionality that keeps me from using it in my
deployable apps
and that is security ,,,,, everyone with admin rights can look in your
database files and see your data , with access you can create a workgroup
file , encrypt the database and only your program can access the data

This is no problem if your users own the data , in my situation the data is
the core business ( this is were the value of the app is ) so you do not
want to have the concurency steal your data and create there own gui aropund
that data and sell this as there own product

i wish i could switch over to a reall RDBMS as our database files are
growing to gigabytes ,,,, however i must say about access it isn`t so bad as
people are telling here because our products are beeing used by thousands of
users throughout Europe and are even used on server farms with more as 50
users using the same program ( thus same DB ) however we are talking about
mostly read data with once in a while a price update

Also the ease to deploy your app with a Access DB,, well ,,, its just a
mather of xcopy and make sure Mdac is installed on the target system

I have once made a deploy package for MSDE and found myself coding in C++
to get everything how i wanted it ( check if there is already an instance,
naming etc etc ) , i understood that this is now much better with sql
express however now you should investigate Installer technology as eveything
is now in a Merge module that can be customized with these options ( MSDE
had also a MM but this lacked these options )

If i am truly honest ,,,,,,,, i must say that if file security was not an
issue i would probably now use the Firebird DB as it performs superb, is
free and is much easier to deploy , (XCOPY deployable , and it is multi
platform ),, there is even an embedded version , that can be scaled up to
the reall server version . and again i must say that this hurts me as i am a
true MS fanatic
regards

Michel Posseth [MCP]


"Earl" <br******@newsg roups.nospam> schreef in bericht
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

If the database design is normalized, then it's less likely that size will
become an issue. A non-normalized database will grow exponentially.

JET ("Access") could be used, as I have in the past for a small system
with less than 10 users, but in time, you will sooner or later hit a
locking issue and/or possible corruption.

As noted by Cor, MSDE is a real bitch to deploy (the cynic in me says that
was by design), so the best "free" choice for a networked solution would
be SQL Express 2005. I'm not sure about the licensing issues in your
scenario.

Otherwise, for a small business, if you don't already have a server
installed and plan to build a small network, you might consider buying
Small Business Server Premium. It comes with SQL Server 2000 Standard.

"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway

[Cor Ligthert [MVP]]

Earl,

Read this page
http://lab.msdn.microsoft.com/express/faq/#general

And than in that the question
Are the Express Edition products free?

Answer
Our plan is to offer the Express products for $49 per product when they are
released.

Just as addition.

Cor

[m.posseth]

well Cor you were in the wrong section this is about the Express editions of
the development tools

we were talking about the databases that MS offers for free ( or not ,,, as
some of you thought )

see this

http://msdn.microsoft.com/library/de...seoverview.asp

Summary: SQL Server Express, a free product based on SQL Server 2005
technology, includes the unique Application XCopy feature, and networking
and security that differ from other SQL Server 2005 editions. These topics
and the integration of SQL Server Express with Visual Studio 2005 are
discussed. Compare this product with the existing Microsoft free databases
like MSDE and Jet. (20 printed pages)

so as you read above the installation part is not an issue anymore as sql
express is now also xcopy deployable ,,, so if entrance to your data , from
anyone who has acces to the computer is not an issue i would say go for SQL
Express , however if entrance to this data should be protected go for a
Access solution

regards

Michel Posseth

"Cor Ligthert [MVP]" <no************ @planet.nl> schreef in bericht
news:%2******** **********@TK2M SFTNGP09.phx.gb l...

Earl,

Read this page
http://lab.msdn.microsoft.com/express/faq/#general

And than in that the question
Are the Express Edition products free?

Answer
Our plan is to offer the Express products for $49 per product when they
are released.

Just as addition.

Cor

[W.G. Ryan MVP]

Earl (in no way being contentious or argumentative) - I don't know about the
statement that MSDE is a bitch to deploy. It took me about a hour to
correctly write an installer project that installed MSDE correctly and set
everything up initially correctly. The first time through I'd say it's
about a 4 on a scale of 1 to 10, after that it's cake and I can easily whip
up an installer to install MSDE, set up the accounts and create the tables I
need in just a few minutes. Heck it takes longer to run than it does for me
to create the package.

THe problem with Access is concurrency mainly, but there are others. THe
non sql standard syntax like IsNull, the lack of any real db language,
security, backup and the like all make access a terrible choice in so many
respects. It was a great alternative in the early 90's, but now it's
nothing but headaches.

If the installation is a problem, drop me a line and I'll gladly walk you
through creating an installer that will include MSDE and set it up for you -
it's really simple and well worth the investment.
"Earl" <br******@newsg roups.nospam> wrote in message
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

If the database design is normalized, then it's less likely that size will
become an issue. A non-normalized database will grow exponentially.

JET ("Access") could be used, as I have in the past for a small system
with less than 10 users, but in time, you will sooner or later hit a
locking issue and/or possible corruption.

As noted by Cor, MSDE is a real bitch to deploy (the cynic in me says that
was by design), so the best "free" choice for a networked solution would
be SQL Express 2005. I'm not sure about the licensing issues in your
scenario.

Otherwise, for a small business, if you don't already have a server
installed and plan to build a small network, you might consider buying
Small Business Server Premium. It comes with SQL Server 2000 Standard.

"DaveG" <ne*******@dave g.no.nospam> wrote in message
news:O9******** ************@te lenor.com...

Hi all

I am planning on writing a stock and accounts program for the family
business, I understand this is likely to take close to 2 years to
accomplish. The stock is likely to run into over a thousand items and
the accounting side will be used for hopefully many years so the
entries are likely to be vast.

The delema is what is best to use ase the DB engine, Access I have as
part of Office 2002 or should I really be looking at SQL server??

What are the pros and cons between the two??

The DB will only be accessed by one client at this time but never more
than 2.

If the choice is SQL Server is there a version available just for
single clients. I remember using MSDE when I did my ASP.Net book can
this be used to create the initial DB???

Thanks for taking the time to read this, any advice would be very
helpfull to me at this planning stage.

--
DaveG - Learning VB.Net slowly.
Skøyen - Oslo - Norway