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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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******@newsgroups.nospam> schreef in bericht
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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******************@TK2MSFTNGP09.phx.gbl...

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******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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]

Michael - Not trying to start a flame war, but to say that security is
stronger in Access than Sql Server b/c someone with Admin privileges can get
to the db nutty. If someone has admin privileges and access to a box, they
can get to just about anything. You can definitely permission sql server
strong enough to keep people from seeing what they don't have permission to.
And if people have Admin rights that shouldn't than there's problems with
the permission scheme not the db.At some point, someone is going to have to
have access to the data and whatever scheme you come up with is only as
strong as the how it's enforced - and it doesn't matter how the data is
compromised per se, but that it's compromised at all.

In your scenario where it's not a problem if the users own there data but it
is if they don't, why not store the data in the Sql Server DB Encrypted? We
do that all the time (in fact we're required to in many instances).

As far as 50 users concurrently. There are countless numbers of people that
have terrible problems w/ Access when the number is as low as 5. And unless
you can control with certainty, the growth of the user base, than
scalability is a major problem. What if your app needed to support 100
people or a much rigorous load? With Sql Server or any real DB that'd be
laughable and wouldn't require any work at all.

I'm not trying to be argumentative, but the fact that Access can work as a
solution doesn't mean it's a good one, and to claim that it's more secure is
just nuts
"m.posseth" <po*****@planet.nl> wrote in message
news:42**********************@nova.planet.nl...

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******@newsgroups.nospam> schreef in bericht
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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]

Hello Ryan ,,,
Well untill sofar i did not find a free solution that can counterpart with
what Access can give me out of the box

i am aware of the security modell of SQL server ( we do use SQL server
enterprise for our web projects ) and that is exactly our problem
i work for a companny who sells data ,, we collect data of Automotive parts
wich oil filter will fit on wich car etc etc etc this is expensive
information
then we create a nice gui around this database and sell it to automotive
parts importers throughout Europe ,,, they give this program on DVD to
local workshops ( only in germany already 6000 )

So you understand that collecting this data is big business, we employ
numerous people who`s job is to find out wich parts fits on wich car ,,,

Maybe you are beginning to understand my problem and for a fact numerous
other developers around the world ???

when the data is the value of your program , and this program is shipped to
an end user ,,, there is not reall alternative as a worgroup Access database
as i encounter the performance limits of access i really wish it was true
that i can secure my data in such a way that only my program has access to
this data
encrypting all columns in my database would degrade the performance in such
a way that it is unacceptable for the customer ( there is lots of querying
going around in these databases )

the problem is we arre talking about different forms of security

in my situation when the database goes corrupted ,,, so be it,,,, perform a
new installation of the program and the problem is solved

in my context security means securing my data from the concurency ,,,, so
this means that nobody and when is say nobody i also mean administrators etc
etc except my program should have access to the database ( with access this
is possible with MSDE it isn`t )

Okay before we end in a discussion stating that everything can be hacked ,,,
ofcourse but untill sofar nobody has our data yet ,,, if it was a open
database what would have happened ???

P.s.

had once a discussion with someboddy that said it was probably due to my SQL
knowledge that i couldn`t secure it ,,, well i asked him to send me a small
demo database that i should not be able to open ,,, received it atached it
with my enterprise manager to my local instance and walla there was all the
data

well that was exactly my nightmare scenario , with a database that i would
have shipped as soposed to be secured

on numerous newsgroups ,, i found lots of developers facing the same problem
I hope that someone will once comeup with a solution ,, as i said it is lots
of data , so it would be great if i could switch over

regards

Michel Posseth
"W.G. Ryan MVP" <Wi*********@nospam.gmail.com> schreef in bericht
news:%2****************@TK2MSFTNGP14.phx.gbl...

Michael - Not trying to start a flame war, but to say that security is
stronger in Access than Sql Server b/c someone with Admin privileges can
get to the db nutty. If someone has admin privileges and access to a box,
they can get to just about anything. You can definitely permission sql
server strong enough to keep people from seeing what they don't have
permission to. And if people have Admin rights that shouldn't than there's
problems with the permission scheme not the db.At some point, someone is
going to have to have access to the data and whatever scheme you come up
with is only as strong as the how it's enforced - and it doesn't matter
how the data is compromised per se, but that it's compromised at all.

In your scenario where it's not a problem if the users own there data but
it is if they don't, why not store the data in the Sql Server DB
Encrypted? We do that all the time (in fact we're required to in many
instances).

As far as 50 users concurrently. There are countless numbers of people
that have terrible problems w/ Access when the number is as low as 5. And
unless you can control with certainty, the growth of the user base, than
scalability is a major problem. What if your app needed to support 100
people or a much rigorous load? With Sql Server or any real DB that'd be
laughable and wouldn't require any work at all.

I'm not trying to be argumentative, but the fact that Access can work as a
solution doesn't mean it's a good one, and to claim that it's more secure
is just nuts
"m.posseth" <po*****@planet.nl> wrote in message
news:42**********************@nova.planet.nl...

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******@newsgroups.nospam> schreef in bericht
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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

[Earl]

Bill, that's an offer I would love to take you up on. But with SQL Express
out there, is it worth it any longer to mess with an MSDE installation?

"W.G. Ryan MVP" <Wi*********@nospam.gmail.com> wrote in message
news:ey**************@TK2MSFTNGP09.phx.gbl...

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******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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]

if you do not need to support windows 9.X , i would say No , :-)

regards

Michel

"Earl" <br******@newsgroups.nospam> wrote in message
news:eG**************@TK2MSFTNGP14.phx.gbl...

Bill, that's an offer I would love to take you up on. But with SQL Express
out there, is it worth it any longer to mess with an MSDE installation?

"W.G. Ryan MVP" <Wi*********@nospam.gmail.com> wrote in message
news:ey**************@TK2MSFTNGP09.phx.gbl...

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******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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]

If I understand you correctly, the situation is such that it's ok for me (as
the end user) to see the data as long as I go through your program but if I
try to access (no pun intended) the data outside of your program then that's
what needs to be prevented? If this is the case, then I don't think there's
any way possible to secure it, and no, I don't mean in the sense that I
could hack it. If I'm the end user and even one person in my company has
access to view the data, and my goal is to copy that data and sell it as my
own, even your current security scheme wouldn't stop me- b/c I could hire a
few interns or temp workers to use your app to enter data into my
application. However assume that somehow this is a fact you can live with.

As far as encryption goes though... I've done way too much in my career,
particular for the dept of defense where everything sensitive was
enrypted/hashed and obscured to but that this isn't viable. Assuming that
your case is the statistical anomaly, then you'd still have to agree that
for most other cases/companies that had the same requirements you did - that
it would be viable. When you say that encryption is too expensive in terms
of performance, that's saying a lot and such a generalization is almost
inconceivable. I'll accept for the sake of argument that your case is so
rare because of some totally unique business rules but this is such a rarity
that for most everyone else, it's not applicable. Where exactly is the hit
that you're suffering?

If you are querying values in the DB that are encrypted than certainly most
indexing strategies won't work. But you don't have to encrypt every field
in the table, only ones with the sensitive data. So you can index other
fields and take advantage of that to speed your queries. The fact that I
know Column1 has a value of 222 tells me nothing about any of the other
fields in that record (assuming it was a sequence/identity/autoincrement).
Using strategies like this can easily address the query times. You can
also use hashed values, lighter encryption routines etc for some of the
columns but not others. By carefully analyzing your data and taking
advantage both symmetric ciphers an assymetric ciphers, you can honestly
reduce the impact on performance - And since you can do a LOT with Sql
Server or Oracle in terms of hardware power, you can more than offset the
performance hit you're taking by encrypting the data. Honestly, if you have
a 2 processor itanium server with 4 gigs of ram (which is still very
affordable) on the low end, and you are careful about what you encrypt or
hash and how you do it, you could definitely get better performance there
than what many people with ultra low end servers are getting with totally
unencrypted data.

Also, with Sql Server you can use SQL DMO and encrypt your stored procedures
to add an additional layer of protection by not exposing your sql
statements. I'm not sure about Oracle in this regard but somehow I can't
imagine that Oracle would have overlooked functionality like this.

In order for me to 'prove' that it's doable, I'd need to see what the
current baseline is, but 'encrypting' data is very vague. Certainly I can
imagine doing it in such a way that performance would be nightmarish. But
I've worked on two systems with the DOD that were running Sql Server 2000
where just about everything other than lookup tables was encrypted and
there's no way you'd know the difference as an end user b/c the efficiency
and elegance of how they chose to implement this, bolstered by some decent
servers (although as far as hardware went, they were nowhere near the
horsepower of what I've seen in a typical brokerage firm). Many of the
tables that were used were well over 20 million records and a few of them
topped 700 million records and perhaps your system is notably larger than
that in terms of records, but like I said, there was still a lot of room for
performance increases in terms of hardware.

----------------------------------
If you're honestly really in need of a solution for this , I'd be more than
willing to talk to you offline about it and show you some of the ways that
the systems I mentioned above accomplished what they did. Performance was
very important and as far as end users went, there were seldom complaints
about the responsiveness of the applications or the db. Being a military
installation - Security was absolutely critical and in most instances, the
encryption that was used was much stronger/heavier than what the civilian
world has access to - but the flip side of that was that it was even less
performant than commercial grade algorithms like TripleDes - so if you
implemented anything lighter - you'd get even better performance. Finally,
in these two situations, point in time restore capability was almost as
critical as security and more critical than performance so using a RDBMS
with backup and restore ability was a must as well. Most of it was done
with Sql Server although the second system using both Oracle 9i and Sql
Server 2000 extensively (I know this probably sounds dumb - using both dbs
on the same application but that was done to fulfill other security
requirements so that if one of them was compromised, the other one could
fill in the void - well, there's a little more to it than that but you get
the idea.). Anyway, I'd be glad to share some of strategies that were used
(obviously I can't get too specific but since you're only dealing with
commerical grade encryption anyway, that won't be a problem) and point you
in the direction to we used to get there.) Also, above I may have seemed a
bit abrupt or rude - if so, it's just my clumsiness as a writer - I
certainly didn't mean to come off that way if I did..
Cheers,

Bill
"m.posseth" <po*****@planet.nl> wrote in message
news:42**********************@nova.planet.nl...

Hello Ryan ,,,
Well untill sofar i did not find a free solution that can counterpart with
what Access can give me out of the box

i am aware of the security modell of SQL server ( we do use SQL server
enterprise for our web projects ) and that is exactly our problem
i work for a companny who sells data ,, we collect data of Automotive
parts wich oil filter will fit on wich car etc etc etc this is expensive
information
then we create a nice gui around this database and sell it to automotive
parts importers throughout Europe ,,, they give this program on DVD to
local workshops ( only in germany already 6000 )

So you understand that collecting this data is big business, we employ
numerous people who`s job is to find out wich parts fits on wich car ,,,

Maybe you are beginning to understand my problem and for a fact numerous
other developers around the world ???

when the data is the value of your program , and this program is shipped
to an end user ,,, there is not reall alternative as a worgroup Access
database
as i encounter the performance limits of access i really wish it was true
that i can secure my data in such a way that only my program has access to
this data
encrypting all columns in my database would degrade the performance in
such a way that it is unacceptable for the customer ( there is lots of
querying going around in these databases )

the problem is we arre talking about different forms of security

in my situation when the database goes corrupted ,,, so be it,,,, perform
a new installation of the program and the problem is solved

in my context security means securing my data from the concurency ,,,, so
this means that nobody and when is say nobody i also mean administrators
etc etc except my program should have access to the database ( with access
this is possible with MSDE it isn`t )

Okay before we end in a discussion stating that everything can be hacked
,,, ofcourse but untill sofar nobody has our data yet ,,, if it was a open
database what would have happened ???

P.s.

had once a discussion with someboddy that said it was probably due to my
SQL knowledge that i couldn`t secure it ,,, well i asked him to send me a
small demo database that i should not be able to open ,,, received it
atached it with my enterprise manager to my local instance and walla there
was all the data

well that was exactly my nightmare scenario , with a database that i would
have shipped as soposed to be secured

on numerous newsgroups ,, i found lots of developers facing the same
problem
I hope that someone will once comeup with a solution ,, as i said it is
lots of data , so it would be great if i could switch over

regards

Michel Posseth
"W.G. Ryan MVP" <Wi*********@nospam.gmail.com> schreef in bericht
news:%2****************@TK2MSFTNGP14.phx.gbl...

Michael - Not trying to start a flame war, but to say that security is
stronger in Access than Sql Server b/c someone with Admin privileges can
get to the db nutty. If someone has admin privileges and access to a
box, they can get to just about anything. You can definitely permission
sql server strong enough to keep people from seeing what they don't have
permission to. And if people have Admin rights that shouldn't than
there's problems with the permission scheme not the db.At some point,
someone is going to have to have access to the data and whatever scheme
you come up with is only as strong as the how it's enforced - and it
doesn't matter how the data is compromised per se, but that it's
compromised at all.

In your scenario where it's not a problem if the users own there data but
it is if they don't, why not store the data in the Sql Server DB
Encrypted? We do that all the time (in fact we're required to in many
instances).

As far as 50 users concurrently. There are countless numbers of people
that have terrible problems w/ Access when the number is as low as 5.
And unless you can control with certainty, the growth of the user base,
than scalability is a major problem. What if your app needed to support
100 people or a much rigorous load? With Sql Server or any real DB
that'd be laughable and wouldn't require any work at all.

I'm not trying to be argumentative, but the fact that Access can work as
a solution doesn't mean it's a good one, and to claim that it's more
secure is just nuts
"m.posseth" <po*****@planet.nl> wrote in message
news:42**********************@nova.planet.nl...

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******@newsgroups.nospam> schreef in bericht
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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]

Like Michel said, if you don't need 9.x support than probably not.

Some people will argue that MSDE is a little more mature and hence they're a
little more comfortable with it for the near future but that's based more on
the assumption that there will be problematic bugs than anythintg concrete.
"Earl" <br******@newsgroups.nospam> wrote in message
news:eG**************@TK2MSFTNGP14.phx.gbl...

Bill, that's an offer I would love to take you up on. But with SQL Express
out there, is it worth it any longer to mess with an MSDE installation?

"W.G. Ryan MVP" <Wi*********@nospam.gmail.com> wrote in message
news:ey**************@TK2MSFTNGP09.phx.gbl...

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******@newsgroups.nospam> wrote in message
news:%2***************@TK2MSFTNGP09.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*******@daveg.no.nospam> wrote in message
news:O9********************@telenor.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

[DaveG]

On 12/08/2005 DaveG wrote:

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.

Wow!!! Guys!!!! this was supposed to be a simple question.

Thanks for all the input, I have read all of it with an open mind on
both sides of the argument.

My next question that arises from the responses is what would
'Firebird' give me over MSDE or SQL Express would it give me less??
The reason I ask this is that I run a Linux server and I could run
Firebird there and have access from any workstation but it's not that
important.

(PLEASE LETS NOT START THE MS v LINUX DEBATE HERE, just the practicle
issues of Firebird)

Just for the record, security is not an issue as I own the data and
there are no alien clients on the network, I am not planning to sell
the program so deployment is not an issue but I take all that is said,
as who knows I might write a million £ seller, but I think not.

My instinct is telling me to go SQL Express, unless someone can come up
with a good reason for Firebird, being totally open minded and have not
programmed a DB in anger before, I'm open to suggestions and influence.
I'm using VB.net 2003 & ADO.Net should I experience and problems, the
MS sql express web site seems to talk exclusively about VS.net 2005??

Thanks one more time for all the input from everybody.

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

[m.posseth]

Well Dave

it will sure not give you less it will probably give you more :-) ,,,,,
as it is not cripled but a full version , also xcopy deployable, has a .Net
provider etc etc well it can do everything what all the others can , and as
extra benefit you can move your data to linux \ unix machines ,,,

However MS SQL has such a big name as a good and stable database and the
resources on the web are so hughe that it will take you a fraction of the
time to develop everything in MS SQL as it would if you did the same with
Firebird

But Firebird is sure a great database after playing with it they convinced
me that they have a great product , however for me as a VS programmer it
would cost me to much time that makes for me MS SQL the best choice .

regards

Michel


"DaveG" <ne*******@daveg.no.nospam> wrote in message
news:2-********************@telenor.com...

On 12/08/2005 DaveG wrote:

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.

Wow!!! Guys!!!! this was supposed to be a simple question.

Thanks for all the input, I have read all of it with an open mind on
both sides of the argument.

My next question that arises from the responses is what would
'Firebird' give me over MSDE or SQL Express would it give me less??
The reason I ask this is that I run a Linux server and I could run
Firebird there and have access from any workstation but it's not that
important.

(PLEASE LETS NOT START THE MS v LINUX DEBATE HERE, just the practicle
issues of Firebird)

Just for the record, security is not an issue as I own the data and
there are no alien clients on the network, I am not planning to sell
the program so deployment is not an issue but I take all that is said,
as who knows I might write a million £ seller, but I think not.

My instinct is telling me to go SQL Express, unless someone can come up
with a good reason for Firebird, being totally open minded and have not
programmed a DB in anger before, I'm open to suggestions and influence.
I'm using VB.net 2003 & ADO.Net should I experience and problems, the
MS sql express web site seems to talk exclusively about VS.net 2005??

Thanks one more time for all the input from everybody.

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