Runtime Ado Connection Assignment for Multi-Table Report

[Foada]

I did a quick check of the FAQs and a search but with no clear answer. I am a newb with CR and I am in a real time constrant so I appologize if this seems simple. Basically I am using VB6 w/ CR 8.5 on Win2K. (All current) I designed my report which is based from an Access2000 DB and obtains its data from 10 different tables. That is working properly with no problems. What is the best way to change the data source to a different Access DB location? The DB design will be the same but the actual location can be changed. I was able to change the data source by creating a new ADO connection and then filling a recordset and using the SetDataSource method, but I then have to perform this for each of the 10 tables. I designed the report using the RDC. Is there someway I can change the DB path/location at runtime without having to refill all the recordsets? Any help will be greatly appreciated. Thanks in advance.

Option Explicit
Dim Report As New crxSummary
Dim cnn1 As ADODB.Connection

Private Sub Command_Click()
Dim strCnn As String
Dim rs As New ADODB.Recordset
Dim sSQL As String
'
Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" & _
"Data Source = G:\Win2K\Projects\Development\VisualStudio 6\Visual Basic\Development\Voting50\CTIVote50.mdb"
cnn1.Open strCnn

'THIS MUST BE DONE FOR ALL 10 TABLES
sSQL = "Select * From Owner"
With rs
.CursorLocation = adUseClient
.Open sSQL, cnn1, adOpenStatic, adLockBatchOptimistic, adCmdText
End With
Report.DiscardSavedData
Report.Database.Tables(1).SetDataSource rs

crxReport.ViewReport
End Sub

If you choose to battle wits with the witless be prepared to lose.

 

[synapsevampire]

Rather than using 10 tables, you might create a Access query which does the joins, and then use a ODBC connection and then just change the ODBC connection.

-k

 

[Foada]

Thanks for your response. The problem is not all of the data is related. I could join 4 of the tables in a query but I would still need to pull data from 6 tables.

If you choose to battle wits with the witless be prepared to lose.