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).
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 & "';"
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'.
SQL.