?
Solved

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

Posted on 2015-02-06
19
Medium Priority
?
287 Views
Last Modified: 2016-02-11
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?
0
Comment
Question by:Jay Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +1
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40593838
upload a copy of the excel file.
0
 

Author Comment

by:Jay Williams
ID: 40593887
Here you go.
IPP-Request-Form.xlsm
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40593919
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Jay Williams
ID: 40593949
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40594035
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
 
LVL 85
ID: 40594173
Also be sure your Office and Windows installations are fully up to date.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40594248
@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
 

Author Comment

by:Jay Williams
ID: 40594250
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 668 total points
ID: 40594305
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
 

Author Comment

by:Jay Williams
ID: 40594324
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
 

Author Comment

by:Jay Williams
ID: 40598180
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
 
LVL 85
ID: 40598252
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
 

Author Comment

by:Jay Williams
ID: 40598267
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
 
LVL 85
ID: 40598286
There are no combined pathnames. There are two distinct pathnames, separated by a space and surrounded by double quotes, with the /decompile switch.
0
 

Author Comment

by:Jay Williams
ID: 40598290
thanks
0
 

Author Comment

by:Jay Williams
ID: 40598325
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 664 total points
ID: 40598463
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40599025
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
 

Author Comment

by:Jay Williams
ID: 40600486
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question