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.

I don't know the answer, but the "Exclusive" error is actually the "first" error, so when you get Runtime Error 3061, you've actually gotten past that error, not the other way around. — OpiesDad
– OpiesDad, Commented Jul 12, 2016 at 23:21
What happens when you take the Debug.Print of the SQL statement and run it directly in Access? — OpiesDad
– OpiesDad, Commented Jul 12, 2016 at 23:24

OpiesDad · Accepted Answer · 2016-07-13 15:25:09Z

2

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.

edited Jul 13, 2016 at 15:25

answered Jul 12, 2016 at 23:27

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dbmitch Over a year ago

Same thing for DATEPART("m" in earlier part of SQL (which you got in your example)

OpiesDad Over a year ago

Yeah, I should have been more clear about needing to do it for both. Edited to clarify.

PhillipOReilly Over a year ago

I would have been stalled for a long time finding that! Thanks! Did the trick.

Collectives™ on Stack Overflow

VBA DAO.OpenRecordSet Inconsistent Errors

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related