Report ADO recordsource causes Access Crash

[dabruins]

I've been tearing hair out over this one the past few days. I have created a report that uses an SQL Pass-Through Query to a MySQL backend database as its recordsource. If I run the query as it is (simply double-click on it), it returns the records from MySQL as expected in a datasheet view. If I click on the report which now uses the query as its recordsource the access database prompts me with the notification "Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience." along with an option to send an error report to Microsoft and repair the open database. Once this is done it shuts down access entirely.

This now occurs if I attempt to copy the report or access it in design mode as well.

At one point I had the report working ok but via a different approach. I would setup the query string based upon settings on a form set by the user. I would then set the QueryDef of the query to that of the query string as well as set the connection string to be used to connect to the backend MySQL database. Once these were set i would then preview the report. Again this had worked a few weeks ago in the same frontend database but now it too causes the application to crash.

Has anyone any experience with viewing reports in access that query records in a MySQL database or via ADO? I would appreciate any input on this. Thanks!

 

[Crowley16]

you could try making another query which just selects all the contents of the passthrough query and then bound the report to the new query...



--------------------
Procrastinate Now!

 

[dabruins]

Hi Crowley.

I finally tracked this down late last friday. It turns out my version of msjet40.dll (9025) is incompatible with my version of the mysql ODBC driver. I reverted to an earlier version (8186) and now things are working as intended. I have set up my app to bypass the jet datbase engine using unbound forms but it looks like in this case in order to output to a report I have no other option than to work via jet (Report references a preset QueryDef)? The problem is now how to manage this with all the clients that will be using the reporting features in the frontend. Am I going to have to revert all their versions of the dll to 8186 and how will that affect their other apps? The other option seems to be to get the parameters for the query from a form, reset the query string for the QueryDef object and then open the report which uses it as a recordsource. I'm not too keen on this as I have to give admin access to the user on the query to allow this to proceed.

Thanks for your help?