Access and a large SQL Table

[lisaharris]

I have a SQL database with a table that has over 450 fields. Access 2007 still has that nasty 255 fields per table limit, so when linking the SQL table to Access, the table is cut off after the 255th field.

Is there any workaround to this?

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[MajP]

Two seperate tables linked by the primary key.
But I have to believe that at 450 fields, this is non normalized data. I just can not imagine manys item in the world that can be uniquely identified by 450 attributes.

What type of data is it? What do you want to do with Access because there may be other limitations? Can you normalize the data?

 

[lisaharris]

Unfortunately, I have no control over the SQL table's design or normalization (or lack thereof). The table is part of our CRM system and can't be changed. I guess I could create a couple views in SQL and query those with Access. A royal pain, but I think that's my only workaround.

__________
Veni, Vidi, Visa: I came, I saw, I charged it.

 

[jsteph]

Is it possible this "table" from the CRM system is actually a view? Do you have enough visibility into the backend to verify this? It's possible you could read the view design and get at the base tables and make your own local 'view'.

I know it may not be doable if you're completely locked out of looking at schema info, but it's worth a look.
--Jim

 

[Golom]

Sadly, the 255 limit also applies to views (i.e. queries in Access lingo.) Thus, you may be able to create two linked tables to get all 450 fields into access but you will not be able to see more than 255 of them at a time.

 

[dhookom]

I'm not sure why more than a couple dozen fields would be significant at any given time.

I would probably create a number of pass-through queries that reference the significant columns that are required.

Duane
Hook'D on Access
MS Access MVP