不能使用变量作为具有访问数据库的 VBA Excel 查询中的列名

Question

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

该查询更新右字段中的access数据库。我的问题是，我不能得到它的工作与领域[Accessible Parking On Site]是一个变量。(newName和hostel是变量，我在字段名周围使用[]，因为表中的一些字段名有空格)。

Answer 1

与您已经使用的变量类似…

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

Answer 2

你能这样试试吗?

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_hereNorthwind.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

另外，设置适当的引用…转到"工具参考"，选中"Microsoft ActiveX Data Objects 2.6 Object library"。