The RDC requires different logon under SQL Server than it does Oracle. Here is the code for successful VBA logon to SQL Server. This problem took two weeks out of my life; hope someone else can benefit.
Dim crwApplication As New CRAXDRT.Application
Dim crwReport As New CRAXDRT.Report
Dim crwSubReport As CRAXDRT.Report
Dim crwTables As CRAXDRT.DatabaseTables
Dim crwTable As CRAXDRT.DatabaseTable
Dim crwSections As CRAXDRT.Sections
Dim crwSection As CRAXDRT.Section
Dim crwReportObjects As CRAXDRT.ReportObjects
Dim crwSubReportObject As CRAXDRT.SubreportObject
Set crwReport = crwApplication.OpenReport(searchrptno)
For i = 1 To crwReport.Database.Tables.Count
crwReport.Database.Tables(i).SetLogOnInfo strSQLDatabase, strSQLDatabase, "", ""
tblLocation = strSQLDatabase & ".dbo." & crwReport.Database.Tables(i).Location
crwReport.Database.Tables(i).Location = tblLocation
If crwReport.Database.Tables(i).TestConnectivity Then
End If
Next i
Set crwSections = crwReport.Sections
For k = 1 To crwSections.Count
Set crwSection = crwSections.Item(k)
Set crwReportObjects = crwSection.ReportObjects
For i = 1 To crwReportObjects.Count
If crwReportObjects.Item(i).Kind = crSubreportObject Then
Set crwSubReportObject = crwReportObjects.Item(i)
Set crwSubReport = crwSubReportObject.OpenSubreport
For j = 1 To crwSubReport.Database.Tables.Count
crwSubReport.Database.Tables(j).SetLogOnInfo strSQLDatabase, strSQLDatabase, "", ""
tblLocation = strSQLDatabase & ".dbo." & crwSubReport.Database.Tables(i).Location
crwSubReport.Database.Tables(i).Location = tblLocation
If crwSubReport.Database.Tables(i).TestConnectivity Then
End If
Next j
End If
Next i
Next k
IP Logged
