VBA DAO.OpenRecordSet不一致的错误

Question

运行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不会越过第一个错误的时候。

由于this post on *，我已经检查过变量名称，并且在monthToImport之前和之后都使用了单引号和数字符号（＃）。从

Expected 3 

去

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 

对我错过的任何想法在这里？在此先感谢您的帮助。

Answer 1

问题是DATEPART函数需要引号中的第一个参数，否则它会查找字段yyyy或m。

例如：

DATEPART("yyyy", #6/1/2016#) 

或

DATEPART("m", #6/1/2016#) 

总数：

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 

为此在VBA（在你不知道的情况下，但我猜你喜欢），每次拨打DATEPART函数时只需加双引号即可...

例如：

mySQLCall = mySQLCall & "AND (DATEPART(""yyyy"",Time_Stamp)...." 

只要是完整的，运行时错误“3008”实际上是第一个错误....访问不会尝试运行任何SQL，直到它可以决定了其具有适当的权限。