Programmation sql-dmo à travers une application vb

Soyez le premier à donner votre avis sur cette source.

Snippet vu 8 710 fois - Téléchargée 42 fois

Contenu du snippet


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

A voir également

Ajouter un commentaire

Commentaires

Messages postés
8
Date d'inscription
lundi 28 avril 2003
Statut
Membre
Dernière intervention
8 mai 2008

Personnellement, j'ai essayé la portion de sauvegarde et je suis comblé.
j'aurais préféré remplacer dans le code cette ligne

objSQLServerDMO.Connect "Name of the machine where component is running", sa

par celle-ci

objSQLServerDMO.Connect "Name of the instance you want to connect to", "Login", "Password"

Merci encore
Messages postés
8
Date d'inscription
lundi 28 avril 2003
Statut
Membre
Dernière intervention
8 mai 2008

Personnellement, j'ai essayé la portion de sauvegarde et je suis comblé.
j'aurais préféré

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.