Crystal 5 Connecting to 2 ODBC connections (One SQL Server, the other ACCESS) 
Author Message
 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.



Sun, 01 Jul 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Crystal 7, Access through SQL/ODBC Connection

2. ODBC Connection gives read only access to SQL Server

3. sql server connect. Migration Access->sql server

4. sql server connect. Migration Access->sql server

5. sql server connect. Migration Access->sql server

6. SQL Server connections not closing when Access connection also open

7. DAO - Connect to SQL Server via ODBC

8. ODBC Connect to SQL server 6.5

9. VB4 ODBC connect to SQL Server problem

10. Problem connecting to SQL server via ODBC

11. Problem connect VB to SQL Server ODBC

12. connecting to sql server using ODBC...

 

 
Powered by phpBB® Forum Software