Update an Excel Sheet Using Vba/Ado

Update an excel sheet using VBA/ADO

Get rid of IMEX=1 from your connection string. That works for me.

.ConnectionString = "Data Source=" & ActiveWorkbook.Path & constConfigFile & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

VBA ADO Update Query

I have made a similar test with an update and a join, just for fun, and it worked perfectly. Here is my code:

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$]  inner join [Sheet2$] on [Sheet1$].test1 = [Sheet2$].test1  SET [Sheet1$].test3 = [Sheet2$].test3 ")

    Set rs = Nothing
    Set con = Nothing
End Sub

Perhaps all you need is this ;ReadOnly=False; in your connect string ?

Note that , despite the name I use for the driver, this works in a .XLSM file.

VBA-SQL UPDATE/INSERT/SELECT to/from Excel worksheet

I'm posting the solution here since I can't mark his comment as the answer.

---

Thanks to @Jeeped in the comments, I now feel like an idiot. It turns out three of my field names were using reserved words ("name", "date", and "in"). It always seems to be a subtle detail that does me in...

I renamed these fields in my worksheet (table) and altered the appropriate code. I also had to Cast the input strings into the proper data types. I'm still working the rest of the details out, but here's the new query:

qry = "INSERT INTO <tbl> (empName, empDay, inTime, outTime, vac, sales)" & vbNewLine & _
                  "VALUES (CStr('<name>'), CDate('<date>'), CDate('<in>'), CDate('<out>'), " & _
                      "CBool('<vac>'), CDbl(<sales>));"

I needed the CDate() (instead of the #*#) so I could pass in a string.
So CDate('<date>') instead of #<date>#

Update Excel data via SQL UPDATE

Didn't think that would be all to make it work...

Answer
Connections to Excel files are set to ReadOnly as default.

You have to add ReadOnly=False to your Connection String.

More information can be found at Microsoft Support

Excel VBA ADO UPDATE SQL Table/Record

Ok so I got the results I wanted.
The records are being updated as expected using a loop option in the vba.
It may not be the prettiest way of doing what I was trying to achieve but it works.

If anyone would like to suggest improvements then I am all ears.
But here is my final macro:

Private Sub UpdateTable()

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim strWard As String
Dim strDate As Date
Dim strUser As String

'=====USER NAME VBA================
    Set WSHnet = CreateObject("WScript.Network")
    UserName = WSHnet.UserName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
    UserFullName = objUser.FullName
'===================================

'strDate is a stamp of the date and time when the update button was pressed
strDate = Format(Now(), "dd/mm/yyyy hh:mm:ss")
'User is the username of the person logged into the PC the report is updated from
strUser = UserFullName

'Set Connection string
Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=ServerName; " & _
            "Initial Catalog=dbName;" & _
           **USER DETAILS HERE**

'Connect and Run the SQL statement to update the reords.
cnn.Open cnnstr
Dim row As Range
For Each row In [tbl_data].Rows
    uSQL = "UPDATE Breach_Test_Key SET [VAL_BREACH_REASON] = '" & (row.Columns(row.ListObject.ListColumns("VAL_BREACH_REASON").Index).Value) & _
    "' ,[VAL_BREACH_DETAIL] = ' " & (row.Columns(row.ListObject.ListColumns("VAL_BREACH_DETAIL").Index).Value) & _ 
    "' WHERE  [ID] = '" & (row.Columns(row.ListObject.ListColumns("ID").Index).Value) & "'"

    'Debug.Print uSQL
cnn.Execute uSQL
Next

cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Updating records in Access table using excel VBA

My below code is working fine. I tried to address your above three points in a different way.

##########################

IMPORTANT

1) I have removed your other validations; you can add them back.
2) DB path has been hard coded, you can set it to get from a cells again
3) My DB has only two fields (1) ID and (2) UserName; you will have obtain your other variables and update the UPDATE query.

Below is the code which is working fine to meet your all 3 requests...Let me know how it goes...

Tschüss :)

Sub UpdateDb()

'Creating Variable for db connection
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\db.accdb;"

Dim a, PID

'a is the row counter, as it seems your data rows start from 2 I have set it to 2
a = 2

'Define variable for the values from Column B to R. You can always add the direct ceel reference to the SQL also but it will be messy.
'I have used only one filed as UserName and so one variable in column B, you need to keep adding to below and them to the SQL query for othe variables
Dim NewUserName

'########Strating to read through all the records untill you reach a empty column.
While VBA.Trim(Sheet19.Cells(a, 1)) <> "" ' It's always good to refer to a sheet by it's sheet number, bcos you have the fleibility of changing the display name later.
'Above I have used VBA.Trim to ignore if there are any cells with spaces involved. Also used VBA pre so that code will be supported in many versions of Excel.

        'Assigning the ID to a variable to be used in future queries
        PID = VBA.Trim(Sheet19.Cells(a, 1))

       'SQL to obtain data relevatn to given ID on the column. I have cnsidered this ID as a text
        sSQL = "SELECT ID FROM PhoneList WHERE ID='" & PID & "';"

        Set rs = New ADODB.Recordset
        rs.Open sSQL, cn

          If rs.EOF Then

                'If the record set is empty
                'Updating the sheet with the status
                Sheet19.Cells(a, 19) = "ID NOT FOUND"
                'Here if you want to add the missing ID that also can be done by adding the query and executing it.

            Else

                  'If the record found
                  NewUserName = VBA.Trim(Sheet19.Cells(a, 2))
                  sSQL = "UPDATE PhoneList SET UserName ='" & NewUserName & "' WHERE ID='" & PID & "';"
                  cn.Execute (sSQL)

                  'Updating the sheet with the status
                  Sheet19.Cells(a, 19) = "Updated"

          End If

       'Add one to move to the next row of the excel sheet
       a = a + 1

 Wend

cn.Close
Set cn = Nothing

End Sub

---

Related Topics