Handling Multiple Databases

The current version of the report wizard supports only one database connection string but however, you can programmatically change your database connection string at runtime to handle multiple databases. This can be complished with a simple conditional state such as the IF or SELECT statements.
We can use a simple if statement as follows:

<%

'if some condition e.g. Session, then use strConn1
If blnCondition Then
objWiz.Init(strConn1)
Else
objWiz.Init(strConn2)
End If

%>

This method will remain fine if both databases have the same structure - i.e. the same tables and table fields.
If they don't, then you can do either of the following before you set your connection string:

Switch your reports file to match your connection string or
Show only the reports that are associated with the current connection string.

Switching your reports file will be easier because you can just accomplish that as follows:

<%

Dim strFile, strConn, strDB

If blnCondition Then
    strDB = "C:\Databases\NorthWind.mdb"
    strFile = "C:\Reports\NorthWind.xml"
Else
    strDB = "C:\Databases\Pubs.mdb"
    strFile = "C:\Reports\Pubs.xml"
End If

strConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
    "DATA SOURCE=" & strDB & ";"

objWiz.ReportsFile = strFile
objWiz.Init(strConn)

%>

The second method - Showing only the reports associated with the current connection string is a little more complicated in the it will involve you associating all your reports into groups and using or showing only certain groups or hiding other groups by use of the UseReports and HiddenReports properties as follows:

<%

Dim strGroup, strConn, strDB

If blnCondition Then
    strDB = "C:\Databases\NorthWind.mdb"
    strGroup = "NorthWind"
Else
    strDB = "C:\Databases\Pubs.mdb"
    strGroup = "Pubs,Bookstore,Authors"
End If

strConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
    "DATA SOURCE=" & strDB & ";"

objWiz.UseReports = strGroup
objWiz.Init(strConn)

%>

The best strategy as of now to will be to create multiple instances of the report wizard in different folders that are directed to a different database. This way, you can customize your stylesheet file styles.css and security settings based on the database or department.

...