(Solved) – On pressing enter, the query should update the database using VBA

Sub dataconfigupdate()

Dim cnn1 As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim iCols As Integer
    Const DRIVER = "{SQL Server}"
    Dim sserver As String
    Dim ddatabase As String
    Dim iRowNo As Integer

    Worksheets("Settings").Range("H32") = Now

    sserver = Worksheets("Settings").Range("D11").Value
    ddatabase = Worksheets("Settings").Range("D12").Value

    Set cnn1 = New ADODB.Connection
    cnn1.ConnectionString = "driver= " & DRIVER & ";server=" & sserver & ";database=" & ddatabase & ""
      cnn1.ConnectionTimeout = 30
      With Worksheets("EnterConfig")
      If Application.SendKeys("Enter") Then

 cnn1.Execute "delete from [dbo].[orders]"

'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sname = .Cells(iRowNo, 1)
            splace = .Cells(iRowNo, 2)
            sthing = .Cells(iRowNo, 3)

            'Generate and execute sql statement to import the excel rows to SQL Server table
            cnn1.Execute "insert into [dbo].[orders] (Name, Place, thing) values ('" & sname & "', '" & splace & "', '" & sthing & "')"

            iRowNo = iRowNo   1

       End With

       MsgBox "Data updated."

       Worksheets("Settings").Range("D32") = Now

End Sub

I am trying to execute a code in VBA which should execute on pressing of the ENTER key. I have written an if condition If Application.SendKeys("Enter") Then but it is not executing properly. Otherwise the code runs fine if I run it on pressing of the button.
As I am new to VBA am stuck on implementation of this functionality. Please help me.

Leave a Reply

Your email address will not be published. Required fields are marked *