Too many fields defined... ?

[soonerland]

Hi everybody!

I need to run a really long query in Access2000 but getting a message 'Too many fields defined'.

Is there any remedy to cure this, except cutting number of requested fields?

By the way, what is the largest number of fields it can handle?


Thanks,

Alexandre

 

[Francescina]

The (only) workaround is creating subqueries and using the "*" instead of the subqueries field names!

 

[smaxted]

Francescina,

Thanks for responding. Yet no success. I am new to this stuff, not everything is clear. What do you mean 'subqueries'? Are they other queries extracting smaller numbers of fields or is a subquery a special entity in Access?

If it's smaller queries it doesn't work. In my case I make several queries then try to combine them consequently joining (using '*' instead of enumerating all fields). The very last fails to join. This last has 17 fields, all the previous joined together have 117. This makes 134 in total. This is less than 255 allowed in Access but the problem is I use a SQL construction where I have to place the fields twice:

SELECT *
FROM tbl_tmp_Extr RIGHT JOIN tbl_tmp4 ON tbl_tmp_Extr.SMDTSTR=tbl_tmp4.SMDTSTR
UNION SELECT *
FROM tbl_tmp_Extr LEFT JOIN tbl_tmp4 ON tbl_tmp_Extr.SMDTSTR=tbl_tmp4.SMDTSTR;


Suppesedly, this where the number exceeds the limit.




Still need a good advice!


Alexandre Scotty Mac a.k.a smaxted

"Another day, another problem"

 

[jjonesal]

Do you need all fields from tbl_tmp_Extr and all fields from tbl_tmp4? I know they have at least one field in common that could be eliminated (SMDTSTR)

J. Jones
jjones@cybrtyme.com

 

[SashaBuilder3]

jjonesal,

You are right, but it makes just 1 less: 133. Still too much...

Thanks anyway!



More solutions please!!!!!


Alexandre

 

[jjonesal]

I think your limit is due to Jet. I think you really only need to get the number of output columns down to 127 in order for this to work.

Options:

1. Are there any fields in your output that you could concatenate? Depending on what you are using the query output for, this might be an option: (i.e. Concat firstname and lastname to produce fullname) - kinda klunky - but could work.

2. Output fewer columns (probably not an option, but look closely)

3. Output 127 columns and then use code to
a. add additional columns to the structure of the table (using Columns.Add)
b. then iterate the original data updating each record

4. Put a portion of the columns into one table (with one query) and the remainder of the columns into another table (with another query). Then, join these two tables when you need data from both tables. (Not sure about this one...)

I know none of these sound very appealing, but I don't think I can come up with anything else...






J. Jones
jjones@cybrtyme.com

 

[at51178]

jjonesal,


Seems this system limitation is hard to get around. My ultimate goal is producing a CSV spreadsheet. Maybe the best thing is to output two-three tables then write a standalone procedure for match merging them into one. Anyway I'll need it on a regular basis.


Well, now I know Access a little better...

Thank you, guys, for not leaving me alone!


Alexandre

 

[at51178]

There is a way around that I have the same problem with some of my databases

what you would want to do is just set the query that you are using so that you are able to open it with out any error messages.

Once you have done that you will have to create action query that will make a table
once the query becomes an action query you click on the query and it will create all the fields that you have in your query in a table once the table is created then you create another query that reads off the table and then add the extra columns that you wish to see.

hope that helps.

 

[elisharae]

You see, at51178, I cannot just mechanically add extra columns to the table. I need the tables merged on matching a certain field (date in my case). As soon as I get to this I have to use the UNION SQL construct seen above. And here it is - the magic circle closes... Unless there is something I couldn't catch in your idea...


Anyway, thanks for your response.

Alexandre