Announcement

Collapse
No announcement yet.

insert sql result into variable in vba Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • insert sql result into variable in vba Excel



    Hi all,

    My need is to insert sql result into variable in vba Excel.
    Here is my code:

    Code:
     
    Sub ghghghghg()
    Dim objADO
    Dim objRs
    Dim strSQL
    Dim j As Integer
    Set objADO = CreateObject("ADODB.Connection")
    objADO.Properties("Prompt") = 2 'Prompt Always
    objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=servername; UID=; PWD=;"
    strSQL = "SELECT count(*) INTO '" & j & "' from tablename "
    objADO.BeginTrans
    objADO.Execute strSQL
    objADO.CommitTrans
    objADO.Close
    Set objADO = Nothing
    End Sub


    The execution interrupts at line "objADO.Execute strSQL" with error message:

    Run-time error '-2147217900 (80040e14)':
    [Microsoft][ODBC driver for Oracle][Oracle]ORA-00905: missing keyword

    What I'm missing?

    Another question is how do I tune "Prompt" property in order to ask username and password only once (do not repeat prompt untill user closes Excel).

    Thanks ahead.
    Last edited by Efes; October 14th, 2011, 22:16. Reason: include code tags

  • #2
    Re: insert sql result into variable in vba Excel

    My problem is solved, I've got sql result into variable.
    I still need to tune "Prompt" property in order to ask username and password only once (do not repeat prompt untill user closes Excel).

    Code:
    Sub rscount()
    Dim objADO As ADODB.Connection
    Dim strSQL
    Dim rs As ADODB.Recordset
    Dim j As Integer
    Worksheets("ttttt").TextBox16.Text = ""
    Set objADO = CreateObject("ADODB.Connection")
    Set rs = New ADODB.Recordset
    objADO.Properties("Prompt") = 2
    On Error Resume Next
    objADO.Open "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=b2oracle.test; UID=ABS; PWD=;"
    If Err.Number = -2147217842 Then
    MsgBox "Interrupted by user"
    Exit Sub
    End If
    If Err.Number = -2147467259 Then
    MsgBox "Server is absent"
    Exit Sub
    End If
    If Err.Number = -2147217843 Then
    MsgBox "Incorrect username or password"
    Exit Sub
    End If
    On Error Resume Next
    rs.Open "SELECT count(*) as CNT from zoo.marco_tmp_131011d", objADO
    If Err.Number = -2147217865 Then
    MsgBox "Insufficient privileges"
    Exit Sub
    End If
    On Error GoTo 0
    j = rs(0).Value
    rs.Close
    Set rs = Nothing
    objADO.Close
    Set objADO = Nothing
    Worksheets("ttttt").TextBox16.Text = j
    MsgBox "Number of rows in table: " & j
    End Sub
    Last edited by Efes; October 14th, 2011, 22:14. Reason: include code tags

    Comment


    • #3
      Re: insert sql result into variable in vba Excel

      Please edit your posts to include code tags rather than quote tags around your code listings - code tags are required by the rules you agreed to when you joined the forum.

      You can add code tags by replacing the quote tags ([quote] & [/quote]) with [code] & [/code]...

      Comment


      • #4
        Re: insert sql result into variable in vba Excel

        Originally posted by cytop View Post
        Please edit your posts to include code tags rather than quote tags around your code listings - code tags are required by the rules you agreed to when you joined the forum.

        You can add code tags by replacing the quote tags () with [code] & [/code]...
        I've edited my posts. Hope it's ok now.

        Comment


        • #5


          Re: insert sql result into variable in vba Excel

          You can use an InputBox for the password to store this as a variable to be used in the connection string. Also you're better off using the Oracle Provider instead of the Microsoft one.

          HTH

          C

          Comment

          Working...
          X