+ Reply to Thread
Results 1 to 12 of 12

Access Database Import Data From Specific Date Range

  1. #1
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Access Database Import Data From Specific Date Range

    Hi Team,

    I have an Excel sheet that I use to important data from an access database but I would like to control the data that it imports.. Basically, the database contains a column that contains the date that entry has been enterred in the database. When I import my data onto excel, I would like it to import the data from a given date range the user would setup. I would imagine that this would have to be through VBA so let's say I have a UserForm that contains 2 DTPickers where a user would input the date range.

    I have little experience with SQL queries, can you help me with this type of query?

    I tried the code below but it gives me a compile error : User-defined type not defined on line "Dim dbConnection As ADODB.Connection"


    Thanks





    Please Login or Register  to view this content.

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Access Database Import Data From Specific Date Range

    You will need to go to the reference library and select "Microsoft ActiveX Data Objects X.X Library"
    Make the following are also selected,
    Microsoft Access X.X Object Library (or later)
    Microsoft DAO X.X Object Library
    Choose the latest.

    Cheers
    Last edited by JapanDave; 12-28-2015 at 11:49 AM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    Hi JapanDave,

    I tried that and now the code starts running but i get an object required error on line "Sheet1.Range("A2").CopyFromRecordset nsltrackerreport"

    Also, can you help with the SQL query respecting the date range selection?

    Thanks

  4. #4
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Access Database Import Data From Specific Date Range

    Tell what this does,
    Also what are the fields in the nsltrackerreport.TBL?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    Hi, I'm now getting a user-defined type not defined error on line "Dim FSO As New FileSystemObject"

  6. #6
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Access Database Import Data From Specific Date Range

    In the reference library check, "Microsoft Scripting Runtime"

  7. #7
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    Yeah that worked It was able to import all the table. So now all that's left is for it to import data submitted in the date range selected by user in Userform.

    The name of the column in access is called SubmitDate

    Thank for your help

  8. #8
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,132

    Re: Access Database Import Data From Specific Date Range

    Here is an alternative solution you may be interested in:

    http://datapigtechnologies.com/blog/...ults-to-excel/
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    Hi Thank again..I reviewed that method and although it's practical, it wouldn't suite the needs in the environment I'm working in where the user will not have direct access to the database table via Access.


    I actually tried the SQL Statement below and it worked to get all entries stamped with the date in DTPickerCtrl1

    Please Login or Register  to view this content.



    Then I tried to accomplish what I initially wanted (Date Range) with the below code :

    Please Login or Register  to view this content.

    but this is not pulling anything.

    Any help on this?

  10. #10
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Access Database Import Data From Specific Date Range

    Try this,

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    Alright so that somewhat works The reason why i say somewhat its that for example I have 3 entries in the Access Database...One for the 8th, another for the 9th and another for the 10th of December 2015..

    When selecting the date range from the 1st of December to the 11th of December, nothing appears....When I select the 1st to the 13th, all 3 appear...It's being weird...any idea why?


    PS: I will include the code for how the values are actually processed to the database from another application.





    Please Login or Register  to view this content.


    And here's the code for when I import the data to excel based on selected date range with your latest mods:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-22-2011
    Location
    Laval quebec
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Access Database Import Data From Specific Date Range

    I actually got it to work :

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to: Extract specific data from an Access Database
    By JPWRana in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2013, 03:38 PM
  2. Replies: 3
    Last Post: 05-30-2013, 04:29 AM
  3. Is it possible to import data to excel from an access database online?
    By Rob* in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-16-2012, 04:22 AM
  4. Import database query that has a date range
    By trigger05 in forum Excel General
    Replies: 3
    Last Post: 06-18-2008, 12:16 PM
  5. Open an Access database Import data like Microsoft Query
    By hilander in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2007, 06:55 PM
  6. How to import data from Access Database.
    By vidi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2006, 02:35 PM
  7. Replies: 0
    Last Post: 03-15-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1