By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

Run Action Queries in VBA-TERADATA

P: 4
Subject:How to Run Action Queries in VBA-TERADATA??

Message:
hi,
Im trying in exceuting an action queris in Vba.
Im able to connect to teradata.
after connection,im imporitng through text pad the query that i wabt to run.
the problem is the text pad consists of many action queris.
the code im using is shown below.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3.  
  4. Public dTime As Date
  5.  
  6. Dim db As New ADODB.Connection
  7.  
  8. Dim rsT As New ADODB.Recordset
  9.  
  10. Dim rs1t As New ADODB.Recordset
  11.  
  12. Dim sPath As String ' here we put the path of the database
  13.  
  14. Dim ssql, ssql_1 As String 'your SQL Statement
  15.  
  16. Dim intcolIndex, cnt, i As Integer
  17.  
  18. Dim strImport As String
  19.  
  20. Dim lngChars As Long
  21.  
  22. Dim intFile As Integer
  23.  
  24. Dim lCount As Long
  25.  
  26. Sub krish()
  27.  
  28. 'dTime = Now + TimeValue("00:15:00")
  29.  
  30. 'Application.OnTime TimeValue("06:55:00"), "krish"
  31.  
  32. 'MsgBox "HelloWorld!"
  33.  
  34. Application.ScreenUpdating = False
  35.  
  36.  
  37. db.ConnectionString = "Provider=MSDASQL;DSN=p5450;Driver={Teradata};Database=iw_deflt_proddb_v;UID =****;PWD=****"
  38.  
  39. 'db.CommandTimeout = 5000
  40. '
  41. 'db.ConnectionTimeout = 5000
  42.  
  43. db.Open
  44.  
  45. intFile = FreeFile
  46.  
  47. Open "C:\Documents and Settings\p54g30\Desktop\TRC_SQL_1.0.txt" For Input As intFile
  48.  
  49. lngChars = LOF(intFile)
  50.  
  51. strImport = Input(lngChars, intFile)
  52.  
  53. ssql = strImport
  54.  
  55.  
  56.  
  57. With rs1t
  58.  
  59. .ActiveConnection = db
  60.  
  61. .LockType = adLockOptimistic
  62.  
  63. .CursorType = adOpenKeyset
  64.  
  65. .Open ssql
  66.  
  67.  
  68. Sheets("sample").Visible = True
  69.  
  70. Sheets("sample").Cells.Clear
  71.  
  72. For intcolIndex = 0 To rs1t.Fields.Count - 1 ' the field names
  73.  
  74.     Cells(1, intcolIndex + 1).Value = rs1t.Fields(intcolIndex).Name
  75.  
  76. Next
  77.  
  78. Sheets("sample").Range("1:1").Font.Bold = True
  79.  
  80. ActiveWindow.DisplayGridlines = True
  81.  
  82. cnt = 1
  83.  
  84. Do While Not .EOF
  85.  
  86. cnt = cnt + 1
  87.  
  88.   For i = 0 To rs1t.Fields.Count - 1
  89.  
  90.   Worksheets("sample").Cells(cnt, i + 1).Value = rs1t.Fields(i).Value
  91.  
  92.   Next i
  93.  
  94.     rs1t.MoveNext
  95.  
  96.  
  97. Loop
  98.  
  99. End With
  100.  
  101. End Sub
  102.  
  103.  
This is showing error like ODBC doesn't support the requested properties.
the problem is shown by in bold letters in the code.
pls help me in sorting out the problem.
im new and trying to work out with this for more thana a week.
thanks in advance
Apr 18 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.