LISTING 2: Code to Rename View with SQL-DMO and ADO

Sub RenameViewWithADO(srvname As String, loginname As String, _
  pwd As String, dbname As String, _
  oldname As String, newname As String)
Dim srv1 As SQLDMO.SQLServer
Dim vew1 As SQLDMO.View
Dim str1 As String
Dim str2 As String
Dim cnn1 As ADODB.Connection

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

BEGIN CALLOUT A
'Generate the T-SQL to drop the view with the
'old name.
Set vew1 = srv1.Databases(dbname).Views(oldname)
str1 = vew1.Script(SQLDMOScript_Drops)
str1 = Replace(str1, "GO", "")

'Generate the T-SQL to add the view with the
'new name.
str2 = srv1.Databases(dbname).Views(oldname).Text
str2 = Replace(str2, oldname, newname)
END CALLOUT A

BEGIN CALLOUT B
'Create an ADO Connection object that executes the
'T-SQL to remove the view with the old name and
'add the view with the new name.
Set cnn1 = New ADODB.Connection
With cnn1
  .Provider = "sqloledb"
  .ConnectionString = "data source = " & srvname & ";" & _
    "user id = " & loginname & ";Password = " & pwd & ";" & _
    "initial catalog = " & dbname
  .Open
  .Execute str1
  .Execute str2
End With
END CALLOUT B

'Clean up objects.
cnn1.Close
Set cnn1 = Nothing
srv1.Disconnect
Set srv1 = Nothing
End Sub