Help - MSDE or Jet for Stand-alone App,

[David]

I am converting a older VB app to .net. The app currently strores
info. Into a JET database. As this is an end user app, I can not
convert to SQL server, etc. so I need to use either the older Jet
engine or using MSDE.

Questions:

Issues with loading MSDE on a neophyte user's machine. Will it
require user tweaking and will it effect their system performance?
Also, will they need to install as ADMIN?

Also, if multiple people need access to the same database, will MSDE
need to be installed on all of the machines and can it access the same
database file on a network drive? Or will one machine need to be
designated as the DB server? Eg. Time for SQL server?

If one is running SQL server 2000, can the databases be used by MSDE?
If not, where can find info on what changes need to be done?

What is the current stability state of the Jet engine. I know that
the pre-4 versions had data corruption issues. Did version 4.x fix
most of these issues?


Thanks

P.S.

Any recommendations for a good tutorial on installing MSDE on the
target machine and replicating the database files would be helpful.

 

[Sash]

I definitely would recommend using MSDE. We have found it to be
reliable and fairly straightforward to use. My company has an
application that we distribute with MSDE for small installations and
have the customer buy and use SQL Server 2000 for larger installations.
By using MSDE, you can always upgrade to full SQL Server 2000 if
needed - all the features, stored procedure and language syntax, etc.
is the same in MSDE as in full fledged SQL Server. This allows you
great flexibility that JET/Access doesn't have.

To answer some of your questions:

Issues with loading MSDE on a neophyte user's machine. Will it
require user tweaking and will it effect their system performance?
Also, will they need to install as ADMIN?

There are several ways to distribute MSDE including embedding the MSDE
installer or using the separate MSDE installer. We tried embedding the
MSDE installer in our installer but some problems with the MSDE.MSM
(merge module) prevented this from working. I believe the problem was
that the merge module used an older version of MDAC and hadn't been
updated. We just distribute the MSDE installation program with our
software as a seperate install and then have the user install if SQL
Server 2000 is not being used. All you need to do is edit the MSDE
setup.ini file before you install. The MSDE install works silently with
no GUI components. You could edit this file for your users to make your
install standard and not require them to make changes. If using the
MSDE installer, make sure to include an SA password using
SAPWD="your_secure_password" (MSDE 2000 SP3a and higher requires
non-blank password) and set the DISABLENETWORKPROTOCOLS=0 to allow
users on a network to access the database. I believe you must be Admin
user to install properly. How many installers out there let non-Admin
users install anyway?

Also, if multiple people need access to the same database, will MSDE
need to be installed on all of the machines and can it access the same
database file on a network drive? Or will one machine need to be
designated as the DB server? Eg. Time for SQL server?

You can create one MSDE database and have it on a network just like
regular SQL server DBs. Multiple people can access the MSDE database.
Just remember to install with the option DISABLENETWORKPROTOCOLS=0 to
allow network access. MSDE does not limit you to number of connections
or users (this is erroneously reported on some newsgroups as being
true). MSDE does use a 'governor' to limit the database however. You
can read the MSDN help topic below to learn about this.

http://msdn.microsoft.com/library/?url=/library/enus/architec/8_ar_sa2_0ciq.asp?frame=true

If one is running SQL server 2000, can the databases be used by MSDE?
If not, where can find info on what changes need to be done?

You can use SQL Server 2000 Enterprise Manager to look at MSDE
databases. The MSDE redistribution license however does not let you
distribute Enterprise Manager or the other graphical UI tools found
with SQL Server. For our MSDE clients we incorporated the necessary
features into our application included some of the DB operations
needed. One example is if you use Windows Auth for your login model.
Since we used Windows Authentication model so we needed to have the
user add a user group to the database to give other users permission to
access the DB. You can do this by building your own screen or
application and calling the appropriate SQL stored procedures or DML
syntax.

Any recommendations for a good tutorial on installing MSDE on the
target machine and replicating the database files would be helpful.

Here is the MSDE home page:
http://www.microsoft.com/sql/msde/default.asp

The google groups have a lot of MSDE postings that can be helpful. I
looked but there were no MSDE books available.

 

[Sash]

I definitely would recommend using MSDE. We have found it to be
reliable and fairly straightforward to use. My company has an
application that we distribute with MSDE for small installations and
have the customer buy and use SQL Server 2000 for larger installations.
By using MSDE, you can always upgrade to full SQL Server 2000 if
needed - all the features, stored procedure and language syntax, etc.
is the same in MSDE as in full fledged SQL Server. This allows you
great flexibility that JET/Access doesn't have.

To answer some of your questions:

Issues with loading MSDE on a neophyte user's machine. Will it
require user tweaking and will it effect their system performance?
Also, will they need to install as ADMIN?

There are several ways to distribute MSDE including embedding the MSDE
installer or using the separate MSDE installer. We tried embedding the
MSDE installer in our installer but some problems with the MSDE.MSM
(merge module) prevented this from working. I believe the problem was
that the merge module used an older version of MDAC and hadn't been
updated. We just distribute the MSDE installation program with our
software as a seperate install and then have the user install if SQL
Server 2000 is not being used. All you need to do is edit the MSDE
setup.ini file before you install. The MSDE install works silently with
no GUI components. You could edit this file for your users to make your
install standard and not require them to make changes. If using the
MSDE installer, make sure to include an SA password using
SAPWD="your_secure_password" (MSDE 2000 SP3a and higher requires
non-blank password) and set the DISABLENETWORKPROTOCOLS=0 to allow
users on a network to access the database. I believe you must be Admin
user to install properly. How many installers out there let non-Admin
users install anyway?

Also, if multiple people need access to the same database, will MSDE
need to be installed on all of the machines and can it access the same
database file on a network drive? Or will one machine need to be
designated as the DB server? Eg. Time for SQL server?

You can create one MSDE database and have it on a network just like
regular SQL server DBs. Multiple people can access the MSDE database.
Just remember to install with the option DISABLENETWORKPROTOCOLS=0 to
allow network access. MSDE does not limit you to number of connections
or users (this is erroneously reported on some newsgroups as being
true). MSDE does use a 'governor' to limit the database however. You
can read the MSDN help topic below to learn about this.

http://msdn.microsoft.com/library/?url=/library/enus/architec/8_ar_sa2_0ciq.asp?frame=true

If one is running SQL server 2000, can the databases be used by MSDE?
If not, where can find info on what changes need to be done?

You can use SQL Server 2000 Enterprise Manager to look at MSDE
databases. The MSDE redistribution license however does not let you
distribute Enterprise Manager or the other graphical UI tools found
with SQL Server. For our MSDE clients we incorporated the necessary
features into our application included some of the DB operations
needed. One example is if you use Windows Auth for your login model.
Since we used Windows Authentication model so we needed to have the
user add a user group to the database to give other users permission to
access the DB. You can do this by building your own screen or
application and calling the appropriate SQL stored procedures or DML
syntax.

Any recommendations for a good tutorial on installing MSDE on the
target machine and replicating the database files would be helpful.

Here is the MSDE home page:
http://www.microsoft.com/sql/msde/default.asp

The google groups have a lot of MSDE postings that can be helpful. I
looked but there were no MSDE books available.

 

[Paul Clement]

¤ I am converting a older VB app to .net. The app currently strores
¤ info. Into a JET database. As this is an end user app, I can not
¤ convert to SQL server, etc. so I need to use either the older Jet
¤ engine or using MSDE.
¤

You may want to indicate whether this is a multi-user application and if so the maximum number of
concurrent users you would expect at any one time.

¤ What is the current stability state of the Jet engine. I know that
¤ the pre-4 versions had data corruption issues. Did version 4.x fix
¤ most of these issues?

Very little has changed here. Jet is a stable database engine but the potential for database
corruption still exists (especially over the network).


Paul
~~~~
Microsoft MVP (Visual Basic)