255 Column Limit using Microsoft Access

[Victoria Holowchak]

I was hoping that Microsoft Access 2002 would allow users to see all
the columns of an Oracle database table that contained more than 255
columns. I noticed that my Oracle8 ODBC driver is only at 8.1.6.0.0
for win95, win98 and winNT. If I upgraded my ODBC driver would Access
be able to recognize the columns beyond the 255th column? or is this
an Access limitation?

Please don't lecture me about good table design and recommend
splitting the table up into smaller table(s) because that is not an
option for me at this point.

If 255 columns is an Access limitation and there is no way around it,
I'm thinking about creating a database view to virtually split the
table so that the new columns beyond the 255th column appear to be in
a different table and perhaps Access would recognize these columns.
Has anybody tried this?

[Terry Kreft]

Re: 255 Column Limit using Microsoft Access

Have a look at using Pass Through queries to get around the 255 columns
limit.

Terry

"Victoria Holowchak" <VICTORIA_HOLOW CHAK@DOFASCO.CA > wrote in message
news:4e0aa977.0 312180739.15ea7 ec8@posting.goo gle.com...[color=blue]
> I was hoping that Microsoft Access 2002 would allow users to see all
> the columns of an Oracle database table that contained more than 255
> columns. I noticed that my Oracle8 ODBC driver is only at 8.1.6.0.0
> for win95, win98 and winNT. If I upgraded my ODBC driver would Access
> be able to recognize the columns beyond the 255th column? or is this
> an Access limitation?
>
> Please don't lecture me about good table design and recommend
> splitting the table up into smaller table(s) because that is not an
> option for me at this point.
>
> If 255 columns is an Access limitation and there is no way around it,
> I'm thinking about creating a database view to virtually split the
> table so that the new columns beyond the 255th column appear to be in
> a different table and perhaps Access would recognize these columns.
> Has anybody tried this?[/color]

[Rich P]

Re: 255 Column Limit using Microsoft Access

I have had a similar situation. What I did was this. First, you don't
have to really have a table with 300 columns where you scroll from left
to right. So I used ADO to connect to this Oracle db and retrieved the
field count and names. I added these to a table which I could view from
a listbox. From the listbox I could pick the fields I needed to look at
and retrieved data from those fields to populate a table (which I create
on the fly using DAO). Here is the connection string for ADO to Oracle:

Dim conn As New ADODB.Connectio n, i As Long, j As Long
Dim Rst As New ADODB.Recordset , RS As Recordset

conn.Provider = "OraOLEDB.Oracl e"

conn.Connection String = "Data Source=ddd" & _
";User ID=lharris" & _
";Password=pass word"

conn.Open
Rst.CursorLocat ion = adUseClient

Rst.Open "SELECT COUNT(TRANSFER_ DT) FROM CCC2.TRANSFER WHERE TRANSFER_DT[color=blue]
>= TO_DATE('01-01-02', 'DD-MM-YY')", conn[/color]

Note: you have to already have the Oracle ODBC driver loaded for this to
work. The data source will be whatever you named your dsn. I have
tried the ado connection without loading the Oracle ODBC driver, and got
an error. The other catch is that your sql statements have to be pl sql
(oracle sql) as above - Oracle requires casting stuff alot like TO_DATE
for date stuff).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[TC]

Re: 255 Column Limit using Microsoft Access

In addition to the other suggestions, maybe you could get your Oracle DBA to
create some views on that table. The first view would expose fields 1-200
(or whatever), the next would expose fields 201-400 (or whatever), & so on.
Then, each view is within the Access limit of 255 fields.

Then, we he/she had done that, you could beat him/her around the head about
having a table with ... (you know what's coming here!)

HTH,
TC


"Victoria Holowchak" <VICTORIA_HOLOW CHAK@DOFASCO.CA > wrote in message
news:4e0aa977.0 312180739.15ea7 ec8@posting.goo gle.com...[color=blue]
> I was hoping that Microsoft Access 2002 would allow users to see all
> the columns of an Oracle database table that contained more than 255
> columns. I noticed that my Oracle8 ODBC driver is only at 8.1.6.0.0
> for win95, win98 and winNT. If I upgraded my ODBC driver would Access
> be able to recognize the columns beyond the 255th column? or is this
> an Access limitation?
>
> Please don't lecture me about good table design and recommend
> splitting the table up into smaller table(s) because that is not an
> option for me at this point.
>
> If 255 columns is an Access limitation and there is no way around it,
> I'm thinking about creating a database view to virtually split the
> table so that the new columns beyond the 255th column appear to be in
> a different table and perhaps Access would recognize these columns.
> Has anybody tried this?[/color]