Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import tables using VBA (2) (Access2003)

    Every month I am receiving text files that I am uploading manually into Access tables.
    I was asked to automate it and I need your help if you will, please.

    Here how it goes.

    I need to rename TABLE_ONE Prior into TABLE_ONE_Month_LastDay_Year (month is suppose to be 2 month ago from current)
    I need to rename TABLE ONE into TABLE_ONE Prior
    I need to Import text file - Import - Advanced - Specs (choose TABLE_ONE structure) - Next - Next - Next - No Primary Key - create TABLE_ONE table.
    DONE
    __________________________________________________ __________________________________________________ _

    I have 4 tables to Import data into - so my question is - how difficult it is to code it in using VBA?
    Can you help? Thanks so much

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (2) (Access2003)

    You could use this:

    DoCmd.Rename "TABLE_ONE_Month_LastDay_Year", acTable, "TABLE_ONE Prior"
    DoCmd.Rename "TABLE_ONE Prior", acTable, "TABLE_ONE"
    DoCmd.TransferText acImportDelim, "TABLE_ONE", "TABLE_ONE", "C:test.txt"

    where C:test.txt is the path and filename of the text file to import.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (2) (Access2003)

    I appreciate it so much! Will try, seems perfect to me...

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (2) (Access2003)

    One more....
    Sorry I forgot I am importing 2 tables as new tables. And 2 others into existing shell.
    Is code different if I am Importing as a new name table or if I am appending into Existing table?
    I need to delete from TABLE_OTHER and then import into the shell.

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (2) (Access2003)

    If you need to import into an existing table, you can specify the name of that table in the DoCmd.TransferText line. The records will be appended to the existing ones.
    If you need to delete records, you can use (for example)

    DoCmd.RunSQL "DELETE * FROM TABLE_OTHER"

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (2) (Access2003)

    Good Day!
    I am trying to rename a table with date that is 2 month prior to current month.
    I am using DoCmd.Rename "TBL" & " " & Format(DateSerial(myMonth - 2, myDay, myYear) , "mm_dd_yyyy"), acTable, "TBL Prior"

    but I am getting ridiculous dates like 1999 and 1996 - but not 2008.

    I had tried with no luck:


    Dim myMonth As Variant
    Dim myDay As Variant
    Dim myYear As Variant

    'myMonth = Month(Date)
    'myDay = Day(Date)
    'myYear = Year(Date)

    Where am I wrong?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import tables using VBA (2) (Access2003)

    DateSerial uses 3 parameters, year month and day in that order.

    What you have is subtracting 2 from what it thinks is the year.

    Try this:
    DoCmd.Rename "TBL" & " " & Format(DateSerial(myYear, myMonth - 2, myDay) , "mm_dd_yyyy"), acTable, "TBL Prior"

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (2) (Access2003)

    I am gettiing 09_30_1999 if declaration is
    Dim myMonth As Date
    Dim myDay As Date
    Dim myYear As Date

    thanks, anything else I can try?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (2) (Access2003)

    You should declare them as Long (or as Integer) since they are numbers, not as Date.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (2) (Access2003)

    Dim myMonth As Integer
    Dim myDay As Integer
    Dim myYear As Integer

    DoCmd.SetWarnings False


    DoCmd.Rename "TBL" & " " & Format(DateSerial(myYear, myMonth - 2, myDay), "mm_dd_yyyy"), acTable, "TBL Prior"


    Still renames into 09_30_1999....sorry.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (2) (Access2003)

    You don't assign any values to myYear, myMonth and myDay, so they are all 0.
    You should insert lines that assign the appropriate values to these variables.

Posting Permissions

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