asked on

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.

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slubek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul Cook-Giles

ASKER

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
Else
   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")
   rs.Close
   conn.Close

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

    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.

Paul Cook-Giles

ASKER

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.

SOLUTION

slubek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

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..