运行Access 2016
我试图从Excel导入MS Access .mdb表中的数据。(我的客户使用的专有软件只能识别*.mdb文件。)当表格关闭时,当我运行这段代码时,会出现以下错误:
Run-Time Error 3061
Too few parameters - Expected 2
如果我在Access中打开表格时运行代码,有一半的时间会出现错误,而另外一半的时间则不会出现:
Run-Time error '3008'
The table 'Daily_Logs_of_Flows' is already opened exclusively by
another user, or it is already open through the user interface
and cannot be manipulated programmatically.
这似乎表明VBA有时会通过第一个错误。
我已经检查了变量名,并且由于StackOverflow上的这篇文章,在monthToImport之前和之后使用了单引号和数字符号(#)。错误从中消失了。
Expected 3
to
Expected 2
这里是代码
Sub importPLCDataFromAccess(monthToImport As Date)
Dim myDbLocation As String
myDbLocation = "K:\Users\WWTP Computer\Documents\POV_Projects\PLC Interface\PLC_Data.mdb"
DIM mySQLCall as String
Set myWorkbook = ActiveWorkbook
Set myDataSheet = myWorkbook.Worksheets("Page 1")
Set myEngine = New DAO.DBEngine
'Set myWorkspace = myEngine.Workspaces(0)
Set myDB = myEngine.OpenDatabase(myDbLocation)
' I deleted the workspace
' Set myDB = myWorkspace.OpenDatabase(myDbLocation)
mySQLCall = "SELECT Time_Stamp, GolfVolume, CreekVolume, InfluentVolume FROM Daily_Logs_of_Flows "
' Limit records to month requested...
mySQLCall = mySQLCall & "WHERE (DATEPART(m,Time_Stamp) = DATEPART(m,#" & monthToImport & "#)) "
' ... during the year requested
mySQLCall = mySQLCall & "AND (DATEPART(yyyy,Time_Stamp) = DATEPART(yyyy,#" & monthToImport & "#)) "
mySQLCall = mySQLCall & "ORDER BY Time_Stamp"
Debug.Print "mySQLCall = " & mySQLCall
Debug.Print "monthToImport: " & monthToImport
'Error occurs on next line where execute query & populate the recordset
Set myRecordSet = myDB.OpenRecordset(mySQLCall, dbOpenSnapshot)
'Copy recordset to spreadsheet
Application.StatusBar = "Writing to spreadsheet..."
Debug.Print "RecordSet Count = " & myRecordSet.recordCount
If myRecordSet.recordCount = 0 Then
MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No Data"
GoTo SubExit
End If
'....
End Sub
以下是当前SQL语句的Debug.Print:
mySQLCall = SELECT Time_Stamp, GolfVolume, CreekVolume, InfluentVolume FROM Daily_Logs_of_Flows WHERE (DATEPART(m,Time_Stamp) = DATEPART(m,#6/1/2016#)) AND (DATEPART(yyyy,Time_Stamp) = DATEPART(yyyy,#6/1/2016#)) ORDER BY Time_Stamp
有没有想法,我这里缺少了什么?提前感谢您的帮助。