Extraction of Data in Access(.mdb) Based on Date

[JERRY89]

Dear All,

Is there any method I can extract Microsoft Access (mdb format) data based on the DATE & ACCESS TABLE to Excel. I need an Example so that i can use in my file. For example in Excel i key a date from 01/07/2020 till 24/07/2020, then it can let me select which Microsoft Access file and display out all the Available table so i can choose which to Extract.

[HansV]

That is a broad/vague problem. One table may contain zero, one or multiple date fields. If you don't know in advance which table you want, this appears a rather pointless exercise...

[JERRY89]

Hi Hans,

If i know which table to extract and the date field..Is there a sample for me to work on it...

[HansV]

In the Visual Basic Editor, select Tools > References, and tick the check box for 'Microsoft Office n.0 Access database engine Object Library'.
(n depends on your version of Office)

You can then use code like this (change the path and name of the database, the name of the table and the name of the date field):

Code: Select all

Sub ExtractData()
    Dim strPath As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Long
    Dim dtmFrom As Date
    Dim dtmTo As Date
    Dim strSQL As String
    strPath = "C:\Databases\MyDatabase.mdb"
    Set dbs = DBEngine.OpenDatabase(strPath)
    dtmFrom = DateValue("01/07/2020")
    dtmTo = DateValue("24/07/2020")
    strSQL = "SELECT * FROM Orders WHERE OrderDate Between #" & _
        Format(dtmFrom, "yyyy-mm-dd") & "# And #" & _
        Format(dtmTo, "yyyy-mm-dd") & "#"
    Set rst = dbs.OpenRecordset(strSQL)
    Application.ScreenUpdating = False
    For i = 1 To rst.Fields.Count
        Cells(1, i).Value = rst.Fields(i - 1).Name
    Next i
    Range("A2").CopyFromRecordset rst
    Application.ScreenUpdating = True
    rst.Close
    dbs.Close
End Sub

Instead of hard-coding the start and end dates, you can get them from cells on a worksheet, of course.

[JERRY89]

Hi Hans,

Thanks a lot for your code it is very good and efficient.