excel-vba
SQL in Excel VBA - Best Practices
Suche…
Wie verwende ich ADODB.Connection in VBA?
Bedarf:
Fügen Sie dem Projekt folgende Referenzen hinzu:
Microsoft ActiveX Data Objects 2.8-Bibliothek
Microsoft ActiveX Data Objects-Recordset 2.8-Bibliothek
Variablen deklarieren
Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command
Verbindung herstellen
ein. mit Windows-Authentifizierung
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. mit der SQL Server-Authentifizierung
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
Führen Sie den SQL-Befehl aus
Private Sub ExecuteCmd(sql As String)
mCmd.CommandText = sql
Set mRS = mCmd.Execute
End Sub
Daten aus Datensatz lesen
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
Verbindung schließen
Private Sub CloseConnection()
Call mDataBase.Close
Set mRS = Nothing
Set mCmd = Nothing
Set mDataBase = Nothing
End Sub
Wie benutze ich es?
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
Ergebnis
Versandnummer: 1 CompanyName: Speedy Express Phone: (503) 555-9831
ShipperID: 2 CompanyName: United Package Phone: (503) 555-3199
ShipperID: 3 CompanyName: Federal Shipping Phone: (503) 555-9931
ShipperID: 4 CompanyName: schneller Versand Telefon: (503) 555-1234
Modified text is an extract of the original Stack Overflow Documentation
Lizenziert unter CC BY-SA 3.0
Nicht angeschlossen an Stack Overflow