excel-vba
SQL i Excel VBA - bästa metoder
Sök…
Hur använder man ADODB.Connection i VBA?
Krav:
Lägg till följande referenser till projektet:
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Förklara variabler
Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command
Skapa anslutning
a. med Windows-autentisering
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. med SQL Server-autentisering
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
Kör SQL-kommandot
Private Sub ExecuteCmd(sql As String)
mCmd.CommandText = sql
Set mRS = mCmd.Execute
End Sub
Läs data från postuppsättningen
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
Stäng anslutningen
Private Sub CloseConnection()
Call mDataBase.Close
Set mRS = Nothing
Set mCmd = Nothing
Set mDataBase = Nothing
End Sub
Hur man använder det?
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
Resultat
LeveransID: 1 Företagsnamn: Speedy Express Phone: (503) 555-9831
LeveransID: 2 Företagsnamn: United Package Phone: (503) 555-3199
AvsändarID: 3 Företagsnamn: Federal Fraktelefon: (503) 555-9931
AvsändarID: 4 Företagsnamn: snabb leverans Telefon: (503) 555-1234
Modified text is an extract of the original Stack Overflow Documentation
Licensierat under CC BY-SA 3.0
Inte anslutet till Stack Overflow