[Nick Horowitz]

MSAccess has a limit of 255 columns on any queries that are run locally with the MSJet engine, and the 255 limit also impacts the list of fields that MSAccess pulls from the database and presents to you in the drop-down menu of field names (i.e., Access only shows you the first 255 fields).

In the old Oracle9i driver, I could code a “pass-thru” SQL query that would send the SQL string directly to the Oracle server and run it at the server (instead of running it locally in MSJet engine). I could include any fieldname in this pass-thru query string and Oracle would return the data to me, thereby working-around the 255 field limit in Access (plus the server-side queries are much faster since Oracle does all the lifting server-side and just returns the results via XML stream to the client)

However, with the new RDA ODBC driver, the 255 field limit is impacting me. I tried a pass-thru SQL query, but RDA handles the pass-thru queries differently and any field I specify in the SQL string which is not listed in the MSAccess drop-down is giving error of “could not find any column information for table:Administrator_CUSTOMERS”. I know I spelled the column name correctly because I looked in the OA_COLUMNS system table to make sure I got the spelling correct with the underscore replacement of special chars.

Has anyone run into this quirk and do you have any creative solutions?
This is a cached copy. Click here to see the original post.

[longlam]

RE: MSAccess 255 Column Limit

Hello Nick,

I remember us having a convo with Evan about the limitations of the driver. I was wondering if you have the opportunity to use the old method why are you not currently using it? Did they turn it off on your account? The reason I’m asking is that I’m going to try to beg, borrow or steal my way into using the old driver. It’s silly that they will make us use a driver that doesn’t perform at the same level of the current one while phasing it out. I don’t think that the current driver will get to the point where the current oracle driver is for at least a year. If they will continue to allow users to use the old driver up to that point I think it would make a lot of us much happier especially since we are paying for this access.

[Nick Horowitz]

I have been told that the old driver will be shut-off at anytime this year. So I am on borrowed time already!

The old Oracle 9i driver is much more expensive to implement because they have to move your account onto a dedicated server with no more than 4 other customers. So there was a big setup fee because so much work was involved in moving you to a dedicated server. You also have to send them an encryption key which their Professional Services Dept. has to setup on their side so there is consulting time involved in setting it up there too (it uses a shareware PuTTy encryption client). The PuTTy client has to remain open the entire time that you are using the Oracle driver, which is not an very industrial strength solution, so that is a draw back depending how you want to utilize the driver. PuTTy is fine for desktop ad hoc queries, but I wouldn’t try to use it in any type of “production” websever environment.

[longlam]

RE: MSAccess 255 Column Limit

That’s something that I didn’t know before. Seems like the new ODBC is what we have to deal with then. The difficult thing for me right now is that we’re beginning some projects that involve using the odbc via a jdbc-odbc bridge in Java. This is much faster in terms of getting data if you know what you need and you know where to look, in comparison to web services but I get a lot of errors with this new driver. Any ideas if a specific jdbc driver will be out soon? I’m guessing it wouldn’t be too difficult to create if they already have the toolkit handy. Good luck with figuring out these limitations!

[ivan.parra]

RE: MSAccess 255 Column Limit

What errors are you recieving from the JDBC ODBC Bridge? We are aware that the strings exposed via the JDBC/ODBC Bridge is not correctly exposed if you use the “getObject” method because the bridge incorrectly handles the unicode data type. However, using “getString” works fine.

Other than that, I have not seen any other errors. What else are you experiencing?

[Author]

Posts