Crystal 5 Connecting to 2 ODBC connections (One SQL Server, the other ACCESS)
I have several reports all designed and tested to work with SQL Server via
ODBC. I have created a new ODBC DSN to connect to an ACCESS database. the
SQL Server and access tables will be the same as will the fields.
I do not want to distribute two sets of reports, one for SQL Server and one
for access. The default is to connect to SQL Server, only some clients
will need the ACCESS database so linking to the SQL Server tables in the
access database is not an option.
I have the following code which seems to be close but generates an error
when typeing to print the Access report.
Private Sub Command1_Click()
Dim RPTEngine As Integer
Dim RPTReportHdl As Integer
Dim RPTOutputHdl As Integer
Dim RPTStartHdl As Integer
Dim RPTLocation As PETableLocation
Dim RPTSetRet As Integer
Dim tlen As Integer
Dim tblType As PETableType
Dim lgOnnInfo As PELogOnInfo
Dim numtables As Integer
Dim x As Integer
Dim RetError As Integer
Dim dummy As String
Dim i As Integer
Dim ttTableType As PETableType
Dim liLogOnInfo As PELogOnInfo
RPTEngine = PEOpenEngine
RPTReportHdl =
PEOpenPrintJob("D:\code\source\Barlows\Core\Reports\Staff.rpt")
RPTOutputHdl = PEOutputToWindow(RPTReportHdl, _
"Test", _
-32768, _
-32768, _
-32768, _
-32768, _
0, _
0)
'PEGetNthTableLocation RPTReportHdl, _
0, _
RPTLocation
' Logoff the server
'ttTableType.StructSize = PE_SIZEOF_TABLE_TYPE
'RetError = PEGetNthTableType(RPTReportHdl, _
' 0, _
' ttTableType)
'If RetError = 0 Then
' RetError = PEGetErrorCode(RPTReportHdl)
'End If
'liLogOnInfo.StructSize = PE_SIZEOF_LOGON_INFO
'RetError = PEGetNthTableLogOnInfo(RPTReportHdl, _
' 0, _
' liLogOnInfo)
'If RetError = 0 Then
' RetError = PEGetErrorCode(RPTReportHdl)
'End If
'RetError = PELogOffServer(ttTableType.DLLName, _
' liLogOnInfo)
'
'If RetError = 0 Then
' RetError = PEGetErrorCode(RPTReportHdl)
'End If
numtables = PEGetNTables(RPTReportHdl)
For x = 0 To numtables - 1
If Option1.Value Then
RPTLocation.Location = "INFOTRANS.dbo.StaffDetail" & Chr(0)
'RPTLocation.Location = ";DSN=INFOTRANS;UID=sa;PWD=" & Chr(0)
' liLogOnInfo.ServerName = "INFOTRANS"
' liLogOnInfo.UserID = "sa"
' liLogOnInfo.Password = ""
' liLogOnInfo.DatabaseName = "INFOTRANS"
' PELogOnServer ttTableType.DLLName, _
' liLogOnInfo
Else
'liLogOnInfo.ServerName = "INFOTRANSACCESS"
'liLogOnInfo.UserID = ""
'liLogOnInfo.Password = ""
'liLogOnInfo.DatabaseName = "StaffDetails"
'RetError = PELogOnServer(ttTableType.DLLName, _
' liLogOnInfo)
'If RetError = 0 Then
' RetError = PEGetErrorCode(RPTReportHdl)
'End If
RPTLocation.Location = "INFOTRANSACCESS.StaffDetail" & Chr(0)
'RPTLocation.Location = "C:\INFOTRANS\CORE.MDB.StaffDetail" &
Chr(0)
'RPTLocation.Location = ";DSN=INFOTRANSACCESS" & Chr(0)
End If
RPTLocation.StructSize = PE_SIZEOF_TABLE_LOCATION
' Set the new database location to the Access ODBC DSN
RPTSetRet = PESetNthTableLocation(RPTReportHdl, _
0, _
RPTLocation)
If RPTSetRet = 0 Then
RetError = PEGetErrorCode(RPTReportHdl)
End If
PEGetNthTableLocation RPTReportHdl, _
0, _
RPTLocation
PEDiscardSavedData RPTReportHdl
Next x
RetError = PETestNthTableConnectivity(RPTReportHdl, 0)
If RetError = 0 Then
RetError = PEGetErrorCode(RPTReportHdl)
End If
RPTStartHdl = PEStartPrintJob(RPTReportHdl, True)
If RPTStartHdl = 0 Then
RetError = PEGetErrorCode(RPTReportHdl)
End If
' Close the print job
PEClosePrintJob RPTReportHdl
PECloseEngine
End Sub
The error occured when the PEStartPrintJob is called. The PEGetErrorCode
returns code 527.
An ODBC error is displayed in a message : Invalid object name
'INFOTRANSACCESS.StaffDetail.
The ACCESS ODBC DSN is called INFOTRANSACCESS and the table is called
StaffDetail and does exist. If this same change is made in crystal the
report works.
Thanks in advance.