excel-vba
SQL en Excel VBA - Mejores Prácticas
Buscar..
¿Cómo usar ADODB.Connection en VBA?
Requisitos:
Agregue las siguientes referencias al proyecto:
Biblioteca Microsoft ActiveX Data Objects 2.8
Biblioteca de Microsoft ActiveX Data Objects Recordset 2.8
Declarar variables
Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command
Crear conexion
a. con autenticación de 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
segundo. con autenticación de 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
Ejecutar comando sql
Private Sub ExecuteCmd(sql As String)
mCmd.CommandText = sql
Set mRS = mCmd.Execute
End Sub
Leer datos del conjunto de registros
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
Conexión cercana
Private Sub CloseConnection()
Call mDataBase.Close
Set mRS = Nothing
Set mCmd = Nothing
Set mDataBase = Nothing
End Sub
¿Cómo usarlo?
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
Resultado
ShipperID: 1 CompanyName: Speedy Express Teléfono: (503) 555-9831
ShipperID: 2 CompanyName: United Package Phone: (503) 555-3199
ShipperID: 3 CompanyName: Federal Shipping Phone: (503) 555-9931
ShipperID: 4 CompanyName: speedy shipping Teléfono: (503) 555-1234
Modified text is an extract of the original Stack Overflow Documentation
Licenciado bajo CC BY-SA 3.0
No afiliado a Stack Overflow