CODEWITHSUNDEEP
sqlexcelvba
FabriManga
FabriManga

Reputation: 17

SQL query performed in VBA

I'm trying to connect Teradata Sql assistant to Excel through VBA. I'd like to write a query in VBA and print the result in a Sheet. I have gone through all the previous explanations but I still can't figure out why it's not working.

Dim strConn As String
strConn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=TERADATA"

Dim Query As String
Query = "select * FROM P_ZC074_TMIS.FACT_TMX_PL_NII_TP_FX where CNT_ORG ='5872196'"

Dim rs As New ADODB.Recordset
rs.Open Query, strConn

Sheet1.Range("A1").CopyFromRecordset rs

I have activated the Microsoft ActiveX Data Objects 6.1 Library but I receive a "Query Timeout Expired" error; I guess I have to use a CommandTimeout to fix this issue (assuming that it's just that) but I have no idea how to write that in VBA code.

Thanks in advance.

Upvotes: 0

Views: 854

Answers (1)

Gareth
Gareth

Reputation: 5243

You need to open a connection to the database first and pass that to the recordset that you're trying to open similar to this:

Public Sub GetData()

Dim oDB As ADODB.Connection: Set oDB = New ADODB.Connection
Dim oCM As ADODB.Command: Set oCM = New ADODB.Command
Dim oRS As ADODB.Recordset: Set oRS = New ADODB.Recordset
Dim strConn As String, strQuery As String

On Error GoTo Err:

strConn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=TERADATA"
strQuery = "select * FROM P_ZC074_TMIS.FACT_TMX_PL_NII_TP_FX where CNT_ORG ='5872196'"

oDB.Open strConn

With oCM
    .ActiveConnection = oDB
    .CommandType = adCmdText
    Set oRS = .Execute
End With

If Not oRS.BOF And Not oRS.EOF Then
    ThisWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS
End If

Err:

On Error Resume Next
oRS.Close
Set oRS = Nothing
oDB.Close
Set oDB = Nothing

MsgBox ("An error occurred!" & vbNewLine & "Error Number: " & Err.Number _
        & vbNewLine & "Error Message: " & Err.Description), vbCritical, "Error!"

End Sub

Upvotes: 1

Related Questions