Create a sql server database from a vb program

Posted on 2005-02-28
Medium Priority
Last Modified: 2010-04-17
Hi Experts!

I am trying to write an installation program in vb6 that will create a SQL database with a new name, tables, etc. Until today I was using DAO, now I want to make the databases more useful through network. So I decided to use the SQL Server. I am using SQL Server 7 and 2000. I am having a database on my computer where I was writing my application. I created database using MS SQL Server 7 Enterprise Manager. Now as I have developed my application to my other computer (not plugged to network) with installed SQL Server 2000, without any database on it. I would like my application to ask me if I want to create a database (because there's no database as I install my application) on this computer and to create it for me automaticly. Is it possible with small code as with DAO?? Please help!

Question by:klm-soft
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 22

Expert Comment

ID: 13426770
There are a couple of ways to do this... both will require SQL Server Admin rights to perform as will any other method, too.
One method is to have a pre-generated text file containing all the t-sql script to accomplish the task and then invoke the OSQL utility to process the script.  A variation of that is to have the VB program generate the script file on the fly.  The more advanced way is to code the app to use SQLDMO to directly perform all the necessary operations of defining the db, tables, views, indexes, relationships, constraints, udf's, sprocs, users, permissions, backup jobs, etc.  I've not used DAO in so long I don't remember if it's possible to use it for this, bit I tend to doubt it.  With the other options, there's no need to.


Author Comment

ID: 13427430
I prefer to use the advanced way - code in my application. I heard there is sth like SQLDMO, but never used it. Where can I find a sample of code creating database, so I can see how to do that??
LVL 22

Accepted Solution

JesterToo earned 600 total points
ID: 13432287
SQL Server "Books On Line", aka BOL, has examples of how to create all, or nearly all, the objects you will need.  Just select the "index" tab, type in "SQL-DMO", and double click on "examples" about half-way down the list.  You will get a combo-box of choices... start with "Creating a Database" then proceed to the other tasks such as "Creating a Table", "Altering a Table by adding a column", etc.

I don't have a ful-fledged app lying around to provide as an example and a quick check around the web didn't turn up any "complete" examples either... just fragments.

If you run into any difficulties with the coding just post your question(s) back here.

Also, If you don't have access to BOL for some reason, let me know and I will post the relevant examples from BOL.

LVL 16

Assisted Solution

JohnBPrice earned 400 total points
ID: 13434035
You need to collect from the user the name of the SQL Server instance, a login/password that has permission to create databases, then it is as simple as

    Dim db As New ADODB.Connection
    db.Open "Provider=SQLOLEDB;server=John\msde;uid=sa;pwd=sa"
    db.Execute "create database blah"
    MsgBox "done"

Obviously this is ADO, but DAO would work too.  Create Database has a number of optional parameters which you might care about, as JesterToo said, check the SQL Server documentation.

Author Comment

ID: 13435178
Thank both of you!!

Now I only have learn how to create tables, etc...

Best regards


Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Simple Linear Regression
Starting up a Project

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question