I have this macro in an Access spreadsheet…it performs the following tasks:
1 -imports a text file called “disco.txt” into a table called “Disco”
2-imports the same exact text file into a second table called “disco_temp”
3-exports the “disco_temp” table to a spreadsheet.
4- delete the “disco_temp” table.
Step one works beautifully.
Step two errors, with the claim that “Field F1 does not exist in destination table Disco_temp. Microsoft was unable to complete the append operation.”
Since step one and two are identical in all respects with the exception of the table name that the file is imported into, does anyone have any idea why the first step would work and the second one would fail? The table doesn’t even exist yet–of course there is no “field F1”. Neither is there in one in the table created in step one, but that seems to work.
Even more perplexing to me is that afte I close out the error message, there is in fact a table called “Disco_temp” with data in it. Anyone out there got any guesses?
F1 is the default name that a table will give the first column of data that doesn’t have a name, or whose name doesn’t adhere to Access’ naming conventions. I can’t think off the top of my head of characters that Access won’t allow, but I do know things like line-breaks, etc are not accepted. In those instances, Access will attempt to rename the field F1. It then looks for a field called F1 in disco_temp, and isn’t finding it. Chances are, it is finding the other column headings with no problem, and is copying the information into the fields as it should.
I’d identify which column from the spreadsheet isn’t showing up in your table, and figure out what it is about the column heading that Access is rejecting.
F1 is the default name that a table will give the first column of data that doesn’t have a name, or whose name doesn’t adhere to Access’ naming conventions. I can’t think off the top of my head of characters that Access won’t allow, but I do know things like line-breaks, etc are not accepted. In those instances, Access will attempt to rename the field F1. It then looks for a field called F1 in disco_temp, and isn’t finding it. Chances are, it is finding the other column headings with no problem, and is copying the information into the fields as it should.
I’d identify which column from the spreadsheet isn’t showing up in your table, and figure out what it is about the column heading that Access is rejecting.
And on re-read, I notice that you say that Disco_temp doesn’t exist before the macro is run, but you’re running an append query. You might want to switch to a “make-table” query if the table is being recreated (and later deleted) every time. In which case, the first half of this message is rendered moot.
Sure, first I double post, and now I’m going to make it worse.
If you’re just importing the text file, exporting to Excel, and deleting the table, why are you using Access at all? Unless there’s a step that I’m missing? You can run a macro in Excel to do the same exact thing.
Thanks for the help…the project is actually a lot more complicated than what I described, so yeah, I do need it in Access for reasons not worth discussing. Without getting into the details…it has more to do with making the end result easier for the person that’s going to use it (and thus more complicated for me).
Don’t feel too bad about the double post—I feel much dumber. After a good night’s sleep the problem was obvious when I looked at it with fresh eyes this morning.
On the step where it was supposed to export the file to Excel, I had it set to “import”, so it was importing the table back into itself. Not what I wanted at all and an easy fix.