Importing file and replacing existing file contents

[jamphan]

I have a macro that will import a text file call CAFile from a network location into my database and will also delete the existing file with the same name because it is an ever changing file. I am running into a problem because I need to be able to delete the existing file but it is used in several queries so it is not allowing me to delete it. Is there a way I can clear the contents of the old CAFil in my database and replace it with the new imported CAFile information or is there a better way to do this. Thanks for the help.

 

[The_Doc_Man]

I am running into a problem because I need to be able to delete the existing file but it is used in several queries so it is not allowing me to delete it.

Is this file linked as though it were a table? That would do it. (File locking is in the way because Access will try to open all externally linked tables when the main DB opens.)

Is the file set up without DELETE permissions? That would do it. (Permissions are your problem.)

Is the FOLDER set up without WRITE permssions? That would do it. (Permissions are eating your lunch.)

Is the file actually OPEN at the time because one of these other queries is doing something with it, too? That would do it. (File locking is in the way.)

Is the FILE SHARE on the remote network member set up without DELETE permissions? That would do it. (Network permissions are in the way.)

For that matter, I'm not sure about what happens when you do an import through a macro, at least with respect to file closure. If you convert the macro to VBA code, you can at least force closure on the file.

 

[jamphan]

The file is linked like it is a table. I have no problem deleting it manually, it is just when the macro runs it will not allow deletion. What would the VBA code look like if I wanted to import and replace the contents of the existing file?

 

[fernin8r]

Importing

What you need to do is :

DoCmd.TransferText

You can either transfer text directly into your table or set up a temporary table so that you can set up a series of checks before copying over.

Delete the file

Don't link the text file to a table. This is probably why you can delete it.

 

[jamphan]

When I transfer the text to the table it will add to what is already there right? I want it to overwrite the existing data.

As far as the text being linked to the table, it is not. The imported text file becomes the table that is my queries are based on.

 

[fernin8r]

If you want to overwrite the table just delete the information in the table first:

CurrentDb.Execute "DELETE FROM Table1"


Then transfer the data.


Also, what did you mean by:

The file is linked like it is a table.

 

[jamphan]

When I add the DoCmd.TransferText what is the syntax going to be because I am importing a text file from a shared network drive and need to reformat the field from a number to text format. In my macro I was able to include specifications that I had saved when I imported the file the first time to the database.

 

[fernin8r]

Text File requirements

Your text file needs to be set up a certain way.

1.) Your Fields have to be comma delimited
2.) Your strings must be enclosed in ,"quotes",
3.) Your numeric values should just be left alone ,1234,
4.) I would include the Field names in the first line of the text file.
5.) Watch out for Memo fields. If the data for a Memo type field in your text file contains carriage returns, you might run into issues when you are loading the file.
6.) Also watch out for strings that contain quotation marks. It will confuse the string (example ,"I have "quotes" in my string",)

A Sample Text file is attached

Now... having properly created the text file, it's time to TransferText

SYNTAX:

DoCmd.TransferText acImportDelim, , "Table1", "S:\temp.txt", True

Change Table1 to the table name you would like to transfer the file information to. Change "S:\temp.txt" to the file location. The True at the end states that you have field names in the first row of your textfile.