Add a Database to a SQL Server

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility: Visual Basic 6

More information:

This function allows you to add a new database to a SQL Server. You can also optionally add 1 or more tables to the new database. Refer to the comments for usage and limitations of the function.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function AddSQLDatabase(ByVal ServerName As String, _
    ByVal DatabaseName As String, ByVal UserID As String, _
    ByVal Password As String, ByVal DatabaseFileName As String, _
    ParamArray AddTables() As Variant) As Boolean

'*************************************
'PURPOSE:       Creates a Database for an SQL Server
'PARAMETERS:    ServerName: Name of SQLServer
'               DatabaseName: Name of Database to Delete
'               UserID: LoginID for SQL Server. 
'                  User must have sysadmin or
'                  dbcreator privileges
'               Password: Password for SQL Server
'               DatabaseFileName: FileName for Database
'               AddTables: List of Tables to attach to the db.  
'                  If you don't want
'                  to add tables, pass "" as this parameter
'RETURNS:       True if successful, false otherwise
'REQUIRES:      Reference to Microsoft SQLDMO object library
'
                
'EXAMPLE:       AddSQLDatabase "MySQLServer", "NewDB", -
'                   "sa", "myPassword", _
'                  "C:\Program Files\Microsoft SQL Server\MSSQL\Data\NewDB.mdf", _
'                  '"Newtable1", "NewTable2"

'NOTES: Tested with SQL 2000.  Should work with SQL 7.0
'******************************************

Dim oSQLServer As New SQLDMO.SQLServer
Dim oDatabase As New SQLDMO.Database
Dim oDBFile As New SQLDMO.DBFile
Dim oApp As New SQLDMO.Application
Dim oFileGroup As SQLDMO.FileGroup
Dim oTable As SQLDMO.Table
Dim lCtr As Long
Dim oColumn As SQLDMO.Column
On Error GoTo ErrorHandler

With oSQLServer
   .Connect ServerName, UserID, Password

oDatabase.Name = DatabaseName
    
     'THIS uses default properties of
    'the dbfile.  If you want to change these,
    'set optional properties, such as MaximumSize
    oDBFile.Name = DatabaseName
    oDBFile.PhysicalName = DatabaseFileName

Set oFileGroup = oDatabase.FileGroups.Item("PRIMARY")
    oFileGroup.DBFiles.Add oDBFile
           
    
    .Databases.Add oDatabase
     For lCtr = 0 To UBound(AddTables)
            If CStr(AddTables(lCtr)) <> "" Then
                
                Set oTable = New SQLDMO.Table
                oTable.Name = AddTables(lCtr)
                'You must add at least one colummn.
                'I'm adding a default identity col named
                'id. If you want to control this,
                'add more parameters to the function
                Set oColumn = New SQLDMO.Column
                
                oColumn.Datatype = "int"
                oColumn.Identity = True
                oColumn.Name = "ID"
                oTable.Columns.Add oColumn
                
                oDatabase.Tables.Add oTable
                    
            End If
        Next
  End With
AddSQLDatabase = True
ErrorHandler:
Set oSQLServer = Nothing

Set oDatabase = Nothing
Set oDBFile = Nothing
Set oFileGroup = Nothing
End Function