VB icon

Add New Table (SQL Server 7.0)

Email
Submitted on: 1/17/2015 7:13:00 PM
By: SSBizen (from psc cd)  
Level: Advanced
User Rating: By 5 Users
Compatibility: VB 6.0
Views: 532
 
     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.
 

Windows API/Global Declarations:

Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
'Windows API/Global Declarations for :Add New Table (SQL Server 7.0)
'**************************************
Microsoft SQLDMO Object Library(SQLDMO.RLL)
Microsoft ADO 2.5
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: Add New Table (SQL Server 7.0)
' Description: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.
' By: SSBizen (from psc cd)
'
' Inputs:N/A
'
' Returns: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.
'
' Side Effects:N/A
'**************************************

Option 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


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Advanced category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.