Creating and Opening MSDE DBs

  • Thread starter Thread starter Wayne Wengert
  • Start date Start date
W

Wayne Wengert

I want to convert a VB6 app that used Access 2000 as its DB to a VB.NET app
using MSDE and I have some questions.

In the old VB6/Access app, the user could define a new database using a Open
File Dialog. The user specified the path and file name. Using MSDE I believe
I can only let the user provide a new DB name (not a path). Is that true? If
so, how is that usually handled in code? Do I provide a textbox for the user
to enter a new DB name and the do the create using that?

Is there a way to show the user a list of all existing DBs on the MSDE
instance so the user could select an existing one to open?

TIA
 
Unlike JET, SQL Server (MSDE is just another version there-of), handles all
of the physical file IO itself. When you want to create a Database and do so
in code you can do so in a variety of ways. One easy way is to simply submit
a CREATE DATABASE T-SQL command. See books online for the details, but you
get to choose the database name, file location(s), size, how it's to be
stretched when it runs out of space and lots more. Your application will
need to log in with an account that has Create Database permission. No, I
don't recommend developing or running applications with the SA account. Make
your own account and grant it specific permission to carry out these
sensitive tasks. Yes, you'll need the SQL Enterprise manager or equivalent
tools. The easiest way is to simply order the SQL Server Developer Edition
($49) which comes with all of the needed tools to
develop/manage/tune/protect/backup/restore/deploy your database.

hth

--
____________________________________
Bill Vaughn
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.
__________________________________
 
Bill;

Thanks for that information. You raised some interesting issues. If my app
is to use MSDE as the backend and the client already has MSDE installed and
has changed the default login, how does my app know what login to use?

Wayne
 
By license, each application installs its own instance of MSDE (up to 16
instances). Each instance gets its own security accounts, SA password, etc.

--
____________________________________
Bill Vaughn
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.
__________________________________
 
Thanks... I did not realize that.

Wayne

William (Bill) Vaughn said:
By license, each application installs its own instance of MSDE (up to 16
instances). Each instance gets its own security accounts, SA password, etc.

--
____________________________________
Bill Vaughn
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.
__________________________________

No, using
Click to expand...
 
If I can be of any help...please let me know. I know it looks like a lot up
front, but TRUST ME, it gets easier and things start making sense quite
quickly. Fortunately, there are a ton of examples out there and a lot of
people who have made the same mistakes. If you need anything , please let
me know.

Cheers,

Bill
 
Ditto.

--
____________________________________
Bill Vaughn
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.
__________________________________
 
Ok, sorry guys, but I do have to comment on this one...

MSDE allows you to have multiple databases in it. If another instance has
already been installed, then when your app is installed, it could ask for
the sa password, etc. If not, you should give the option of installing a
new instance. However, as a user and programmer, I would very much
*dislike* a company that ended up contributing to 16 MSDE services all
running on my machine!

Regarding the original question of creating databases, which I don't really
see an answer to in this thread....

You can use the transact-SQL statement 'create database' to create your
database, including the name, and also specify the path for the data files
that MSDE uses. Thus, users can specify the 'location' of their database to
be stored, however, in your connecting to and logging in for the
application, the path is not used, just the database name, as mentioned in
prior posts. Also, in the create database statement, you can specify such
things as initial database size, growth parameters, etc.

As many have said, it gets *alot* easier once your past the setup stage, but
keep in mind that MSDE installs a service, normally running all the time, on
the users machine, or a common shared machine somewhere, and you must play
nicely.

As a final note, an example of the create database statement would be:
CREATE DATABASE Products
ON
( NAME = prods_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO

(directly out of transact-SQL help) Of course, you then need to deal with
table creation, stored procedures, etc and so forth. Another option some
people use (and I don't recommend because of database evolution due to
product updates) is create a dummy, empty-template database and 'sp_attach'
it. Again, this is not my recommended approach.
 
Yes, MSDE is SQL Server and SQL Server supports multiple databases. However,
the suggested uses as posted by Microsoft (in an attempt to clarify the
license) states:

". Can I redistribute MSDE for use with multiple applications?

A. Multiple vendors cannot share a copy of MSDE; however, a single
vendor can utilize a single copy of MSDE for multiple applications, provided
the vendor has appropriate MSDE redistribution rights for each application.
"

See http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp



--
____________________________________
Bill Vaughn
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.
__________________________________
 
Thanks to Bill and Richard.

Wayne,

If you just had one instance of MSDE and a bunch of databases in that
instance, you can specify the database in the connection string like the
following.

data source="(local)"; initial catalog=Northwind ;user id=user;
password=pass packet size=4096;persist security info=True;

The "data source" attribute specifies the server name, the "initial
catalog" specifies the name for DB.

If you want to query all the database names in a MSDE instance, you can
also try to reference the Microsoft SQLDOM Object Library, and use
SQLDOM.SqlServerClass to achieve it.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top