当我使用 vba 插入记录时，Access 2016 不断提示输入值

Question

我是 Access 的新手。我有 2 个表：所有候选人(ID、姓氏、名字(和候选步骤(ID、步骤、接收日期、结果( 我的脚本是从 excel 文件中读取一对姓氏，名字。 如果不存在，则将"姓氏"、"名字"插入第一个表，将"步骤"、"接收日期"、"结果"插入到第二个表。 运行时，Access 会不断提示我输入每个字段值，并使用该值放入表中，而不是我从 Excel 传入的值。 有人可以阐明我做错了什么吗？ 多谢。

Public Sub LoadExcelToAccess(xlPath As String)
Dim xlApp As Object
Dim xlWrk As Object
Dim xlSheet As Object
Dim i As Long
Dim sql, sql2 As String
Set xlApp = VBA.CreateObject("Excel.Application")
Set xlWrk = xlApp.Workbooks.Open(xlPath)
Set xlSheet = xlWrk.Sheets("Sheet1")
For i = 2 To 4
Dim rID
'check if the person is already in the DB
Set rID = CurrentDb.OpenRecordset("Select ID from AllCandidates where LastName='" & xlSheet.cells(i, 1) & "' and FirstName = '" & xlSheet.cells(i, 2) & "'")
If rID.EOF Then
'person is not in DB, insert it into the AllCandidates db
MsgBox "ID Not found"
sql = "Insert Into [AllCandidates] (LastName, FirstName) VALUES (" & xlSheet.cells(i, 1).Value & "," & xlSheet.cells(i, 2).Value & ")"
DoCmd.RunSQL sql
End If
rID.Close
'now that the person is surely there, insert the steps status into the CandiateSteps table
Set rID = CurrentDb.OpenRecordset("Select ID from AllCandidates where LastName='" & xlSheet.cells(i, 1) & "' and FirstName = '" & xlSheet.cells(i, 2) & "'")
'get the last ID that were inserted in the AllCandidates table
Dim fld As Field
Set fld = rID.Fields(0)
MsgBox "item in row ID = " & fld
sql2 = "Insert into [CandidateSteps] (ID, Step, DateReceived, Result) VALUES (" & fld & ", " & xlSheet.cells(i, 3).Value & ", " & xlSheet.cells(i, 4).Value & ", " & xlSheet.cells(i, 5).Value & ")"
DoCmd.RunSQL sql2
rID.Close
Next i
'make sure to dispose of objects
xlWrk.Close
xlApp.Quit
Set rID = Nothing
Set fld = Nothing
Set xlSheet = Nothing
Set xlWrk = Nothing
Set xlApp = Nothing
End Sub

Answer 1

正如@TimWilliams提到的，您正在为该问题查询传递不带引号的字符串文字(姓氏和名字(，有趣的是，您确实使用姓氏和名字为所有其他查询包含引号。

但是，与其仅仅包装引号并解决您的问题，不如考虑MS Access的querydef参数化，它不需要任何引号或字符串连接。下面是使用参数的记录集调用(SELECT语句(和操作查询(INSERT、UPDATE、DELETE语句(的示例。请集成到您的代码中。

参数化查询定义记录集

Dim qdef As Querydef 
...
' PREPARED STATEMENT WITH DEFINED PLACEHOLDERS
sql = "PARAMETERS LastNameParam TEXT(255), FirstNameParam TEXT(255);" _ 
& " SELECT ID from AllCandidates WHERE LastName=[LastNameParam] and FirstName=[FirstNameParam]"
' ASSIGN TO TEMP QUERY OBJECT (ABOVE CAN BE A SAVED QUERY)
Set qdef = CurrentDb.CreateQueryDef("", sql)
' BIND PARAMS
qdef!LastNameParam = xlSheet.cells(i, 1)   
qdef!FirstNameParam = xlSheet.cells(i, 2)  
' OPEN QUERY'S RECORDSET
Set rID = qdef.OpenRecordset()
...
rID.Close: qdf.Close
Set qdf = Nothing

参数化 Querydef Action Query(替换 DoCmd.RunSQL(

' PREPARED STATEMENT WITH DEFINED PLACEHOLDERS
sql = "PARAMETERS LastNameParam TEXT(255), FirstNameParam TEXT(255);" _
& " INSERT INTO [AllCandidates] (LastName, FirstName) VALUES ([LastNameParam], [FirstNameParam])"
' ASSIGN TO TEMP QUERY OBJECT
Set qdef = CurrentDb.CreateQueryDef("", sql)
' BIND PARAMS
qdef!LastNameParam = xlSheet.cells(i, 1)   
qdef!FirstNameParam = xlSheet.cells(i, 2)  
' EXECUTE ACTION
qdef.Execute dbFailOnError
qdf.Close
Set qdf = Nothing

顺便说一下，由于您是初学者，参数化是任何关系数据库(MySQL，SQL Server，Oracle，Postgres，DB2，SQLite(通过应用程序层编码运行SQL查询的行业最佳实践。回想一下鲍比表。虽然这看起来像是更多的工作和更多的行，但代码更安全，可以说更易于维护，甚至更有效。