当我使用 vba 执行 SQL 时出现输入错误

Question

>我的 RunSQL 语句似乎遇到了问题，我不断收到以下错误

查询

输入必须至少包含一个表或查询

我确定这是一个简单的错误，但我现在没有看到它。

Private Sub Command13_Click()
Dim SQL_Text As String
Dim reccount As Long
Dim dbs As Database
Dim rs As DAO.Recordset
'Assign count of PO's
Set dbs = OpenDatabase("Y:DatabasesBulk databaseFabric_Data.mdb")
Set rs = dbs.OpenRecordset("SELECT * FROM PO;")
rs.MoveLast
reccount = rs.RecordCount
rs.Close
'IF PO count is greater than 0 the table is Drop and PO's are recounted in prep to be inserted
If recount <> 0 Then
DoCmd.RunSQL ("DROP [PO_#];")
DoCmd.RunSQL ("SELECT [P0_#] INTO PO FROM Fabric_Release;")
Else
DoCmd.RunSQL ("SELECT [PO_#] INTO PO FROM Fabric_Release;")
End If
Set rs = Nothing
'Insert new PO data
SQL_Text = "INSERT INTO Fabric_Release ([PO_#], Supplier, [Fabric Name], [Fabric_#], Order_Qty, ETD) VALUES ('" & Me![PO_#] & "', '" & Me.Supplier & "', '" & Me![Fabric Name] & "', '" & Me![Fabric_#] & "', '" & Me.Order_Qty & "', '" & Me.ETD & "')" & " " & _
"WHERE '" & Me![PO_#] & "' NOT IN (SELECT [PO_#] FROM PO);"
MsgBox (SQL_Text)
DoCmd.RunSQL (SQL_Text)
End Sub

Answer 1

正如@KenWhite所评论的那样，使用SQL参数化的最佳实践(这个概念在MS Access和VBA之外广为人知，但适用于所有数据库和所有与SQL连接的编程语言)。这样做可以避免将 VBA 变量连接到带有引号外壳的 SQL 字符串的混乱甚至危险的需要。

具体来说，MS Access 支持在 DAO 中使用 SQL 中的PARAMETERS子句进行参数化，该子句可以通过 QueryDefs.Parameters 与应用程序层值绑定。因此，请通过以下方式调整您的最后一次RunSQL通话：

...
Dim qdef As QueryDef
' DEFINE PREPARED SQL (NO VBA DATA) WITH DEFINED COLUMN TYPES
SQL_Text = "PARAMETERS [prmPO] AS TEXT, [prmSupplier] AS TEXT, [prmFabricName] AS TEXT," _ 
& "         [prmFabricNum] AS INTEGER, [prmOrderQty] AS LONG, [prmETD] AS DATE; " _
& "INSERT INTO Fabric_Release ([PO_#], Supplier, [Fabric Name], [Fabric_#], Order_Qty, ETD)" _
& " VALUES ([prmPO], [prmSupplier], [prmFabricName], [prmFabricNum], [prmOrderQty], [prmETD])"
' INITIALIZE QUERYDEF
Set qdef = CurrentDb.CreateQueryDef("", SQL_Text)
' Set qdef = CurrentDb.QueryDefs("mySavedParamQuery")  ' FOR SAVED QUERY WITH ABOVE SQL
' BIND PARAMETERS
qdef![prmPO] =  Me![PO_#]                   ' ASSUMED TO BE A TEXT
qdef![prmSupplier] = Me.Supplier            ' ASSUMED TO BE A TEXT 
qdef![prmFabricName] = Me![Fabric Name]     ' ASSUMED TO BE A TEXT
qdef![prmFabricNum] = Me![Fabric_#]         ' ASSUMED TO BE A NUMBER 
qdef![prmOrderQty] = Me.Order_Qty           ' ASSUMED TO BE A NUMBER
qdef![prmETD] = Me.ETD                      ' ASSUMED TO BE A DATE/TIME VALUE
' EXECUTE ACTION QUERY
qdef.Execute
' UNINTIALIZE QUERYDEF
Set qdef = Nothing

---

更好的是，您可以使用已保存的 SQL 查询，该查询指向表单控件，该查询具有可以直接从打开的表单控件读取的DoCmd.OpenQuery调用：

SQL(调整表单名称并在下面另存为 MS Access 查询)

INSERT INTO Fabric_Release ([PO_#], Supplier, [Fabric Name], [Fabric_#], Order_Qty, ETD)
VALUES (Forms!myForm![prmPO], Forms!myForm![prmSupplier], Forms!myForm![prmFabricName], 
Forms!myForm![prmFabricNum], Forms!myForm![prmOrderQty], Forms!myForm![prmETD])

VBA

Private Sub Command13_Click()
' AVOID DROP/MAKE TABLE QUERIES FOR DELETE/INSERT
DoCmd.RunSQL "DELETE FROM [PO]"
DoCmd.RunSQL "INSERT INTO [PO] ([PO_#]) SELECT [PO_#] FROM Fabric_Release"
DoCmd.OpenQuery "mySavedQuery"                 ' NO NEED TO CLOSE ACTION QUERIES
End Sub