Getting around Access 255 Column Limit

[Excellor_From_London]

Hello there,

I have a cross tab query that returns more than 256 columns of data from a table but because Access has a limit in terms of the number of columns it can display, I am not getting any results back.

To by pass this, does anyone know if it is possible to write the output from a cross tab query directly to a text file? Then I could just import the file into another application (or the new excel that accommodates 16K columns) to work on.

Or is it possible to connect another querying tool (e.g. aqua data or some other sql tool) directly to access/the table and run the query from there?

Thanks

Fellow Excellor

 

[Excellor_From_London]

Hi,

Sorry Giacomo, my fault entirely, I rechecked the field names and the row field had an extra chracter in the code, must have acidently changed this by mistake somewhere without realising!

It's working as before now. I ran the new code you supplied but I am still getting a similar output as before. For instances where dates start with 12 or less, there are null values. But, I examined the output in a bit more detail and noticed that there some instances where the date is less than 13 but there are values populated:

04/04/2003 68.14 111.5
12/12/2003 80.8 239.5
01/07/2005 84 376.75
03/12/2004 93 373.75
07/07/2006 130.3 372.5
05/05/1995 271.12 475

So I am not sure entirely going on.

Excellor

 

[giacomo]

what's going on is that the date is being converted to US format (mm/dd/yyyy) so if the date is Jan 7, it's trying to pull data for July 1 sometimes there happens to be data for that date and it works, sometimes not.

So what we need to do is convert the value to get it to recognize the date. Instead of changing the code more I'm wondering if you can change your query. On your date field use the cdbl() function to turn it into a number. Your output will be effected but when you read it back into Excel you should be able to change the format easily enough.

Wish I thought of this sooner

Giacomo

 

[Excellor_From_London]

Hi Giacomo,

I realise it's been a while since your response.

Anyway in terms of fixing the dates issue, what you suggested in your last message works fine! But I'm running into problems with writing the file. When I work with a very small tables - say four columns, 200 rows, the data write fine.

But when I'm using tables of say 250 variables of 200 rows for example, Access hangs/crashes. In this situation a very small amount of the data does appear to be written in the CSV output file when I check it but then it just stops writing.

Could this be a memory issue or something else? I was wondering about the size of data tables you have used in the past with that code, have they been big or very small? Have you had any similar problems with the code in the past?

Thanks,

Excellor

 

[giacomo]

Excellor,

To be honest I just wrote that because I thought it was an interesting request... I've never really tested it in the real world, just on some sample data I made.

if you want you can email me some sample data that causes it to crash and I can have a look this weekend.

Also, I think you're using a query still right? what if you make a temp table to run the code against? does it make any difference? Also if you're reading/writing across a network that could be a factor too. Not sure if this is the case or not.

hth,
Giacomo

 

[JMStrong]

Hello all,

I've a crosstab query in Access 2007 which I've been asked to pull a daily usage of items. Unfortunately, daily usage makes the query hit the column limit. I've tried using what's been done here as a model but could not get anything to work. Below if the SQL View of the crosstab query. Any suggestions on how to modify this SQL to output directly to a CSV file so I can get access to the information?

Thanks in advance for any suggestions.

TRANSFORM IIf(Sum([Usage]) Is Null,0,Sum([Usage])) AS Expr1
SELECT [Working Table].[Vendor Name], [Working Table].Description, [Working Table].[Vendor Nbr], [Working Table].[Unit Price]
FROM [Working Table]
GROUP BY [Working Table].[Vendor Name], [Working Table].Description, [Working Table].[Vendor Nbr], [Working Table].[Unit Price]
ORDER BY [Working Table].[Vendor Name], [Working Table].Description, [Working Table].[Vendor Nbr], [Working Table].[Unit Price]
PIVOT [Working Table].[Report Date];