Access - large number of fields to export into excel
I have an excel template given to me that requires 340 fields to be filled. I have just realized in the many years of working with access that there is a field limit to an access query. So I cannot do a single query. I then broke it into 2 queries and when I tried to call both the same thing happened it wont combine the results saying to many fields defined.
I am at a loss as to how to grab these fields and export them together. Can it be done doing it in sql code ??? And please before I have someone tell me again about bad construction these are not my planning's. I work between a Healthcare org and Gov't, neither hears no, and usually idiots build this stuff, so I am forced to do what they want if possible. LOL so if there is a way to export from access 340 fields into excel I would be greatly appreciative for the help if possible.
I also thought of doing 2 exports but my fear is that after the first export the second could be missing a rec and the rest would be mismatched and no one could tell.
I am at a loss as to how to grab these fields and export them together. Can it be done doing it in sql code ??? And please before I have someone tell me again about bad construction these are not my planning's. I work between a Healthcare org and Gov't, neither hears no, and usually idiots build this stuff, so I am forced to do what they want if possible. LOL so if there is a way to export from access 340 fields into excel I would be greatly appreciative for the help if possible.
I also thought of doing 2 exports but my fear is that after the first export the second could be missing a rec and the rest would be mismatched and no one could tell.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim's comment #2 is the way to go.
As long as your two queries return the records in the same sequence, you could use automation and the copyfromrecordset method top "poke" the results into the right cells.
You will have to write code to create the column headers from the query column names as well. With the copyfromrecordset method, you can specify the starting cell where you want the recordset to go, so for the first recordset, you might indicate cell "A2", and for the 2nd recordset (the second set of 170 columns), you would indicate cell "FO2"
As long as your two queries return the records in the same sequence, you could use automation and the copyfromrecordset method top "poke" the results into the right cells.
You will have to write code to create the column headers from the query column names as well. With the copyfromrecordset method, you can specify the starting cell where you want the recordset to go, so for the first recordset, you might indicate cell "A2", and for the 2nd recordset (the second set of 170 columns), you would indicate cell "FO2"
As long as the recordset (query results) rows will align, the Dale Fye solution is the best way to populate Excel (2007+) from Access. You can put the code into either Access or Excel - push versus pull implementation.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am in mexico on vacation will respond when i return
Enjoy the vacation!
Jim.
Jim.
ASKER
i understand Jims comments nand also will try pats solution
No real work a round that I'm aware of except to:
1. Write the data directly to a file (as a CSV).
2. Open Excel as an automation object and poke the data into the cells directly (Excel must be installed for this).
255 is the field limit in any query output no matter how you approach it and it is not modifiable.
Jim.