Transfer Text command 1

[brainmetz]

Everytime I run this code,

DoCmd.TransferText acImportDelim, , "Activity_File",
strFileName, False

I receive the followin error:

Run-Time Error '3066':
Query must have at least one destination field.

The problem that I cannt figure out is that Activity_File is a table not a query.

Thanks,

Shane

 

[Blanch]

Shane,

I just recreated your problem. The solution is simple. Your table that you are importing to MUST have matching field name(s) to support the incoming data. If you do not have any field names in the text file that your importing from then the fields will be named F1, F2, etc.

The best practice for this is to create an Import specification by running the Import Manually the first time and name the specification something you can remember. Then use the specification name in your code and you can avoid such problems.

Another option is to attch the text file as an outside datasource and use the data within your app as you need. This data will be read only, but when you update the text file from outside the app, it will see the data without having to import.

Hope this helps!

Jim

 

[brainmetz]

Your answer makes alot of sense, just a quick question for you:

I have the names of the database exactly what I need...But for being easy lets just use F1, F2, etc...

How would I program it so that I could import. I really dont want to change the program that is exporting the data...That would way too complicated. And without changing the imported text file, how do I programmatically enter the field names in access.

My file is tab delimited if that helps...Is there anyway to say the first one is F1 and the second F2, etc?

Thanks,

Shane

 

[Blanch]

It's all a matter of preference.

If the data in the text file will always be appended to the table then you could use importing, and I would follow these steps:

1. Rename your current table from tblName to tblName_old
2. Manually Import the text file into a new table named tblName. You will create a saved Import specification that you can identify in your code.
3. Then Rename or Delete the table you just made and Rename the origial back so that the import will fill it again.

If the data is a replacement, or a refresh of data (or records to be appended) I would link the file. The only caveat is that the file be in the same directory everytime.
Do these steps:
1. Leave original Table alone.
2. perform a Link Table... menu action that will bring up the same import specification wizard. The table will be saved as the same name as the TEXT file (by default).
3. Now you can treat the text file like a table, with read-only access.
4. create any type of query you need to meet your unique project goals.

This to me is by far the best solution.

 

[brainmetz]

I did what you said:
1. Rename Activity_File to Old_Activity_File
2. Create a new table called Activity_File from using imported data. Also make a import specification called import.
3. Delete new table and rename old table.

Here is my new code:
DoCmd.TransferText acImportDelim, import, "Activity_File", strFileName, False

I still recieve the error though...

Thanks,

Shane

 

[brainmetz]

Just to let everyone know, I figured it out...

If you recieve the error that I did, you need:

DoCmd.TransferText acImportDelim, "import", "Activity_File", strFileName

instead of this:

DoCmd.TransferText acImportDelim, "import", "Activity_File", strFileName


The thing I was missing is the quotes around the import specification.

Blanch, Thanks for all of your help.