Reporting from Project Server - MS Access Limits

[Hadi]

I dont know if i need to ask this question here or in the Access section. I
have an ODBC connection to the Project Server database so I can make reports
through Access. Access' limit of 255 fields per table is causing me some
trouble. for example, the MSP_VIEW_PROJ_PROJECTS_ENT table has well over 255
fields. Access only shows me the first 255 fields. how can I change that so I
can see all the fields in that table?

thanks,

Hadi

 

[Lizzie Beth]

Hadi,
I have not tried this yet it may be a viable option. Have your DBA create a
view that pulls the key fields to this table and the specific fields you are
wanting to report on and make sure you don't exceed the 255 field limit. If
you need all the data fields, then create multiple views with the key fields
so you can tie them together.

For example, create a view of key fields and all the project enterprise cost
fields. Create another view of key fields and all the project enterprise
date fields. Repeat for all the data that you want to view/report. Link to
these views and query the data from them.

When using access and linking multiple tables, you may experience
performance issues so be careful so you don't impact your production server.

Good Luck,
Lizzie Beth

 

[Hadi]

Lizzie, thanks i'll give that a try

Hadi,
I have not tried this yet it may be a viable option. Have your DBA create a
view that pulls the key fields to this table and the specific fields you are
wanting to report on and make sure you don't exceed the 255 field limit. If
you need all the data fields, then create multiple views with the key fields
so you can tie them together.

For example, create a view of key fields and all the project enterprise cost
fields. Create another view of key fields and all the project enterprise
date fields. Repeat for all the data that you want to view/report. Link to
these views and query the data from them.

When using access and linking multiple tables, you may experience
performance issues so be careful so you don't impact your production server.

Good Luck,
Lizzie Beth