"Too many fields defined" Error

[JString]

Here's the general situation:

I have a table that I want to periodically export to an excel spreadsheet.
I don't want all of the table data exported, mind you, but only data that
corresponds to the form that's currently being viewed. The
transferspreadsheet method requires a table or query name, so I wrote some
code that creates a temporary query based on the global filter and the fields
that are contained in the active subform. No problems as of yet...

Now, here's where it gets complicated. There are two subforms that need to
export to excel. Subform A has about 24 fields I think and subform B has
about half of that (around 10 or less I think). What happens is this: I'll
run the export code to generate an SQL statement based on subform A without
any problems. Then I'll run the code for subform B and again, everything
works. The problem arises when I try to run the code for subform A after
running it for subform B, and the SQL error message that I get is "too many
fields defined."

Does anybody know what's going on here?

 

[JString]

The funny thing here is that the error is not coming up for the
createquerydef statement but for the transferspreadsheet statement. Also, I
opened up that temporary query after the code break and it only has about 20
fields defined, which is right on target and nowhere near the 255 field
limit.

 

[Pete D.]

Couple of things can hit this limit. If you create fields and delete they
will ghost in the table and cause this error. Compact and repair to fix
this problem. Also very complex query can cause this as in the sort process
access will make many extra fields in temp sort tables or in an update query
.. Simplify the query or break it into multi queries to reduce the single
query. This is a Microsoft Jet database engine internal limit of 255.

http://support.microsoft.com/kb/199076

 

[JString]

Do you think that ghosting could be an issue even if the temporary query is
deleted after use and recreated when the code is run again?

 

[Pete D.]

Actually, in your case I think it is a complex query but as I didn't see
those I'm just guessing. Try breaking the one that crashes into a couple of
queries possibally writing it to a temp table and then do the next one off
that table.

 

[a a r o n . k e m p f]

I never have any problems with 'too many fields'.

Of course, back in 2000-- I was faced with legitimate normalized
databases that were several hundred columns wide.
So back in 2000; I took the leap up to SQL Server.

It was by far, the best decision I've ever made in my life.

-Aaron

 

[JString]

If only our IT guy felt the same. For some reason he wants me to stick with
the standard Jet engine.

Anyhow, I fixed the problem by adding some code that deletes any
pre-existing spreadsheets before re-exporting them. Looks like the problem
was on excel's end.