无效的参数错误:MSACCESS和SQL [英] Invalid Argument Error: MSAccess and SQL
问题描述
我想从MSACCESS我的SQL数据库访问特定的线条和我一直在这条线得到一个无效的参数错误:
设置RS = CurrentDb.OpenRecordset(选择TIMEID&放大器; _
FROM tblLunchTime与& _
WHERE ProductionID = prodSelect和结束时间为null,并且开始时间和LT; DATEADD('H',3,NOW())[dbSeeChanges])
是什么不对的呢?
私人小组cmdClockEnd_Click()
检查组已被选中。
如果frmChoice.value = 0则
MsgBox请选择一条生产线。
结束
结束如果
用户输入设置形式。
lblEnd.Visible = TRUE
保存价值的午餐结束。
lblEnd.Caption =格式(现在,MMM / DD / YY HH:MM:SS AMPM)
'声明数据库变量。
昏暗DBNAME作为DAO.DATABASE
昏暗strValuesQuery作为字符串
昏暗的RS作为DAO.Recordset
昏暗prodSelect作为字符串
昏暗sSQL作为字符串
昏暗TIMEVALUE作为字符串
设置数据库名= CurrentDb
获取生产线的值。
如果frmChoice.value = 1,则
prodSelect =L2
elseif的frmChoice.value = 2那么
prodSelect =L3
结束如果
'获取最后TIMEID使用以下参数。
sSQL =SELECT TIMEID&放大器; _
FROM tblLunchTime与& _
WHERE ProductionID = prodSelect和结束时间为null,并且开始时间和LT;#&放大器;使用DateAdd(H,3,现在())及#
设置RS = dbName.OpenRecordset(sSQL,dbSeeChanges)
strValuesQuery = _
UPDATE tblLunchTime&放大器; _
SET结束时间='&放大器;现在和放大器; '与& _
WHERE TIMEID =&放大器; RS [TIMEID]放!;
关闭警告消息了。
DoCmd.SetWarnings假
执行查询。
DoCmd.RunSQL strValuesQuery
重新打开警告消息。
DoCmd.SetWarnings真
结束小组
似乎有大约tblLunchTime混乱......无论是本机Jet / ACE表或其他数据库的链接表。请告诉我们此命令的输出:
Debug.Print CurrentDb.TableDefs(tblLunchTime)。连接
您可以粘贴该行到即时窗口和preSS回车键,显示响应。 (您可以用CTRL + G组合键打开立即窗口。)
以防万一的反应开始的 ODBC 的,建议你试试这条线在code:
设置RS = CurrentDb.OpenRecordset(sSQL,dbOpenDynaset,dbSeeChanges)
更新:既然你过去这一关,建议你改变你的UPDATE语句的方法。不要关闭警告;尝试这样的事情,而不是:
执行查询。
CurrentDb.Execute strValuesQuery,dbFailOnError
和添加错误处理程序来处理由dbFailOnError捕获的任何错误。
I am trying to access certain lines from my SQL database from MSAccess and I keep getting an Invalid Argument Error on this line:
Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _
"FROM tblLunchTime " & _
"WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())", [dbSeeChanges])
Is something not right in this?
Private Sub cmdClockEnd_Click()
'Check if a group has been selected.
If frmChoice.value = 0 Then
MsgBox "Please select a production line."
End
End If
'Setup form for user input.
lblEnd.Visible = True
'Save end of lunch value.
lblEnd.Caption = Format(Now, "MMM/DD/YY hh:mm:ss AMPM")
'Declare database variables.
Dim dbName As DAO.Database
Dim strValuesQuery As String
Dim rs As DAO.Recordset
Dim prodSelect As String
Dim sSQL As String
Dim timeValue As String
Set dbName = CurrentDb
'Get values of Production Line.
If frmChoice.value = 1 Then
prodSelect = "L2"
ElseIf frmChoice.value = 2 Then
prodSelect = "L3"
End If
'Get the last TimeID with the following parameters.
sSQL = "SELECT TimeID " & _
"FROM tblLunchTime " & _
"WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < #" & DateAdd("h", 3, Now()) & "#"
Set rs = dbName.OpenRecordset(sSQL, dbSeeChanges)
strValuesQuery = _
"UPDATE tblLunchTime " & _
"SET EndTime = '" & Now & "'" & _
"WHERE TimeID = " & rs![TimeID] & " "
'Turn warning messages off.
DoCmd.SetWarnings False
'Execute Query.
DoCmd.RunSQL strValuesQuery
'Turn warning messages back on.
DoCmd.SetWarnings True
End Sub
There appears to be confusion about tblLunchTime ... whether it is a native Jet/ACE table or a link to a table in another database. Please show us the output from this command:
Debug.Print CurrentDb.TableDefs("tblLunchTime").Connect
You can paste that line into the Immediate Window and press the enter key to display the response. (You can open the Immediate Window with CTRL+g keystroke combination.)
Just in case the response starts with "ODBC", suggest you try this line in your code:
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
Update: Now that you're past that hurdle, suggest you change your approach with the UPDATE statement. Don't turn off warnings; try something like this instead:
'Execute Query. '
CurrentDb.Execute strValuesQuery, dbFailOnError
And add an error handler to deal with any errors captured by dbFailOnError.
这篇关于无效的参数错误:MSACCESS和SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!