Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

    VB:
     
    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 22: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).

    VB:
    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 22:14. Reason: include code tags

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st September 2010
    Posts
    7,920

    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, 19:38
  2. Assigning formula result to variable
    By dlyulko in forum EXCEL HELP
    Replies: 1
    Last Post: September 24th, 2010, 18:01
  3. Insert Line Break In Formula Result
    By def98 in forum EXCEL HELP
    Replies: 5
    Last Post: September 4th, 2007, 00:10
  4. Automatically Insert Columns With Query Result
    By jacobwarren in forum Excel and/or Access Help
    Replies: 7
    Last Post: January 11th, 2007, 02: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, 00: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