Results 1 to 8 of 8
  1. #1
    mannym is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    4

    Nick the new guy needs help with Access

    Hello,
    My name is actually Manny, not Nick but am the new guy. I am trying to learn access on the go ( not much experience with code, little with macros) and am having issues with a few steps; adding a file name to each record I am importing to a single table and putting a manual date on each record. These records are coming from text files with IMB (intelligent mail barcode) files. Each record has 31 characters, each file can have 1 to 1,000,000 records. I will be adding new files on a daily basis and removing files older than 45 days. In Access, I am cutting each record into 5 columns: Barcode ID, STID, MID, Serial number, and Delivery point. I want to add the file name and date column when importing. I am using Access 2007. Is anyone willing to give a newby some help?

    Thank you in advance for your help.


    Manny from Illinois

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is a very broad question. Code can perform the import and update fields. Exact structure of code depends on data source and method of import. Probably more than one way to accomplish. There are many examples of various methods.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mannym is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    4
    The main purpose of this database is to monitor the uniqueness of the IMB for a 45 day period. Each day, I will have to drop the 46th day of data.

    Basically, I have text files (fixed) dropped to an FTP site. I pull them onto our server into a folder. From there I would like access to scan the folder for new files on my command and append them to a single table in the database. While uploading, I need the file name on each record. This is needed when duplicates are discovered and I need to provide feedback to a client. I hope I covered the missing info.



    Thanks again for your assistance.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Exactly how are you doing the import now - import wizard? What is the file ext - csv, txt?

    Add the date and filename fields to table. Import records to existing table. Run UPDATE query to populate the two fields. How much you want to automate depends on how much code you want write.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mannym is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    4
    Quote Originally Posted by June7 View Post
    Exactly how are you doing the import now - import wizard? What is the file ext - csv, txt?

    Add the date and filename fields to table. Import records to existing table. Run UPDATE query to populate the two fields. How much you want to automate depends on how much code you want write.
    Yes, Import Wizard for txt files. I have tried to do the update query. I haven't had it work yet. I am using 2007. btw

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What does "haven't had it work" mean - error message, wrong result, nothing happens? Post the attempted query SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mannym is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2018
    Posts
    4
    Nothing happens.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post the query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums