Can you trap Too many fields defined error.

Posted on 2009-02-19
Medium Priority
Last Modified: 2013-12-25
I am using DAO in VB6 to open a third party Excel file
I am getting error 3190 "Too many fields defined."
The Excel file contains data but also many unused blank columns.
Is there any way to open tis file without the error?
The current code that gives the error is below
Set WorkingTable = gdbCurrentDB.OpenRecordset(tblObj.Name, dbOpenSnapshot, dbReadOnly)

Open in new window

Question by:richardbc
  • 5
  • 2
  • 2
LVL 46

Accepted Solution

aikimark earned 1000 total points
ID: 23691962
1. DAO doesn't know how to 'handle' empty columns.
2. I doubt ADO would be any better at this.
3. From a database table perspective, empty columns don't make any sense when viewing worksheet contents as a table.  (As a manual exercise, try to solve the "What's the column name?" problem when it is empty.)

Potential workarounds:
4. You might be able to import the worksheet contents into your database, skipping the blank columns.
5. You might use Excel automation to create a new worksheet with contiguous columns.
6. You might use Excel automation to populate a local data structure with the data from the non-empty columns.
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 23691967
<<I am getting error 3190 "Too many fields defined."
The Excel file contains data but also many unused blank columns.>>
  Yes, the limit in Access/JET is 255 fields.  It's seeing the blank columns as fields.  You can verify this by opening the spreadsheet and hitting Ctrl/End.  You will get positioned at the last row/column that the spreadsheet is tracking.   To reset that, delete all the rows and columns that are blank, then save the spread sheet.  The last cell value will be updated.  You can double check by repeating the test.
  If this is a one time deal then your set.  If not, then you won't be able to open the spread sheet as a table.  Instead, you'll need to use OLE automation to fire up an instance of Excel, open the spreadsheet in that Excel instance, and then read the data row by row.
  Let me know if you need help with that.

Author Comment

ID: 23718337
Thanks for the comments
As I said the file is third party and I am trying to get them to produce the file with less columns but ...............
The file is also odd in that it does not allow you to delete columns - it is not locked but you can highlight one or more columns and then delete them - they vanish for an instant and then reappear!!
So as a workaround I have copied the data only to a new worksheet and saved that - this works but it is not a good solution as it requires outside intervention and it is not transparent to the user.
Is there any way to tell the spreadsheet that there is only say 100 columns?
Is there any way to tell VB to only open X tables?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58
ID: 23720894
<<Is there any way to tell the spreadsheet that there is only say 100 columns?>>
The procedure I out lined, which in order to work, the spreadsheet must be unproctected.
<<Is there any way to tell VB to only open X tables?>>
  Not  When you use the built-in stuff.   If you opened it in code and did the import work yourself, then yes you can look at each cell and determine if it is empty.
LVL 46

Expert Comment

ID: 24099770

Please post this strange Excel file.
LVL 46

Expert Comment

ID: 24363188
This question should be closed with no points refunded.

The answers to this question are:
* yes, you can trap errors with the ON ERROR statement
* DAO requires contiguous columns when opening ODBC sources, such as Excel worksheets.
LVL 46

Expert Comment

ID: 24367795
split points between #23691962 and #23691967

Author Comment

ID: 24392201
I cannot post the file as it is owned by someone else and none of the answers showed any way to trap the error when opening a third party file and noone answered this
I think answers should answer the question and not just ramble around
Therefore I feel no points should be awarded
LVL 46

Expert Comment

ID: 24394994

"answers should answer the question and not just ramble around"
1. we did answer your two questions (Yes, the error can be trapped.  No, you can not use the Jet engine/DAO on data sources with missing columns)

2. any rambling is just your perception and were probably attempts to find a workaround.

There is a work-around, but you have shut down that solution with your request to close this question.  In the future:
* Think about problems and solutions, rather than questions and answers.  
* The way you phrase your posted question greatly affects the number of respondents and the 'flavor' of their comments.
* If you develop a reputation for poor question quality and a propensity for pre-solution question closing/deleting requests, you will find that VERY FEW of your future questions will even get a response.


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

830 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