VBA DAO.OpenRecordSet Inconsistent Errors

Question

Running Access 2016

I am attempting to import data from an MS Access .mdb table from Excel. (The proprietary software my client uses only recognizes *.mdb files.) When I run this code when the table is closed, I get the error:

Run-Time Error 3061
Too few parameters - Expected 2

If I run the code when the table is open in Access, HALF the time, I get that error and half the time I get:

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.

That seems to indicate that VBA gets past the first error sometimes.

I have checked variable names and have used both single quotations in and number signs (#) before and after monthToImport because of this post on StackOverflow. The error went from

Expected 3 

to

Expected 2

Here is the code

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  

Here is the Debug.Print of SQL statement as currently reads:

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

Any thoughts on what I am missing here? Thanks in advance for your help.

Answer 1

The problem is that the DATEPART function needs the first parameter in quotes, otherwise it looks for the field yyyy or m.

For example:

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

or

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

In total:

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

To do this in VBA (in case you don't know, but I'm guessing you do), just double up the quotation marks each time you call the DATEPART function...

For example:

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

Just to be complete, Run-Time error '3008' is actually the first error....Access won't attempt to run any SQL until it can determine that it has the proper permissions.