Query table with more than 255 colunms (1 Viewer)

[jobrien4]

My company's ERP system's database in an Informix database. I am able to connect to their tables through and ODBC connection in MS Access and write queries, reports, etc. It is not possible to change the structure of the tables though.

There is one table that has 310 columns in it that I need to access. I only need about 220 of those columns. If I import the table into MS Access, it only allows me to see the first 255 columns. If I manually write out the SQL to query the remaining 60 columns, it cannot locate those columns. From what I've seen, there is no way to view those other columns in MS Access.

I did get an install of MS SQL Server Management Studio. Is there a way to view the Informix database through an ODBC connection in MS SSMS ? Or is there another way to view that table and see all columns ? I just need to query the table, chop it down to maybe 220 columns, and be able to import the data into Excel as a live connection so I can start manipulating it in Excel.

Does anyone have any suggestions?

 

[Uncle Gizmo]

I note that most products have the basic ability to export their information in text format, usually CSV (comma separated value) If you can export the table in CSV then you can use it in anything...

 

[sneuberg]

Does Excel also have this 255 column limitation. Have you tried to get the data into Excel directly.

 

[The_Doc_Man]

If you actually had Informix available, you might be able to write ANOTHER database that contained only queries to the ERP database - the moral equivalent of linked table manager - and draw data from the queries for which the total would be the 220 fields you wanted. Watch out for the Access 4kb limit, though. If you have enough fields to blow the 255 field limit, you might have enough text data to blow the 4k buffer size limit.

 

[vignesgangboard]

When Access goes to create an ODBC linked table it queries the remote database to get the column information. The structures for holding table information in Access are limited to 255 columns, so only the first 255 columns of the remote table are available. For example, for the SQL Server table
CREATE TABLE manyColumns (
id int identity(1,1) primary key,
intCol002 int,
intCol003 int,
intCol004 int,
...
intCol255 int,
intCol256 int,
intCol257 int)

goo.gl/rnfwG2

 

[Uncle Gizmo]

When Access goes to create an ODBC linked table it

Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.

 

[Pat Hartman]

Even though you can't change the Informix schema, you should be able to add views. Create one or more views of the long table that include ONLY the columns you need. You can link to the view rather than the table and Access will be none the wiser.