Source / Exemple :
'Connecting to the SQL Server
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
MsgBox "Connected to SQL Server", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Connecting to a system database
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objDatabaseDMO As New SQLDMO.Database
Set objDatabaseDMO = objSQLServerDMO.Databases("Tempdb")
MsgBox "Connected to Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a new database
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objDatabaseDMO As New SQLDMO.Database
objDatabaseDMO.Name = "Emp_Database"
objSQLServerDMO.Databases.Add objDatabaseDMO
MsgBox "New Database Emp_Database Created", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting a database
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases.Remove "Emp_Database"
MsgBox "New Database Emp_Database Deleted", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a table into a new database
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objDatabaseDMO As SQLDMO.Database
Set objDatabaseDMO = objSQLServerDMO.Databases("Tempdb")
Dim objTableDMO As New SQLDMO.Table
objTableDMO.Name = "Employee"
Dim objColumn1DMO As New SQLDMO.Column
objColumn1DMO.Name = "Emp_ID"
objColumn1DMO.Datatype = "Varchar"
objColumn1DMO.Length = 4
objColumn1DMO.AllowNulls = True
Dim objColumn2DMO As New SQLDMO.Column
objColumn2DMO.Name = "Emp_Name"
objColumn2DMO.Datatype = "Varchar"
objColumn2DMO.Length = 25
objColumn2DMO.AllowNulls = True
objTableDMO.Columns.Add objColumn1DMO
objTableDMO.Columns.Add objColumn2DMO
objSQLServerDMO.Databases("TempDB").Tables.Add objTableDMO
MsgBox "Table Employee created into the Tempdb Database", vbOKOnly, "SQLDMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Dropping a table
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("Tempdb").Tables("Employee").Remove
MsgBox "Table Employee deleted from the Tempdb Database", vbOKOnly, "SQLDMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a index
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objDatabaseDMO As SQLDMO.Database
Set objDatabaseDMO = objSQLServerDMO.Databases("Tempdb")
Dim objTableDMO As New SQLDMO.Table
objTableDMO.Name = "Employee"
Dim objColumn1DMO As New SQLDMO.Column
objColumn1DMO.Name = "Emp_ID"
objColumn1DMO.Datatype = "Varchar"
objColumn1DMO.Length = 4
objColumn1DMO.AllowNulls = True
Dim objColumn2DMO As New SQLDMO.Column
objColumn2DMO.Name = "Emp_Name"
objColumn2DMO.Datatype = "Varchar"
objColumn2DMO.Length = 25
objColumn2DMO.AllowNulls = True
objTableDMO.Columns.Add objColumn1DMO
objTableDMO.Columns.Add objColumn2DMO
objSQLServerDMO.Databases("TempDB").Tables.Add objTableDMO
Dim objIndexDMO As New SQLDMO.Index
objIndexDMO.Name = "idx_Emp_ID"
objIndexDMO.Type = SQLDMOIndex_Unique
objIndexDMO.IndexedColumns = "[Emp_ID]"
MsgBox "Index idx_Emp_Id created on column Emp_ID", vbOKOnly, "SQLDMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Perform Database backup
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
'Creating a database
'If you already have database then you need not create again
Dim objDatabase As New SQLDMO.Database
objDatabase.Name = "Emp_Database"
objSQLServerDMO.Databases.Add objDatabase
'Backing up your database
Dim objBackupDMO As New SQLDMO.Backup
objBackupDMO.Action = SQLDMOBackup_Database
objBackupDMO.Database = "Emp_Database" 'Name of the database for which you are taking backup
objBackupDMO.MediaName = "Emp_Database.bak"
objBackupDMO.Files = "E:\Emp_Database.bak" 'Path where you need to place the backup
objBackupDMO.SQLBackup objSQLServerDMO
MsgBox "Emp_Database backup finished", vbOKOnly, "SQLDMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Restoring database backup
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objRestoreDMO As New SQLDMO.Restore
objRestoreDMO.Action = SQLDMORestore_Database
objRestoreDMO.Database = "Emp_Database" 'Name of the database which you need to restore
objRestoreDMO.Files = "E:\Emp_Database.bak" 'Path of the file which you need to restore
objRestoreDMO.SQLRestore objSQLServerDMO
MsgBox "Restoring backup of Emp_Database finished", vbOKOnly, "SQLDMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating Triggers
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objTriggerDMO As New SQLDMO.Trigger
objTriggerDMO.Name = "TRI_Employee"
objTriggerDMO.Text = "Create Trigger Tri_Employee on Employee For Delete As Delete from Employee"
objSQLServerDMO.Databases("Tempdb").Tables("Employee").Triggers.Add objTriggerDMO
MsgBox "Trigger TRI_Employee created into the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting Triggers
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("Tempdb").Tables("Employee").Triggers.Remove "TRI_Employee"
MsgBox "Trigger TRI_Employee deleted from the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating Views
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objViewDMO As New SQLDMO.View
objViewDMO.Name = "V_Employee"
objViewDMO.Text = "Create View V_Employee As Select * from Employee"
objSQLServerDMO.Databases("Tempdb").Views.Add objViewDMO
MsgBox "View V_Employee created into the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting Views
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("Tempdb").Views.Remove "Vie_Employee"
MsgBox "View V_Employee deleted from the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating User Defined Data Types
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objUDTDMO As New SQLDMO.UserDefinedDatatype
objUDTDMO.Name = "UDT_YOURNAME"
objUDTDMO.BaseType = "Varchar"
objUDTDMO.Length = 25
objUDTDMO.AllowNulls = False
objSQLServerDMO.Databases("Tempdb").UserDefinedDatatypes.Add objUDTDMO
MsgBox "UDT UDT_YourName Created into the Tempdb Database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting User Defined Data Types
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("TempDB").UserDefinedDatatypes.Remove "UDT_YourName"
MsgBox "UDT UDT_YourName deleted from the Tempdb Database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a Stored Procedure
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objStoredProcedureDMO As New SQLDMO.StoredProcedure
objStoredProcedureDMO.Name = "SPE_Employee"
objStoredProcedureDMO.Text = "Create Procedure SPE_Employee As Select * from employee"
objSQLServerDMO.Databases("Tempdb").StoredProcedures.Add objStoredProcedureDMO
MsgBox "Procedure SPE_Employee created into the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting a Stored Procedure
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("Tempdb").StoredProcedures.Remove "SPE_Employee"
MsgBox "Procedure SPE_Employee deleted from the Tempdb database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a Database User
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objDatabaseDMO As SQLDMO.Database
Set objDatabaseDMO = objSQLServerDMO.Databases("Tempdb")
Dim objUserDMO As New SQLDMO.User
objUserDMO.Name = "YourName"
objUserDMO.Login = "YourMachineName\ADMINISTRATOR" 'If you are using NT server, depends on your machine
objSQLServerDMO.Databases("Tempdb").Users.Add objUserDMO
MsgBox "New User Created for TempDB database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Deleting a Database User
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
objSQLServerDMO.Databases("Tempdb").Users.Remove "YourName"
MsgBox "User YourName deleted for Tempdb Database", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Creating a new SQL Server Login
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
Dim objLoginDMO As New SQLDMO.Login
objLoginDMO.Name = "YourName"
objLoginDMO.SetPassword "", "YourPassword"
objSQLServerDMO.Logins.Add objLoginDMO
MsgBox "New Server Login YourName Created", vbOKOnly, "SQL-DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
'Refreshing SQL-DMO Object
'Open a standard exe form
'Make a reference to Microsoft SQLDMO object Library
'Put a command button over the form
'Type the code below in the click event of Command Button
Private Sub Command1_Click()
On Error GoTo Err_Handler:
Dim objSQLServerDMO As New SQLServer
objSQLServerDMO.Connect "Name of the machine where component is running", sa
'Refresh method Updates the SQL-DMO object with the new values"
objSQLServerDMO.Databases("Tempdb").Tables.Refresh True
MsgBox "All tables updates with the new values in the Tempdb Database", vbOKOnly, "SQL_DMO"
Exit Sub
Err_Handler:
MsgBox Err.Description & " " & Err.Number, vbOKOnly, "SQL-DMO"
End Sub
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.