Speed Problem please help - Access 97

[Cort]

Thank you for your help and I'm sure this has been asked before. I was unable to find anything in the FAQ's or prior threads.

I'm connecting using CR 8.5 to Access 97. I'm using direct connect. The Database is decent size 400MB or so and is uses several tables joined from other databases.

Connecting to this database using CR takes a very very long time. Around 10 - 15 mins. Then selecting the table(s)/query I was takes the same amount of time. After that is done though it works very well.

Is there a way to spped up the process. I feel I'm doing something wrong here. And thanking you again for your help here.

 

[DaveSw]

Hello, I just dealt with a similar problem. Not sure how much of my solution applies to you, but I was using direct connect and pulling data from a query (rather than a table). The problem I found is that in direct connect mode CR filters all of the records itself, which is dreadfully slow!

My solution was to switch to a OLEDB connection using the Jet driver. Using that method CR started using Access as a "SQL Database" (you can tell because the Show SQL Query database menu option is enabled). This was a lot faster because crystal used my record selection formula to build a SQL statement with a WHERE clause. This meant that Access did the filtering instead of CR.

Hope this is helpful for you!

 

[chelseatech]

I had a client with a subreport that searched through 2000 records on a remote MDB to find one record, and the subreport was executed for every detail in the main report.

Talk about slow. You could watch it counting...

Converted the driver to ODBC and the it was vastly faster. It seemed to be the remote MDB that cause all the delay, and ODBC sort of does Client/Server, even though it is Access behind the scenes.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Cort]

Thanks for the help all. I'll set up an ODBC connection and see if that helps. I'll let you know. CR 8.5 is a wonderfully flexible program and I hop to be able to use it but waiting 15 mins to refresh data on a report and 30 mins to begin to even create a report is not something I relish.

 

[Cort]

Thanks a ton chelseatech. The wait time is now down to 3 minutess to connect and 10 seconds to add a table/query. That is manageable.

Im also going to try the OLEDB connection method and see if that sppeds it up even more.

Thanks again