ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A sample using SQL-DMO programming in VBA procedures to drop a column from a sql server table and replace it with a new column that has an identity property and is a primary key.

Sub CallRemoveOriginalProductIDColumnAndAddNewProductIDColumn()
Dim srvname As String
Dim loginname As String
Dim pwd As String
Dim dbname As String
Dim tblname As String

'Specify pointers for SQLServer object, loginname
'and password, as well as database name
srvname = "cablat"
loginname = "sa"
pwd = ""
dbname = "SQLMagTablesSQL"

tblname = "Products"
RemoveOriginalProductIDColumn srvname, _
loginname, pwd, dbname, tblname, _
"name", "ProdID", _
"datatype", "int"
AddNewProductIDColumn srvname, _
loginname, pwd, dbname, tblname, _
"name", "ProdID", _
"datatype", "int"

End Sub


Sub RemoveOriginalProductIDColumn(srvname As String, _
loginname As String, pwd As String, _
dbname, tblname, _
ParamArray ColSpecs() As Variant)
Dim srv1 As SQLDMO.SQLServer
Dim tbl1 As SQLDMO.Table
Dim col1 As SQLDMO.Column

'Connect to target SQLServer object
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, loginname, pwd

'Reference target table and column
'and remove column
Set tbl1 = srv1.Databases(dbname).Tables(tblname)
Set col1 = tbl1.Columns(ColSpecs(1))
tbl1.Columns(col1.Name).Remove

'Cleanup objects
srv1.Disconnect
Set srv1 = Nothing

End Sub


Sub AddNewProductIDColumn(srvname As String, _
loginname As String, pwd As String, _
dbname, tblname, _
ParamArray ColSpecs() As Variant)

'Connect to target SQLServer object
Set srv1 = New SQLDMO.SQLServer
srv1.Connect srvname, loginname, pwd


'Set reference to the target table
Set tbl1 = srv1.Databases(dbname).Tables(tblname)

'Add an integer data type with an
'Identity property
Set col1 = New SQLDMO.Column
col1.Name = ColSpecs(1)
col1.DataType = ColSpecs(3)
col1.Identity = True
col1.IdentitySeed = 5
col1.IdentityIncrement = 10
tbl1.Columns.Add col1

'Assign the column with the Identity property
'to serve as the table's primary key
Set key1 = New SQLDMO.Key
key1.Name = "ProductsPK"
key1.Type = SQLDMOKey_Primary
key1.KeyColumns.Add "ProdID"
tbl1.Keys.Add key1

'Cleanup objects
srv1.Disconnect
Set srv1 = Nothing

End Sub

Want to understand  SQL Server and Microsoft Access 2000 so that you can program it to do more tasks like this?  Get Professional SQL Server Development with Access 2000 by Rick Dobson from Wrox Press.  Learn more about the book by clicking here.

Copyright 2001 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.