Change mysql connection at runtime

[dstrange]

Right now I've got a working report but how can I change the report datasource in code @ run time?

If it helps my report's datafields are from a Command I created in CR9 design.

 

[dstrange]

I have used this thread766-115032 as a guide so far for trying to make this report dynamic because the database connection can vary. I am using CR9 and MySQL database. I'm still trying to figure out how to setup the exact same command I have working from CR by code in vb6 because the database connection will change time to time. Also if I leave the command I have setup on my report will doing it through code override the existing one? This is all new to me. Thanks

' Create a new instance of an ADO command object

Dim datcmd1 As ADODB.Command
    Set datcmd1 = New ADODB.Command
    Set datcmd1.activeConnection = cnn1
    datcmd1.CommandText = "SELECT Sum(If(IsNull(paid),0,paid) + If(IsNull(provided),0,If(paid>0,0,provided))) AS totalpaid, Billing.method AS typepaid, Billing.date, Visits.exam9 FROM Visits RIGHT JOIN Billing ON Visits.prim_key = Billing.visit_num GROUP BY Billing.date, Visits.exam9, Billing.method"
    datcmd1.CommandType = adCmdText

' Add the datasource to the report

    rpt.Database.AddADOCommand cnn1, datcmd1

    testTest.Open datcmd1

'Then, modify your addadocommand call:

   rpt.Database.AddADOCommand testTest.activeConnection, testTest.ActiveCommand

This is where I am unclear on. How do I correctly set my fieldobjects to work with my newly created command. This is what I have so far but I am uncertain as to what should be done.

    Set fld = rpt.Sections(3).AddFieldObject("{datcmd1.Command GroupNamedatedaily1}", 0, 0)

 

[dstrange]

An update on what I've done now. I've abandoned the add command and decided to create the same a view on both the dev and prod mysql databases. Everything works fine when chasnging databases through Crytal Reports 9 in VB but I need to be able to automatically change table connections through code. I have spent a couple of days on this now and I havn't had much success.

What's the most efficient way to update the odbc database connection to my report through code. The name of the view is rptcrjournal. I figured I could just try this below but no luck.

query = "Provider=MSDASQL.1;Extended Properties=;DATABASE=thedatabase;DRIVER={MySQL ODBC 3.51 Driver};OPTION=1 + 2 + 8 + 32 + 2048 + 16384;PWD=thepassword;PORT=3306;SERVER=555.555.55.55;UID=theuid"

rpt.Database.SetDataSource(query, rptcrjournal)

Crystal Reports 9
ODBC (RDO) Connection to MySQL database.

 

[dstrange]

I figured it out.
I overlooked that the datasource IS SET to a recordset and NOT the actual server connection.