Results 1 to 6 of 6
  1. #1
    Waterdog's Avatar
    Waterdog is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Colorado
    Posts
    22

    Pulling info from linked CSV then clearing data

    Hey gang, I'm trying to pull data from a CSV file into Access for use in creating various reports. Essentially, I have mapped our UPS program so that it exports shipping data to a CSV file, two computers sending data to one file on the network. From there I have the CSV file linked to an Access database. The goal is to pull this info as well as pulling in related order info from our ordering system and from that generate daily reports. Currently we're just typing information into an Excel file. I'm preferring to automate this a lot more so it's less typing we're having to worry about each day.



    Anyway, that's the sum up story. The issue I'm finding now is I'd like to at the end of each day run a query of some sort to send information to a table in Access containing the info from our system as well as from the CSV. From there I'd like to clear out the data in the CSV file but leaving the file. Just to test out the link, I tried selecting a record and deleting it within Access and I was given an error message of "Deleting data in a linked table is not supported by this ISAM."

    Any ideas? Or is there another approach I should look at for getting this accomplished?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You cannot edit linked Excel or CSV files from Access.
    It's a longstanding restriction so you need to adapt your approach
    It's what the enigmatic ISAM message means.

    One workround is to import the CSV file to an Access table and after use empty that.
    You could then delete the external CSV file using the Kill function if you wish
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You tried deleting line from linked CSV? Can't edit linked CSV or Excel sheets same as can Access tables.

    Have to use CDO (Collaboration Data Object) code. With CSV use text file object Read and Write code.

    In your case, maybe just keep an empty CSV file handy and use FileCopy method to copy over the existing file.

    FileCopy "path\Empty.csv", "path\existingfilename.csv"

    But I really wonder about the necessity of saving an empty csv.

    Just saw Ridders answer, that makes sense.
    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.

  4. #4
    Waterdog's Avatar
    Waterdog is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Colorado
    Posts
    22
    Thanks to you both for your input. The reason for keeping an empty CSV file is because of UPS. Setting up the export mapping in their program, I have two options: append to an existing file or overwrite existing file. With this export I can set it to send the data at different times. One option is when we run the end of day process at the end of each day. The other option is to export the data after each shipment is processed. I'm going with the export after each shipment. That'll work best for us with what we want in pulling reports. So unfortunately, we need to keep the CSV out there. If the file doesn't exist and we leave the UPS program on the "Append to exisiting file" option then we'll just get an error because it won't create a new file it if doesn't exist. Just error out instead.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Found code that doesn't require existing blank csv to copy.

    Open "your folder path\your file name.csv" For Output As #1
    Close #1

    This creates new file or overwrites existing file.
    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.

  6. #6
    Waterdog's Avatar
    Waterdog is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Colorado
    Posts
    22
    Awesome. I'll check that out. Thanks for your input! I know I can always count on you guys to find a good solution.

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

Similar Threads

  1. Queries not pulling info
    By schulzy175 in forum Queries
    Replies: 1
    Last Post: 10-08-2017, 02:18 AM
  2. Query not pulling all of the info.
    By Snickren in forum Queries
    Replies: 2
    Last Post: 01-29-2016, 03:45 PM
  3. Replies: 3
    Last Post: 10-24-2012, 05:41 PM
  4. Replies: 5
    Last Post: 06-13-2012, 09:34 AM
  5. Pulling Record Info From Sub Form
    By redlich23 in forum Forms
    Replies: 1
    Last Post: 09-02-2009, 02:10 PM

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