Linked Table 255 field Limit workaround? (1 Viewer)

gmatriix

Registered User.
Local time
Today, 03:44
Joined
Mar 19, 2007
Messages
365
Hello All,

I am linked to a database that has some tables with fields that are in excess of 255 fields.

When I look at them in Crystal, it displays all the fields.

Is there a workaround that would allow me to see all the fields? I tried to play around with passthrough but didn't work.

Any ideas?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
What is the backend database? Is it SQL Server? If so, you might consider using Views to link to the table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:44
Joined
Feb 19, 2013
Messages
16,553
there is no easy workaround. The limit is 255, end of.

Providing there is a PK you can use then you could have two queries. The issue comes that you would have to view them side by side and have some means of syncing them when scrolling.

However I suspect you are asking a too simple a question. You mention Crystal which is a reporting app. If you are looking to use the data in a report then you could use sub reports.

Or perhaps you just need as list of the fields, not their contents?

In other words, explain why you need to 'see all the fields', how you need to see them and what the ultimate objective is, there may be a better way.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:44
Joined
Feb 19, 2002
Messages
42,981
To work with server-side tables of more than 255 columns, you would use views as theDBguy suggested. Obviously, the table is not normalized so no matter what you do, you will have a problem. Break the columns into logical sets if you can. Once you are using the data in Access, you cannot rejoin the views to get the entire table because queries have the same restriction as tables.

I have Access apps that interface with Timberline which is an ERP application and some of its tables are not normalized. I create views that select only the data I want and just ignore the rest.
 

gmatriix

Registered User.
Local time
Today, 03:44
Joined
Mar 19, 2007
Messages
365
Thanks for the response guys!

I have considered views but this is a read only database (SQL, read only access) and I don't have access or privileges to create a view...(corporation database)
I am just using a datadirect ODBC connection to the database.....

I don't need to use all the fields....however there may be a field I need that Access is not showing me because of the 255 limit...

That is the problem I have having...why they built it this way??? No idea...I will have to ask the guy from 1980 who built it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:44
Joined
Feb 19, 2013
Messages
16,553
Not tried it and don't have anything to try out on but I believe an ADO recordset can return 1024 columns from a table. - see this link https://www.sqlservercentral.com/forums/topic/max-number-of-columns-ado-recordset-can-handle

There may be a 255 column limit set in access and you would struggle to display all the data which would have to be in a form - you can assign an ADO recordset to a form, but not a report.

But assuming there is no access limit to an ADO query in VBA

SELECT TOP 1 * FROM sqlTable

then vba to loop through the fields collection to print out field name and value, perhaps datatype as well

Suggest you get a list of all the fields (from Crystal perhaps) and write queries just pulling the fields you need or think you need. Once you see the type of data you can document each field for future reference - although would have thought that information would already exist somewhere
 

gmatriix

Registered User.
Local time
Today, 03:44
Joined
Mar 19, 2007
Messages
365
Not tried it and don't have anything to try out on but I believe an ADO recordset can return 1024 columns from a table. - see this link https://www.sqlservercentral.com/forums/topic/max-number-of-columns-ado-recordset-can-handle

There may be a 255 column limit set in access and you would struggle to display all the data which would have to be in a form - you can assign an ADO recordset to a form, but not a report.

But assuming there is no access limit to an ADO query in VBA

SELECT TOP 1 * FROM sqlTable

then vba to loop through the fields collection to print out field name and value, perhaps datatype as well

Suggest you get a list of all the fields (from Crystal perhaps) and write queries just pulling the fields you need or think you need. Once you see the type of data you can document each field for future reference - although would have thought that information would already exist somewhere
This is helpful, I will try....Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:44
Joined
Feb 19, 2002
Messages
42,981
If you explain the problem to the DBA, he will rail about what a toy Access is but then he might take pity on you and create the View you need. Won't hurt to ask.
 

gmatriix

Registered User.
Local time
Today, 03:44
Joined
Mar 19, 2007
Messages
365
If you explain the problem to the DBA, he will rail about what a toy Access is but then he might take pity on you and create the View you need. Won't hurt to ask.
Yes...agreed...LOL

The amount of red tape and round table of approvals needed to get that done is really not worth it IMO....by the time I get it......the thrill will have past...lol

Took me forever just to get a virtual machine....lol

I just don't understand why they haven't updated it...?? Also when I link tables....it is not in apha order.....??? huh??
How does Crystal do this and not Access?? Oh well....

When I started at my company I went straight for SQL Server.....but the amount of red tape to have access to create views....caused me to abandon it.....and just use access.....I'm sure others have big corporation struggles....LOL
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:44
Joined
Feb 19, 2002
Messages
42,981
The difference is that Access is based on Jet/ACE and so the limitations of those databases constrain Access to some degree.

You might be able to get past the problem if you use a pass through query to create your own view. You can't update the data anyway. Try that. Create a query with just the columns you need. Since you are not working with a linked table, Access won't care. And since you are not selecting more than 255 columns, there won't be an issue with the column count.

Once you make the passthrough query, you can use it as you would a view and join it to other tables/queries. The issue will be that joining to the passthrough query will make your final query not updateable.
 

Users who are viewing this thread

Top Bottom