Making Reports from Mysql 1

[romh]

Hi. I have used the search function before posting this question, and was not able to find my response. This is my problem.
I am migrating an Access 2002 backend to Mysql in hopes of getting better performance from mysql's client/server architecture. Mainly because I am linking various Access front ends across the state, to a MYSQL server backend.
I have successfully eliminated the use of linked tables and simply open recordsets via ADO to the mysql backend. For forms, this works like a charm. But in Access 2002, you cannot assign a recordset to a report. It says that "this cannot be done in an MDB."
I could use pass through queries, but my problem is that pass through queries cannot be programmed to include references to form's text boxes. For example:
Select * from Inventory Where StockNum = Forms!Form1!text1

I need to able to make an SQL statement dynamically right before opening the report.

How would you guys print reports considering these limitations? Would you open an ADO recordset, and copy all the records to an Access temp table, and open the report based on that table? Or is there a way to programmatically create a pass through query? Or could I use an ADP project with a Mysql backed instead.

Thanyyou very much. I have extensive VBA and Access 2000/2002 knowledge.

 

[dhookom]

I would use DAO to change the SQL of a saved pass-through query. You would build your sql using MySQL syntax and have a line of code like:
strSQL = "SELECT * FROM...Where ...."
Currentdb.QueryDefs("qsptMyRptQuery").SQL = strSQL

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[romh]

Could I use ADO instead of DAO? I did not know that that could be done. That is precisely what I am looking for? I'll try it out. Thanks a million.

 

[romh]

That worked like a charm. That is precisely what I was looking for. Another star for you. One more question.

What is the advantage of binding a stored procedure to a report (ex: from SQL server) versus the method you just showed me. Aren't they both sending and generating only the required SQL and data files, thus using the client/server advantage (where only the required data in passing through network lines)? Right now, this is my only option, considering that mysql's version 5.0 (which includes stored procedures) is still in its testing phases and you cannot open a stored procedure from Access and myODBC.

Thankyou again.

 

[dhookom]

An Access MDB would not allow you to bind a stored procedure to a report. You could bind a pass-through query to a report. An Access Data Project (ADP) would allow you to bind a Report directly to a Stored Procedure.

Not all stored procedures return a single recordset.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[romh]

Would an Access ADP allow me to bind a stored procedure to a report with a backend other than SQL Server? Could I make an ADP with a Mysql backend?
I think the answer to my question about sending ADO queries to the server versus stored procedures, is that stored procedures would probably execute faster since they are pre-compiled in the server.
Thanks alot. I will use your solution for all my reports whose sql gets generated on the fly.

 

[dhookom]

ADPs are only for MS SQL Server. Stored Procedures should be more efficient.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[romh]

Can I conclude that your procedure is the only way of generating SQL from VBA for reports with a backend other than SQL Server? Do I have any other options?

 

[dhookom]

I'm sure there are other methods. Assume you have a stored procedure that accepts start and end dates as well as EmployeeID. Consider a table with two date fields [StartDate] and [EndDate] and an [EmployeeID] field. If you set the values in the ONE record in this table, you can use the values in the table for your "parameter" values rather than sending them in through the SP.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]