Add a Database to a SQL Server

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
End With
AddSQLDatabase = True
Set oSQLServer = Nothing

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s