how to retrieve data from access between to date ranges using vba

[narsing rao]

Hi Team,

i have created user form to enter data into excel data base that is connected to ms access, now i am trying to retrieve data from access data base , i have created group box with from date and to date with button to press but i am not sure how to write the code which select the from date and todate and retrieve the data between that date range.
below is the pic, i have used date picker to select date range.



can anyone help in suggesting the code for it

Regards,
Narsing Rao

 

[Chihiro]

One way is to use ADO or MS Query. Depending on your Excel Version you may want to try PowerQuery (Get & Transform).

In all instance, you can use native query language and write something like...

Select * From Table1 Where DateColumn Between #7/4/2017# and #7/18/2017#

Or

Select * From Table1 Where DateColumn >=#7/4/2017# and <=#7/18/2017#

Links:
ADO
http://www.globaliconnect.com/excel...to-execute-sql-statements&catid=79&Itemid=475

PowerQuery
https://support.office.com/en-us/ar...database-a3d6500c-4bec-40ce-8cdf-fb4edb723525

MS Query
https://support.office.com/en-us/ar...nal-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

 

[narsing rao]

Thanks for the links ad information

i am using office 2016

 

[Chihiro]

Then I'd definitely go with PowerQuery (Get & Transform) to connect to Access and get data. As long as you don't need to consider backward compatibility.

You can pass parameter using named range or other methods. If you search in the forum, you'll find few examples.

One such example:
http://chandoo.org/forum/threads/complex-data-rearrange.30459/#post-182012

 

[narsing rao]

Hi Chihiro,

PowerQuery is the only way we can do it or is there in VBA code to retrieve the data, i tried ADO method but i didnt worked for me.

 

[Chihiro]

PowerQuery is by far the easiest method (or MS Query for backward compatibility. Excel 2007 and older).

Otherwise you'd need to use ADO in VBA. Provided you have appropriate credentials.
If you are accessing accdb file you'd need to use "Microsoft.ACE.OLEDB.12.0" as provider. If using older file with ".mdb" extension, then you'd need to use "Microsoft.JET.OLEDB.4.0" as provider.

Sample code using Northwind.accdb

Sub Demo()
Dim cn As Object, rst As Object
Dim sDate As Date, eDate As Date
Dim strQuery As String
Set cn = CreateObject("ADODB.Connection")
sDate = DateSerial(2006, 3, 30)
eDate = DateSerial(2006, 4, 25)
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\Test\Northwind.accdb"
    .Open
End With

strQuery = "SELECT * FROM [Inventory Transactions] Where [Transaction Created Date] Between #" & sDate & "# and #" & eDate & "#"
Set rst = CreateObject("ADODB.Recordset")
rst.Open strQuery, cn, 1, 3
[A1].CopyFromRecordset rst
rst.Close
cn.Close
End Sub

 

[narsing rao]

Hi Chihiro,

Thanks for sharing code, in tried power query and its very easy and nothing to do with macros,but still i am trying to do something which is very easy for my team where they just select the date range and click the button to retrieve the data.i checked your code with few modification, there are no error messages of any thing still its not pulling the data, its seems its not taking the date range properly from the cells.no i am trying something from excel which can retrieve the data which seems working for me.

Sub DataBasedOnDate()

Application.ScreenUpdating = False

Dim StartDate, EndDate As Date
Dim MainWorksheet As Worksheet
Dim dtTodayDate As String

StartDate = Sheets("Macro").Range("D6").Value
EndDate = Sheets("Macro").Range("D7").Value

Set MainWorksheet = Worksheets("database")

MainWorksheet.Activate

Range("F1").CurrentRegion.Sort _
      key1:=Range("F1"), order1:=xlAscending, _
       Header:=xlYes

Range("F1").CurrentRegion.AutoFilter Field:=6, Criteria1:= _
        ">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

ActiveSheet.AutoFilter.Range.Copy

dtTodayDate = Format(Date, "mmm-dd-yyyy")
On Error GoTo MakeSheet
    Sheets(dtTodayDate).Activate
    Exit Sub
MakeSheet:
    Sheets.Add , Worksheets(Worksheets.Count)
    ActiveSheet.Name = dtTodayDate

ActiveSheet.Paste

Selection.Columns.AutoFit

Range("F1").Select

MainWorksheet.Activate

Selection.AutoFilter

Sheets("Macro").Activate

End Sub

as you have hard coded the date in code , how can i select the date range from selection calendar or date picker, its seems in 2016 date add in is not available.

 

[Chihiro]

Hmm? Dateadd is available in 2016.
For an example, 6 month ago from today.

Debug.Print DateAdd("m", -6, Date)

You can also get dates from cell. You just have to be careful of format and type.
See below for an example.

Sub Demo()
Dim cn As Object, rst As Object
Dim sDate As Date, eDate As Date
Dim strQuery As String
Set cn = CreateObject("ADODB.Connection")
sDate = [A2].Value
eDate = [B2].Value
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=C:\Test\Northwind.accdb"
    .Open
End With

strQuery = "SELECT * FROM [Inventory Transactions] Where [Transaction Created Date] Between #" & sDate & "# and #" & eDate & "#"
Set rst = CreateObject("ADODB.Recordset")
rst.Open strQuery, cn, 1, 3
[D1].CopyFromRecordset rst
rst.Close
cn.Close
End Sub