+ Reply to Thread
Results 1 to 3 of 3

Archiving specific records to access

  1. #1
    emerb
    Guest

    Archiving specific records to access

    Hello,
    I have a workbook with 8 spreadsheets for different business units. These
    spreadsheets contain lists of customer information. The two key columns in
    these spreadsheets are 'START-DATE' and 'ARCHIVE-DATE'. The start-date is
    entered by the user and the archive-date is simply that start-date plus 7
    days.

    I need to automate the task of selecting the rows where the 'ARCHIVE DATE'
    column is equal to todays date. These records will be deleted from the
    spreadsheet and archived in an access database. I was hoping to set it up so
    that when the workbook is opened, this archiving process will be automated.
    What would be the best way around this problem?

    All suggestions will be gratefully received

  2. #2
    Myrna Larson
    Guest

    Re: Archiving specific records to access

    Do you have the necessary code to extract the data?

    One possible sequence of actions would be

    1. use Data/AutoFilter to display only records with the current date in the
    Archive column

    2. copy these records to another sheet

    3. delete them from the main sheet

    4. do whatever is necessary to import the copied records into Access

    For me, assuming you want to also automate step 4, that's the hardest part.

    It may be possible to import records directly from Excel into Access with a
    criterion such as the date. I'm not good enough with Access programming to
    know. If that is true, you would not need steps 1-3. If that works, then your
    2nd task would be to delete the records from Excel (easy).

    Once you have the macro written, you would call it from the Workbook_Open
    event macro.

    If you follow the steps above, you could turn on the macro recorder as you do
    it manually to get an idea of what the code looks like. Then you would modify
    it to change the filter criterion to the current date. (BTW, what if a date
    got skipped? Would you want to archive all records with dates <= current
    date?)

    On Mon, 21 Feb 2005 04:09:06 -0800, "emerb" <emerb@discussions.microsoft.com>
    wrote:

    >Hello,
    >I have a workbook with 8 spreadsheets for different business units. These
    >spreadsheets contain lists of customer information. The two key columns in
    >these spreadsheets are 'START-DATE' and 'ARCHIVE-DATE'. The start-date is
    >entered by the user and the archive-date is simply that start-date plus 7
    >days.
    >
    >I need to automate the task of selecting the rows where the 'ARCHIVE DATE'
    >column is equal to todays date. These records will be deleted from the
    >spreadsheet and archived in an access database. I was hoping to set it up so
    >that when the workbook is opened, this archiving process will be automated.
    >What would be the best way around this problem?
    >
    >All suggestions will be gratefully received



  3. #3
    Jamie Collins
    Guest

    Re: Archiving specific records to access

    Myrna Larson wrote:
    > It may be possible to import records directly from Excel into Access

    with a
    > criterion such as the date.


    You can bypass MS Access and put the data directly into the database
    e.g.

    INSERT INTO MyArchive ([START-DATE], [ARCHIVE-DATE], <other columns
    here>)
    SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
    FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit1$]
    WHERE [ARCHIVE-DATE] = DATE()
    UNION ALL
    SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
    FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit2$]
    WHERE [ARCHIVE-DATE] = DATE()
    UNION ALL
    SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
    FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit3$]
    WHERE [ARCHIVE-DATE] = DATE()
    UNION ALL
    ....
    UNION ALL
    SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
    FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit8$]
    WHERE [ARCHIVE-DATE] = DATE();

    Perhaps the criterion required is <= DATE(), though.

    Jamie.

    --


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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