Results 1 to 7 of 7
  1. #1
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22

    Code/function needed delete an external text file


    My Access application uses a button to execute a "Saved Import" that updates a table. After the text file has been imported, I need to delete it from its location on the C drive. (C:\programdirectory\transactionlog.txt) I'd prefer to use a Macro to accomplish this, but there doesn't appear to be a proper function available. I'm not familiar with VB so if code is needed to do this, can someone help?

    (I'm migrating the application from different programming software and could previously "evaluate" an IODEL function and provide the path to the file. It was very simple and effective but Access doesn't appear to utilize this function. )

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I use Kill. VBA example:

    strFile = "C:\programdirectory\transactionlog.txt"
    If Dir(strFile) <> "" Then Kill strFile
    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
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    I tried this. The first error returned was "Invalid Outside Procedure".

    I changed it to (with proper directory and filename):

    Private Sub Clearlog()

    strFile = "C:\programdirectory\transactionlog.txt"
    If Dir(strFile) <> "" Then Kill strFile

    End Sub

    The code is saved with the name "DeleteLog." In the Access Object Browser I can see DeleteLog with ClearLog as a member of it's class.

    On my form I have a button... when clicked the embedded macro is instructed to Run Code for the Function Name: DeleteLog(). However, NOW I get the following error:

    "The expression you entered has a function name that Microsoft Access can't find."

    I've tried using both the name of code DeleteLog() and ClearLog(). What "Function Name" is Access looking for?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't use macros.

    What code is saved as "DeleteLog" - you show only Clearlog sub.

    Why is ClearLog a member of DeleteLog - what is DeleteLog?
    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
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    The "macro" I'm referring to are the events that occur when a button on my form is pressed. These events are referred to as an Embedded Macro. I didn't write a Macro using your Kill phrase.

    In the Embedded Macro Access provides a list of actions that should occur, one being "Run Code". When you choose "Run Code" you have to provide the Function Name().

    Prior to choosing the Run Code event, I created an object in VB Code with the Kill instructions and saved it as "DeleteLog."

    Following the first error about an Invalid Outside Procedure, I wrapped your Kill phrase in a Sub routine. The Sub routine in the DeleteLog code object is named ClearLog.

    I can see DeleteLog() in the Objects list for my application. I've now read somewhere that the "Function Named" assigned to a button on a form must reside as an object belonging to the form. I don't have a clue what this means.

    Such a simple task... why so complicated!? :/

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You mean DeleteLog is a general code module? Macros don't call modules, they call functions.

    ClearLog is a procedure in that module. ClearLog must be a Function, not a Sub, to be called by a macro and it must be Public.

    Function ClearLog()

    or

    Public Function ClearLog()
    ...
    End Function


    Now that public function can be called from anywhere in the database.
    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
    Chris033115 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    22
    Perfect! And so simple. Thank you.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-13-2014, 10:44 AM
  2. Replies: 1
    Last Post: 05-29-2013, 03:08 PM
  3. VBA code to delete the 2nd line in a csv file
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 03-02-2013, 02:16 AM
  4. Delete in external database
    By theosgood in forum Programming
    Replies: 3
    Last Post: 10-06-2012, 01:27 PM
  5. Replies: 14
    Last Post: 04-10-2012, 06:37 AM

Tags for this Thread

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