Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    77

    Unanswered: Macro to Import Table

    I have a macro that imports a table from a network drive. I have hard coded in the path. However, each year file location changes. Is there a way in a Macro to adjust the code based on some other parameter?

    Under the "TransferDatabase" action, for the "Database Name", I hard coded:

    y:\sidewalks\2006\swk_master.mdb

    However, I want the 2006 to automatically come from some other location. Here’s what I tried to no avail (where I wanted the dlookup to return 2006):

    y:\sidewalks\(DLookUp("[year]","[tblYear]","[Item]='year'"))\swk_master.mdb

    Thanks,
    SKK

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're asking dlookup to return the field [year] where the field [Item] contains the literal string 'year'? What does that backend look like exactly? Will your year parameter be based on todays date in some way? Maybe you could use intrinsic date functions?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Posts
    77
    There is a table called tblYEAR that only has one field in it called YEAR. This field is used throught many form, and report titles. The YEAR field currently has 2005 in it but will become 2006 for next years data. The 2005 database will be copied into a 2006 folder (all data will be cleared, and we'll start over for next year's construction).

    If possible, I wanted to have the path in the macro for the table transfer to come over automatically based on what's in the YEAR field of the tblYEAR table.

    Thanks,
    SKK

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Please, PLEASE reconsider how you're overall concept for tracking years.

    Very, very seldom does it make any sense to start with a completely new database for a given time period. Real world example, the primary contract I'm working now is consolidating databases that where created fresh for each quarter. Now they need to develop trend statistics for the last 4 years... how do you do it? All the data is in different databases entirely...

    You can pull the current year by using datepart("y", now()).

    I don't know what the overall design looks like for you db, but I'll tell you from experience that a large portion of my consulting contracts are fixing problems that are the result of this type of logic...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2004
    Posts
    77
    Point well taken...

    Thanks again Teddy,
    SKK

Posting Permissions

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