Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: insert sql result into variable in vba Excel

  1. #1
    Join Date
    22nd March 2007
    Posts
    21

    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 at 23:16. Reason: include code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd March 2007
    Posts
    21

    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 at 23:14. Reason: include code tags

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st September 2010
    Posts
    10,362

    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]...

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd March 2007
    Posts
    21

    Re: insert sql result into variable in vba Excel

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: November 29th, 2011, 20:38
  2. Assigning formula result to variable
    By dlyulko in forum EXCEL HELP
    Replies: 1
    Last Post: September 24th, 2010, 19:01
  3. Insert Line Break In Formula Result
    By def98 in forum EXCEL HELP
    Replies: 5
    Last Post: September 4th, 2007, 01:10
  4. Automatically Insert Columns With Query Result
    By jacobwarren in forum Excel and/or Access Help
    Replies: 7
    Last Post: January 11th, 2007, 03:56
  5. storing the query result in a variable
    By amritduttgautam in forum Excel and/or SQL Help
    Replies: 1
    Last Post: June 22nd, 2006, 01:16

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno