Rename a text file after import to Access

[JohnLee]

Good day folks,

I have a database where I import fixed width text files regularly, once I've imported these text files I've been manually renaming them, however I was wondering if it is possible to rename a text file after I have imported it into my database.

This is the code I have for the import of one of my text files:

============
BEGIN CODE
============

DoCmd.TransferText acImportFixed, "eFlowNTKImportSpec", "tblNTK", "I:\niltokey.txt", False, ""

===========
END CODE
===========

What code do I need to write to rename this text file once I've imported it into my database.

Your assistance would be most appreciated.

John

 

[DCrake]

If you are grabbing the name dynamically you could do it in two steps

FileCopy (CurrentName, NewName)
Kill CurrentName

You could also then put an extra step in to check if the file was actually deleted

If Dir(CurrentName) = "" Then
'File Deleted Ok
Else
'File Not Deleted
End If

David

 

[gemma-the-husky]

use a variable, then its easier still, and you can begin to handle multiple files

dim importfile as string
importfile = "I:\niltokey"

DoCmd.TransferText acImportFixed, "eFlowNTKImportSpec", "tblNTK", importfile & ".txt", False, ""

'then use DC's code

name(importfile & ".txt", importfile & "_done.txt")

 

[JohnLee]

Hi both,

Thanks for your response, I couldn't quite work out how to do what you suggested, as my VB skills are far from complete, so this what I came up with:

==========
BEGIN CODE
==========

DoCmd.TransferText acImportFixed, "eFlowNTKImportSpec", "tblNTK", "I:\niltokey.txt", False, ""
DoCmd.OpenQuery "qryApptblNTKTotblNTKHis", acNormal, acEdit
DoCmd.OpenQuery "DeltblNTK", acNormal, acEdit
DoCmd.TransferText acExportFixed, "eFlowNTKImportSpec", "tblNTK", "I:\niltokey.txt", False, ""

==========
END CODE
==========

So what I've done here is to create two queries, one that appends my data to a history table and one that deletes the contents of the NTK table once the data has been imported and appended. I then export the blank contents of the tblNTK table to overwrite the existing niltokey.txt file.

I know it's not as clever as your supplied response and of course not as efficient, but hopefully it should work reasonsable ok.

Thanks once again

John