Trying To Extract Data From Access Using Excel VBA

[RunTime91]

Greetings All...

Full disclosure - This was cross-posted on an Access Forum as I was uncertain where to go with this post..Thanks


Both code snippets are written in Excel VB Editor and are designed to simply extract data from Access and place it on a specific Excel sheet in a Specific Range


The below code works perfectly when I hard code the date

Sub RefreshData3()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim RecCount As Long
Dim SQL As String

'Setup Reference To Workbook And Sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets("Sheet1")
Application.ScreenUpdating = False
xlSheet.Range("Q7:Z9").Clear
'Communicate With User
Application.StatusBar = "Connection to an External Database..."
'Application.Cursor = xlWait
Set Db = OpenDatabase(DbLoc)
        
StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI," & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1)) " & _
        "GROUP BY vProductionDetails.Entered " & _
        "HAVING (((vProductionDetails.Entered) = ""12-19-2018"")) " & _
        "ORDER BY vProductionDetails.Entered DESC;"


'Execute Query
Set Rs = Db.OpenRecordset(StrSQL, dbOpenSnapshot)

'Copy RecordSet to SpreadSheet
Application.StatusBar = "Writing to SpreadSheet..."
If Rs.RecordCount = 0 Then
  Msgbox "There Are No Records To Retrieve", vbInformation + vbOKOnly, "No Data"
  GoTo SubExit
  
Else
  Rs.MoveLast
  RecCount = Rs.RecordCount
  Rs.MoveFirst
End If
xlSheet.Range("Q7:Z7").CopyFromRecordset Rs

Once I had the above working I assigned the 'Date' to a variable using a date (same date - 12/19/2018) plugged in the one of the Excel sheets
With the code below I get the "There Are No Records To Retrieve" error (which is an error 'cause there are records)

Sub RefreshData3()
On Error GoTo SubError
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim RecCount As Long
Dim SQL As String


Dim RunDate As Date
RunDate = Sheets("DataEntry").Range("D4")


'Setup Reference To Workbook And Sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets("Sheet1")
Application.ScreenUpdating = False
xlSheet.Range("Q7:Z9").Clear
'Communicate With User
Application.StatusBar = "Connection to an External Database..."
'Application.Cursor = xlWait
Set Db = OpenDatabase(DbLoc)

StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI, " & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1)) " & _
        "GROUP BY vProductionDetails.Entered " & _
        "HAVING ((vProductionDetails.Entered) = " & RunDate & ") " & _
        "ORDER BY vProductionDetails.Entered DESC;"
        
Set Rs = Db.OpenRecordset(StrSQL, dbOpenSnapshot)

'Copy RecordSet to SpreadSheet
Application.StatusBar = "Writing to SpreadSheet..."
If Rs.RecordCount = 0 Then
  Msgbox "There Are No Records To Retrieve", vbInformation + vbOKOnly, "No Data"
  GoTo SubExit
  
Else
  Rs.MoveLast
  RecCount = Rs.RecordCount
  Rs.MoveFirst
End If
xlSheet.Range("Q7:Z7").CopyFromRecordset Rs

I've tested the Date variable in the Immediate - It's correct
I've surrounded the date in the SQL string with every character combination I can think of (single & double quotes, double-double quotes, hashtags, hashtags & quotes - you get the idea) Still all I can return is the No Records error...

So clearly I'm missing something..


Help?


Thanks so much...

 

[ranman256]

cant you just create an excel connection, then click import?

 

[Norie]

Have you tried formatting the date and enclosing it in #?

StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI, " & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1)) " & _
        "GROUP BY vProductionDetails.Entered " & _
        "HAVING ((vProductionDetails.Entered) = #" & Format(RunDate,"yyyy-mm-dd") & "#) " & _
        "ORDER BY vProductionDetails.Entered DESC;"