Access run time error 2391 no field name 'yes' in your destination table.

This is an automated import that has been working perfectly for a long time--now it isn't.
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"

Open in new window


Row 5 (top of the import range) contains the exact field names of the "ProcessSheets" table (so this stuff won't happen).  There is no "yes" value in row 5.  I searched the spreadsheet template and the only "yes" is in the header above the import range.  I can't figure out what's changed.  Any ideas?
Jay WilliamsOwnerAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
he file opened right up, but didn't appear to do anything more than that.  Is that what to expect?
Yes, that's what Decompile does.
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the excel file.
0
 
Jay WilliamsOwnerAuthor Commented:
Here you go.
IPP-Request-Form.xlsm
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jeffrey CoachmanMIS LiasonCommented:
FWIW your code and spreadsheet work fine for me without error...

Perhaps now (perhaps because of an update of change in the Excel Frmat or Extension) Hidden rows (as your Row 5 is) are now ignored in the data range.
So now Row "5" will be the 5th "visible" row, this would make the new row 5, Row 6.

Try the code like this;
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, False, "IPP_Request!A5:AO5000"
...Or change the range to:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A6:AO5000"
0
 
Jay WilliamsOwnerAuthor Commented:
Jeffrey, I had a similar thought and had already tried starting the range at A6 and got the same error, with a different value not found as a field name.  I changed the HasFieldNames argument to "False," and got "no field named F1."
0
 
Rey Obrero (Capricorn1)Commented:
do a decompile of your access app
then do a compact and repair

open db and goto to the VBA editor
do a Debug > Compile
correct any errors raised

try again your import
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also be sure your Office and Windows installations are fully up to date.
0
 
Jeffrey CoachmanMIS LiasonCommented:
@Rey and Scott
Were either of you able to replicate the issue?
I had no errors when I ran the exact code with the exact spreadsheet...
0
 
Jay WilliamsOwnerAuthor Commented:
Thanks Rey and Scott, I'll get after it, but don't hold your breath.  I tried to decompile, but this IT policy won't let me pass gas without permission.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Jay,
A5:AO5000"

Some observations...
1. The end of your data range seems to be Row 944
...but the resulting imported table has 1622 rows
2. When I do a Group By/Count on just the DrwngNddInBP3rnc field, I get 984 records on "No"

Perhaps it is reading the blank values as "Yes"...?,
...so if NO=0, ...then any other value will be interpreted as "Yes"?

Keep us posted.
0
 
Jay WilliamsOwnerAuthor Commented:
AHA!  That's what changed.  Management wanted us to set that column to a default "No."  That's right when the problems started.  I will keep you posted--on Monday!  Have a great weekend, and THANKS! :-)
0
 
Jay WilliamsOwnerAuthor Commented:
Well, I played all kinds of games with the import range and HasFieldNames argument.  Same deal. I get either an error saying the there is no field named "yes" or "F1."  Asking my help desk to restore earlier versions of the template and database that worked; asking for permission and assistance to decompile Access.  I have to pull out all the stops on this one.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
asking for permission and assistance to decompile Access.
Can you create a desktop shortcut on your workstation? If so, you don't need permission or assistance, I would think. Just create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that shortcut, then open your app, go to the VBA Editor and click Debug - Compile. Fix any errors and continue doing that until the menu item is disabled. After doing all that, Compact the database.
0
 
Jay WilliamsOwnerAuthor Commented:
I thought I did that, Scott, but I didn't have both paths on the same line on the shortcut.  Is there any separator or delimiter in the combined pathname(s)?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There are no combined pathnames. There are two distinct pathnames, separated by a space and surrounded by double quotes, with the /decompile switch.
0
 
Jay WilliamsOwnerAuthor Commented:
thanks
0
 
Jay WilliamsOwnerAuthor Commented:
I ran "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "G:\XE_ECMs\IPP Sharing Development\Processing.accdb"/decompile.  The file opened right up, but didn't appear to do anything more than that.  Is that what to expect?  I went to the module, compiled and then compacted.  Ran the process.  Same result.
0
 
Rey Obrero (Capricorn1)Commented:
try this first, copy and paste

    DoCmd.TransferSpreadsheet acImport, 10, "ProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"
                         
if you are still getting the error,
try importing the excel file to a NON existing table

       
    DoCmd.TransferSpreadsheet acImport, 10, "NEWProcessSheets", sPath & xlFile, True, "IPP_Request!A5:AO5000"
0
 
Jay WilliamsOwnerAuthor Commented:
I see where you're going, Rey.  The "NEWProcessSheets" import was successful--and showed my formatting conflicts.  I also need to reconcile my stated import range with the actual range on the sheet and play with the default values in the DrwngNddInBP3rnc column as Jeffrey suggested.  I think we're onto something.  Will update later today after testing.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.