Create Connection to (not Import from) Access from Excel

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.
Paul Cook-GilesSenior Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can use this below mentioned code in excel to get the value you are looking for..

Sub getdata()
    Dim conn As New Connection
    Dim rs As New Recordset, xselect As String
    Dim varstartdt As Date, varEndDt As Date
        strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=Your Database path here;"          '<----Like "C:\abc.accdb"

       varstartdt=your start date here
      varEndDt =your end date here
        conn.Open (strcon)
        xselect = " SELECT count(HolidayNa) as HL FROM HolidayTb " & _
                  "where HolidayDt between #" & varstartdt & "# And #" & varEndDt & "#"
        rs.Open xselect, conn, adOpenKeyset
        MsgBox rs.Fields("HL")
      Set rs = Nothing
      Set conn = Nothing
    End Sub

Open in new window

Note:- In order to run this code you need to go to excel reference and select Microsoft Activex data object 2.6 library from tools-->references..In excel 2007/2010 version...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can also define ActiveSheet.ListObject based on dynamic query:
    Filename = "C:\Documents\Database.accdb"
    StartDate = ActiveWorkbook.Sheets("Sheet1").Cells(1, 2).Value
    EndDate = ActiveWorkbook.Sheets("Sheet1").Cells(2, 2).Value
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
    Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Filename & ";Mode=Share Deny Write;", _
        "Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;", _
        "Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New ", _
        "Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Comp", _
        "act=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), _
        .CommandType = xlCmdSql
        .CommandText = "select count(*) from HolidayTb where HolidayDt between #" & StartDate & "# and #" & EndDate & "#"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "DbCount"
        .Refresh BackgroundQuery:=False
    End With

Open in new window

Start date in B1, end date in B2

PS. I'm not sure if all above code is needed - it was generated by macro recorder.
Paul Cook-GilesSenior Application DeveloperAuthor Commented:
Saurabh, thank you;  this is what I have now:
Public Function WorkDays(varStartDt As Variant, varEndDt As Variant) As Variant
'counts days (excluding weekend and holidays) between two dates
'requires the existence and maintenance of HolidayTb.HolidayNa and .HolidayDt

  Dim DiffCounter As Long, HolidaysCount As Long, CurrDateValue As Date
  Dim rs As New Recordset, xselect As String
   DiffCounter = 0
   CurrDateValue = varStartDt

If IsNull(varStartDt) Or IsNull(varEndDt) Then
   WorkDays = Null
   strcon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=J:\SampleDBs\Access\CookGilesDevelopment.accdb;"          '<----Like "C:\abc.accdb"
   conn.Open (strcon)
   xselect = " SELECT count(HolidayNa) as HL FROM HolidayTb where HolidayDt between #" & varStartDt & "# And #" & varEndDt & "#"
   rs.Open xselect, conn, adOpenKeyset
      HolidaysCount = rs.Fields("HL")

'loop starts here
    Do While CurrDateValue < varEndDt
    CurrDateValue = CurrDateValue + 1
    If Weekday(CurrDateValue) >= 2 And Weekday(CurrDateValue) <= 6 Then DiffCounter = DiffCounter + 1

    WorkDays = (DiffCounter - HolidaysCount)
End If
End Function

Open in new window

When I try calling it from a Worksheet, it breaks on   rs As New Recordset , and says Compile Error:  User-Defined type not defined.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Paul Cook-GilesSenior Application DeveloperAuthor Commented:
I'm also interested in opinions as to Best Practice, and whether this is the most efficient way to get the count that I need.
it breaks on   rs As New Recordset
Have you inserted reference to MS ADO as Saraubh wrote?
Saurabh Singh TeotiaCommented:
Like slubek said.. go to tools-->references and add -->Microsoft Activex data object 2.6 library

To run this code...

In additional if you are fetching data from access/sql i can tell you this is one of the best way you can find to get the data and it's efficient as well..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.