by SSBizen (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Advanced
Date Added: Wed 3rd February 2021
Rating: 
(5 Votes)
This code lets you add a new table to existing database in SQL Server programmatically. Useful when developing a database application for off-line users. Simply send an executable through e-mail, and let it run once.
Inputs
N/A
Assumes
Set DB name, DB file name, User login, Password(if required), name of new table, and fields. Added table can be easily removed from SQL Server Enterprise Manager.
Code Returns
N/A
Side Effects
N/A
API DeclarationsMicrosoft SQLDMO Object Library(SQLDMO.RLL)
Microsoft ADO 2.5
VBcodersOption Explicit
Const DATABASE = "*" 'Enter name of the database here
Const DBFILE_LOC = "C:\MSSQL7\DATA\*_DATA.mdf" 'Physical path
Const USER = "*" 'User name for login
Const PASSWORD = "*" 'Password
Const TABLE = "*" 'Name of the new table
Const COLUMN1 = "*" 'Field#1 name
Const COLUMN2 = "*" 'Field#2 name
Sub Main()
Dim oSQLServer As SQLDMO.SQLServer, oDatabase As SQLDMO.DATABASE
Dim tblNewTable As New SQLDMO.TABLE
Dim colNewColumn1 As New SQLDMO.Column, colNewColumn2 As New SQLDMO.Column
On Error GoTo Errors
Set oSQLServer = New SQLDMO.SQLServer
oSQLServer.Connect , "sa" 'Use USER/PASSWORD if neccessary
Set oDatabase = oSQLServer.Databases(DATABASE)
'Populate the Column objects to define
'the table columns.
colNewColumn1.Name = COLUMN1
colNewColumn1.Datatype = "decimal"
colNewColumn1.Length = 5
colNewColumn1.NumericPrecision = 3
colNewColumn1.NumericScale = 0
colNewColumn1.AllowNulls = False
colNewColumn2.Name = COLUMN2
colNewColumn2.Datatype = "datetime"
colNewColumn2.Length = 8
colNewColumn2.AllowNulls = True
'Name the table, then set desired properties
'to control eventual table construction
tblNewTable.Name = TABLE
tblNewTable.FileGroup = "PRIMARY"
'Add column objects to the Columns collection
tblNewTable.Columns.Add colNewColumn1
tblNewTable.Columns.Add colNewColumn2
'Create the table by adding the
'Table object to its containing collection.
oDatabase.Tables.Add tblNewTable
Exit Sub
Errors:
ErrorHandler ("Main")
End Sub
Sub ErrorHandler(ByVal strLocation As String)
If Err.Number <> 0 Then
MsgBox "Error #: " & Str(Err.Number) & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Source: " & Err.Source, _
vbCritical + vbSystemModal, "CreateTable: " & strLocation
End If
End Sub
