Access 2010 Maximum Crosstab Columns

I have been asked to create an Excel spreadsheet from an Access database with about 365 columns of data. The Crosstab query that I used for this purpose was previously for 60 months of data plus some static information. This worked via the command:
DoCmd.TransferSpreadsheet acExport, , "qrySpec_FundFutCashFlow_Crosstab", strExportFile

I need a technique using VBA code to create an Excel spreadsheet with about 365 months of data (in columns) from an Access database.
Who is Participating?
jerryb30Connect With a Mentor Commented:
You could build text file, comma separated, with a '.csv' extension, which would open nicely in Excel.
What is your table structure (just fields you want in spreadsheet)?
Are there entries for each month?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
that may not be feasible, queries and tables are limited to 255 columns.

perhaps using VBA codes can do that.
RCUllrichAuthor Commented:
The data only includes a monthly value if there is one. Some assets have a single cash flow record at maturity so I did NOT write 359 "zero" records when only 1 record was needed (with a date 30 years from today).

My issue is that I can't use the crosstab query, therefore I will need to code this in VBA and write out the delimited text file.

RCUllrichAuthor Commented:
Does anyone know if Access 2013 will allow more than 255 columns in a query (or table)?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.