• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

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-Giles
Paul Cook-Giles
  • 2
  • 2
  • 2
3 Solutions
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...

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-GilesAccess/VBA/SQL Database 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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Paul Cook-GilesAccess/VBA/SQL Database 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..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now