Conectando-se ao DB2

Conectando-se ao DB2

Abaixo está uma pequena rotina VBA executada no MS Excel para consultar uma tabela usando o driver de ODBC. Precisa limpar um pouco, declarar minhas variáveis, etc, mas funciona.

Eu sempre achei difícil deixar um exemplo completo disso na Web. Tenho certeza que existe algum exemplo completo disso por aí, mas quis deixar minha contribuição breve. Essa versão é boa devido ao usuário precisar de uma conexão ODBC em sua máquina. Automaticamente limitando o acesso cliente a um login que deva funcionar.

Sub transfer()

Dim varState As String

Set CS = CreateObject(“ADODB.Connection”)Set RS = CreateObject(“ADODB.Recordset”)

‘Retrieve values from Cells on the sheet to use as selections in the query belowvarState = ActiveSheet.Range(“C2”).ValuevarFrom_Date = ActiveSheet.Range(“B3”).ValuevarTo_Date = ActiveSheet.Range(“B4”).Value‘ ISeries connection String‘ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0”ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;Library=PWRDTA41;QueryTimeout=0”

CS.Open (ConnectString)

SqlString = “SELECT hhicusn , C.FFDCNMB, C.FFDSTEB, hhiclsn, SUM(hhiqysa), SUM(hhiexsn), SUM(hhiexac) ” & _” FROM pwrdta41.hhiorddp ” & _” left outer join PWRDTA41.FFDCSTBP c” & _” ON hhicusn = c.ffdcusn and” & _” hhidivn = c.ffddivn and” & _” c.ffdcmpn = hhicmpn and” & _” c.ffddptn = hhidptn” & _” WHERE hhidtei between ” & varFrom_Date & ” and ” & varTo_Date & _” and hhiclsn = ‘105’ and c.ffdsteb = ‘” & varState & “‘” & _” GROUP BY hhicusn, c.ffdcnmb, c.ffdsteb, hhiclsn” & _” ORDER BY hhicusn”

‘Message box can be used for debugging the SQL statement‘MsgBox (SqlString)

RS.Open SqlString, CS

‘Clear Previous contents of CellsCells.SelectActiveSheet.Range(“A7:Z65535”).ClearContents‘copy the Recordset to excel sheet starting at A7ActiveSheet.Range(“A7”).CopyFromRecordset RS

‘Close Connection and RecordSetRS.CloseCS.Close‘Reset cell back to A1ActiveSheet.Range(“A1”).Select

End Sub

Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

            

Conectando-se ao Oracle

Conectando-se ao Oracle

Use o código abaixo para se conectar ao Banco de Dados Oracle:

Sub Ora_Connection()  

Dim rs As ADODB.Recordset  Dim query As String  Set con = New ADODB.Connection  Set rs = New ADODB.Recordset  '---- Replace below highlighted names with the corresponding values  strCon = "Driver={Microsoft ODBC for Oracle}; " & _  "CONNECTSTRING=(DESCRIPTION=" & _  "(ADDRESS=(PROTOCOL=TCP)" & _  "(HOST=Your Host Name)(PORT=Port Number))" & _  "(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"  '---  Open   the above connection string.  con.Open (strCon)  '---  Now connection is open and you can use queries to execute them.   '---  It will be open till you close the connection  

End Sub  

Dim con As ADODB.Connection  

Oracle Connection String with Service:


Sub Ora_Connection()  

Dim con As ADODB.Connection  Dim rs As ADODB.Recordset  Dim query As String  Set con = New ADODB.Connection  Set rs = New ADODB.Recordset  '---  Replace below highlighted names with the corresponding values  strCon = "Driver={Microsoft ODBC for Oracle}; " & _  "CONNECTSTRING=(DESCRIPTION=" & _  "(ADDRESS=(PROTOCOL=TCP)" & _  "(HOST=Your Host Name)(PORT=Enter Port Number))" & _  "(CONNECT_DATA=(SERVICE_NAME=database))); uid=Enter User ID; pwd=Enter Password;"  '---  Open the above connection string.  con.Open (strCon)  '---  Now connection is open and you can use queries to execute them.  '---  It will be open till you close the connection  

End Sub 

Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

            

Conectando-se ao SQL Server - Connect to SQL Server

Conectando-se ao SQL Server - Connect to SQL Server

Não se esqueça:
Tools -> References -> Microsoft ActiveX Data Objects 2.8 Library



Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=MyDatabaseName;" & _
                  "Integrated Security=SSPI;"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM Table1;")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub

Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com