I need to connect a spreadsheet to an Access database, and get a count of the number of events in a table that occur between two dates in cells in the worksheet.
On the Data tab, I click the "From Access" button. A dialog box opens, I navigate to and select my database, and click Open. The Select Table dialog box opens, I select the table that the data I need, and click OK. The ImportData dialog box appears. The Only Create Connection option is greyed out. I do not want to import the data from Access to Excel. I only want to pass two parameters through the connection, and get a number back.
The VBA in Access looks like this:
intHolidayCount = DCount("HolidayNa", "HolidayTb", "HolidayDt between #" & varStartDt & "# And #" & varEndDt & "#")
I expect the VBA in Excel will look similar, with the Start and End dates coming into the function from cell references.