locked
Excel VBA RRS feed

  • Question

  • I need help in excel vba code to connect sql database with excel. I want to first execute select query and a button to ask for update and then update query and then confirmation of update.

    TIA

    Wednesday, November 1, 2017 11:44 AM

Answers

  • Hello Megh_123,

    >>i started getting correct value at cell A2 , then I added update query in code then again at A2 I am '& NRno&' . 

    It seems that you could execute the select sql but failed to execute the update sql, right? I would suggest you check your update sql string.

    It seems that you missed double quotes around & NRno &, please try to add these quotes and test again.

    UpdStr = "Update tab_name set message_state = '" & NRno & "' WHERE fk_ari_rec_id in(SELECT ari.record_id FROM tabname  WHERE receipt_no ='" & Rno & "')"
    'print out the sql to check if it is correct
    Debug.Print UpdStr

    If you still have issue to execute the update sql, since your issue seems to be resolved partly and this thread was post too long ago, I would suggest you mark helpful reply to close this thread and post a new thread about updating sql so that more new eyes will see your issue and give you some helpful suggestions.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Megh_ 123 Wednesday, December 20, 2017 1:46 PM
    Thursday, November 30, 2017 8:45 AM

All replies

  • Hi Megh_123,

    You could use ADO connection in VBA to connect to SQL Server and the do the query/update string. Use Open method to do a query and return result to a record set and use Execute method do executes the update SQL string.

    Here are some simply code.

    'remember add reference to Microsoft ActiveX Data Object Library
    Dim Cn As ADODB.Connection
    
    Set Cn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    
    Cn.Open "driver={SQL Server};Server=servername;Database=TestDb;Trusted_Connection=True;"
    
    SQLStr = "Use NetApp; Select * from [Vendor_List] [Vendor]" ' Enter your SQL here
    
    rs.Open SQLStr, Cn, adOpenStatic
    
    'for excuate sqlstring you could use Execute method
    
    'Cn.Execute SQLStr

    You could refer to below links for more help.

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-ado-with-sql-server-native-client

    https://social.msdn.microsoft.com/Forums/office/en-US/7d3b13f5-18d4-4b21-992c-ec785a4167d4/trying-to-read-a-table-from-sql-server-using-vba?forum=exceldev#91c21dea-3e0d-43c7-8db4-5890095905b2

    https://social.msdn.microsoft.com/Forums/office/en-US/ec231280-53ed-45ee-a7cf-67deb7b16129/need-to-connect-to-sql-server-2017?forum=exceldev

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Terry Xu - MSFT Wednesday, November 8, 2017 6:32 AM
    Thursday, November 2, 2017 6:24 AM
  • Hi Terry,

    Can we store the column value at a particular cell . I want to run update query also which will take this call value and update that value from database.

    if select query runs and give output as 1, then I need to update this value in database to 2.


    Megh_ 123

    Tuesday, November 28, 2017 10:35 AM
  • I am also getting error as "Invalid SQL Statement ORA 00900" in rs.open command

    Megh_ 123

    Tuesday, November 28, 2017 11:01 AM
  • Hi Megh_123,

    >>I am also getting error as "Invalid SQL Statement ORA 00900" in rs.open command

    What's your query string? Could it work in SQL Server? Please try to execute your query string in SQL Server to check if it is valid SQL string.

    Best Regards,

    Terry


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, November 29, 2017 9:16 AM
  • Yes , it is working in SQL server


    Megh_ 123

    Wednesday, November 29, 2017 9:23 AM
  • Hello,

    I search for the error message. It seems an error which often occurs when executing a procedure. Did you call a procedure? Could you share us your code?

    Best Regards,

    Terry


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, November 29, 2017 9:49 AM
  • Hi Terry,

    I worked on the sql code. The error I am getting is in the user entered value that I want to take in sql query(select state from...). Please find below my code.I want a receipt no (like RCT00000801) to be entered by user. This receipt number I want to use in sql code and execute it. This sql query will give me a state like 1, I want to update that state too in vba code. I have not written update query in code now. Kindly help me getting user entered value for select query and completing the update command.

    Please find the code below

     Private Sub CommandButton1_Click()
    MsgBox "Welcome to ABC Tool"
    Dim Rno As Variant
     Dim cn As ADODB.Connection
     Dim rs As ADODB.Recordset
      Rno = InputBox("Enter Receipt No", "Receipt Number")
    MsgBox "Receipt Number entered is " & Rno & " that you entered"
    Set rs = New ADODB.Recordset
     Set cn = New ADODB.Connection
     cn.Open "provider= msdaora; Database= Oracle 11 ;data source =ABC; user id=ABC; password =ABC; Trusted_connection=True"
     SQLStr = "SELECT query ..where receipt no= "& Rno&" "
     cn.Execute SQLStr
     rs.Open SQLStr, cn, adOpenStatic
     
     Sheet1.Range("A2").CopyFromRecordset rs
     rs.Close
    cn.Close
     Set cn = Nothing
    Set rs = Nothing

    End Sub



    Megh_ 123

    Wednesday, November 29, 2017 12:47 PM
  • In the above code how should I write the Rno  in select query so that what receipt number user entered will be pulled from select query. Can you please check the declaration once for Rno ,I think this might also be the issue.

    Megh_ 123

    Wednesday, November 29, 2017 1:22 PM
  • Hi Sir ,

    Please find my updated code with update query added

    Private Sub CommandButton1_Click() 


     MsgBox "Welcome to  Tool" 

     Dim Rno As Variant 
     Dim cn As ADODB.Connection 
     Dim rs As ADODB.Recordset 
     Dim rrs As ADODB.Recordset 
      
     Rno = InputBox("Enter Receipt No", "User Input Response", "a") 

     MsgBox "Receipt Number entered is " & Rno & " that you entered" 

      
     Set rs = New ADODB.Recordset 
     Set cn = New ADODB.Connection 
      
     cn.Open "provider= msdaora; Database= Oracle 11 ;data source =ABC; user id=ABC; password =ABC;Trusted_connection=True" 
      
     SQLStr = "SELECT QUERY where receipt_no ='" & Rno & "'" 
     cn.Execute SQLStr 
     rs.Open SQLStr, cn, adOpenStatic 
     Sheet1.Range("A2").CopyFromRecordset rs 
      
     rs.Close 
      
     'MsgBox ("Receipt Number state is " & SQLStr & " from the database" )
     Reply = MsgBox(Prompt:="Do you want to change message state?", Buttons:=vbYesNo, Title:="Update Value") 
     If Reply = vbYes Then 
     NRno = InputBox("Please enter the new Message state value", "User Input Response", 1) 
     MsgBox "New Message State entered is " & NRno & " that you entered" 

    'update query using 
     UpdStr = "Update tab_name set message_state = ' & NRno & ' WHERE fk_ari_rec_id in(SELECT ari.record_id FROM tabname  WHERE receipt_no ='" & Rno & "')" 
     cn.Execute UpdStr 
    MsgBox "Message state is updated" 

    'need to check again the from select query if value is updated or not

    SSQLStr = "SELECT aim.message_state FROM agx_irt_receipt_item airi ,agx_irt_message aim WHERE airi.record_id  = aim.fk_ari_rec_id AND airi.receipt_no ='" & Rno & "'" 
    cn.Execute SSQLStr 
     rrs.Open SSQLStr, cn, adOpenStatic 
     
    'want updated value at A3 
    Sheet1.Range("A3").CopyFromRecordset rrs 
    rss.Close 

    ElseIf Reply = vbNo Then 

    Sheet1.Range("A2").ClearContents 
    Sheet1.Range("A3").ClearContents 

    cn.Close 
    Set cn = Nothing 
    Set rs = Nothing 
    End If 
    End Sub 



    errors in this code: i started getting correct value at cell A2 , then I added update query in code then again at A2 I am '& NRno&' . also update query is not being executed and no output is seen at a3 cell


    Megh_ 123

    Wednesday, November 29, 2017 3:38 PM
  • Hello Megh_123,

    >>i started getting correct value at cell A2 , then I added update query in code then again at A2 I am '& NRno&' . 

    It seems that you could execute the select sql but failed to execute the update sql, right? I would suggest you check your update sql string.

    It seems that you missed double quotes around & NRno &, please try to add these quotes and test again.

    UpdStr = "Update tab_name set message_state = '" & NRno & "' WHERE fk_ari_rec_id in(SELECT ari.record_id FROM tabname  WHERE receipt_no ='" & Rno & "')"
    'print out the sql to check if it is correct
    Debug.Print UpdStr

    If you still have issue to execute the update sql, since your issue seems to be resolved partly and this thread was post too long ago, I would suggest you mark helpful reply to close this thread and post a new thread about updating sql so that more new eyes will see your issue and give you some helpful suggestions.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Megh_ 123 Wednesday, December 20, 2017 1:46 PM
    Thursday, November 30, 2017 8:45 AM