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

Inputs

N/A

Assumptions

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.

Returns

N/A

Side Effects

N/A

API Declarations

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

Code

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

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


Comments on this post

No comments have been added for this post.

You must be logged in to make a comment.