Can't use a variable as column name in VBA Excel query with Access db

user_13474333 0
Viewed 278
Dim SQLStr As String

SQLStr = "UPDATE amenities SET [Accessible Parking On Site]='" & newName & _
            "' WHERE HostelKey='" & hostel & "';"

This query updates the access database in the right field. My problem is that I can't get it to work with the field [Accessible Parking On Site] being a variable. (newName and hostel are variables, I am using [] around the field name because some field names in the table have spaces).

ms-access vba excel sql
2 Answers

Similar to the variables you're already using...

Dim SQLStr As String, fieldName As String

fieldName = "Accessible Parking On Site"

SQLStr = "UPDATE amenities SET [" & fieldName & "]='" & newName & _
            "' WHERE HostelKey='" & hostel & "';"
user_4788840

Can you try it like this?

Sub ImportDataFromExcel()
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\your_path_here\Northwind.mdb"
    Set conn = New ADODB.Connection
    conn.Open strConn
    
    With Worksheets("Sheet1")
        lastrow = .Range("A2").End(xlDown).Row
        lastcolumn = .Range("A2").End(xlToRight).Column
        Set rng = .Range(.Cells(lastrow, 1), .Cells(lastrow, lastcolumn))
    End With
        
        'therow = 1
        
        For i = 2 To lastrow
            'r = rng.Row
            'If r > 1 Then
                strSQL = "UPDATE PersonInformation SET " & _
                    "AnewNamege=" & Worksheets("Sheet1").Range("A" & i).Value & " WHERE " & _
                    "hostel=" & Worksheets("Sheet1").Range("B" & i).Value
                conn.Execute strSQL
            'End If
            'r = r + 1
        Next i
        
    
    conn.Close
    Set conn = Nothing
End Sub

Also, set the appropriate reference... Go to Tools References, and check the box that says 'Microsoft ActiveX Data Objects 2.6 Object library'.

user_52126140
Related