Search Tools Links Login

Add New Table (SQL Server 7.0)

Posted: 2002-06-01
By: ArchiveBot
Viewed: 68

Filed Under:

VB6 Code Cache

No attachments for this post

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.

Original Author: SSBizen




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.



Side Effects


API Declarations

Microsoft SQLDMO Object Library(SQLDMO.RLL)
Microsoft ADO 2.5


Option Explicit
Const DATABASE = "*" 'Enter name of the database here
Const DBFILE_LOC = "C:MSSQL7DATA*_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

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

Comments on this post

No comments have been added for this post.

You must be logged in to make a comment.