Fast Update of Access Data with Excel Data Using Excel Vba

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

Transmit Data to Access from Excel and then Run Update Query

Here is a template i use for adodb connections for running execute sql commands. I do realize that this is a template for a sql server connection but you already have the connection part set up so, just take what you need from this :)

Private Sub sqlupdate()

    Dim rng As Range, rcell As Range
    Dim vbSql As String, chkNum As String, var As String
    Dim cnn As ADODB.Connection

    Set rng = ThisWorkbook.Sheets("Sheet2").Range("F2:F754")
    For Each rcell In rng.Cells
        var2 = rcell.Value
        var = rcell.Offset(0, 5).Value
        vbSql = "UPDATE tbl SET column='" & var & "' WHERE othercol='" & var2 & "';"
        Set cnn = New Connection
        cnnstr = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=DBNAME;User ID=USERID;Password=PASSWORD; Trusted_Connection=No"
        cnn.Open cnnstr
        cnn.Execute vbSql
        cnn.Close
        Set cnn = Nothing
    Next rcell
End Sub

As far as suppressing the error message from access

 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual

& when done

 Application.ScreenUpdating = True
 Application.DisplayAlerts = True
 Application.Calculation = xlCalculationAutomatic

---

Related Topics