excel-vba
SQL dans Excel VBA - Meilleures pratiques

Comment utiliser ADODB.Connection dans VBA?

Exigences:

Ajouter les références suivantes au projet:

• Bibliothèque Microsoft ActiveX Data Objects 2.8

• Bibliothèque de jeu d'enregistrements Microsoft ActiveX Data Objects 2.8

Déclarer des variables

Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command

Créer une connexion

une. avec l'authentification Windows

Private Sub OpenConnection(pServer As String, pCatalog As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI")
    mCmd.ActiveConnection = mDataBase
End Sub

b. avec l'authentification SQL Server

Private Sub OpenConnection2(pServer As String, pCatalog As String, pUser As String, pPsw As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI;User ID=" & pUser & ";Password=" & pPsw)
    mCmd.ActiveConnection = mDataBase
End Sub

Exécuter la commande sql

Private Sub ExecuteCmd(sql As String)
    mCmd.CommandText = sql
    Set mRS = mCmd.Execute
End Sub

Lire les données du jeu d'enregistrements

Private Sub ReadRS()
    Do While Not (mRS.EOF)
        Debug.Print "ShipperID: " & mRS.Fields("ShipperID").Value & " CompanyName: " & mRS.Fields("CompanyName").Value & " Phone: " & mRS.Fields("Phone").Value
        Call mRS.MoveNext
    Loop
End Sub

Fermer la connexion

Private Sub CloseConnection()
    Call mDataBase.Close
    Set mRS = Nothing
    Set mCmd = Nothing
    Set mDataBase = Nothing
End Sub

Comment l'utiliser?

Public Sub Program()
    Call OpenConnection("ServerName", "NORTHWND")
    Call ExecuteCmd("INSERT INTO [NORTHWND].[dbo].[Shippers]([CompanyName],[Phone]) Values ('speedy shipping','(503) 555-1234')")
    Call ExecuteCmd("SELECT * FROM [NORTHWND].[dbo].[Shippers]")
    Call ReadRS
    Call CloseConnection
End Sub

Résultat

Expéditeur: 1 Nom de l'entreprise: Speedy Express Téléphone: (503) 555-9831

ID expéditeur: 2 CompanyName: United Package Téléphone: (503) 555-3199

ShipperID: 3 CompanyName: Federal Shipping Téléphone: (503) 555-9931

Expéditeur: 4 Nom de l'entreprise: expédition rapide Téléphone: (503) 555-1234