[A2K7] Report design hangs viewing Data properties

[GeoffreyMcLaren]

The report designer hangs when I am assigning a control source or viewing the Data properties tab.
Visually the hang seems to occur when Access is trying to determine the "Text Format" property.
It does eventually come back. But, depending on the size of the ODBC attached SQL view I'm using as a data source it can be quite lengthy. 10's of minutes.
This problem did not exist in Access 2000.

Any help would be appreciated.

 

[GeoffreyMcLaren]

Further:

http://groups.google.com/group/micr...orts/browse_frm/thread/80f73b1550ada7fd?tvc=1

"We use Access 2007 to do some ad-hoc reports from Oracle. We link using ODBC and create a query with our criteria. If we try to create a report, Access will appear to hang. We have found that for some reason Access sends a SQL Select command through ODBC to the database server but it does not include the criteria from the view. If you have a very large table with thousands of rows and many columns, it can take a long time for ODBC to retrieve the data. If you just run the report, it uses the selection criteria and works great. The problem also occurs if you are connected to a SQL Server database. It was not a problem on Access 2003. I think there are some other instances where this behavior is exhibited but I cannot duplicate the problem consistently. If I open the report in design view and right-click on any field in the report to view the properties, it sends a query to the server without criteria. Is there some setting we can use to tell Access not to keep going to the Server or at least to include the criteria when it does or perhaps just get one row? The report is not using the data in the design view. This behavior has caused a lot of problems for our users.

 

[pjm]

Hi

In these circumstances I create a "skeleton" version of the pass-through query like:-

select 'A' colname1, 'B' colname2, 0 value1, 0 value2 from dual

Using your real query's column names - and because it is only selecting one row it returns very quickly. When I am happy with the report design I change the record source of the report back to the proper query.

HTH

pjm