Crystal Reports throwing error "The table OEINVH could not be found" error - Sage 300 ERP 2

[sarojaramkumar]

Dear Team,
It is very urgent, your immediate attention is highly helpful and thankful

ERROR: WHEN CRYSTAL REPORT VIEWER IS OPENING THAT TIME IT IS THROWING ERROR - THE TABLE OEINVH COULD NOT BE FOUND
rest of the places, it is working fine.

Environment used: WINDOWS 2008 R2 SERVER, CRYSTAL REPORT XI sp4, SQL SERVER 2008R2


I am customizing OE INVOICE report, when i try to open crystal report using report viewer - it shows table could not be found error.
Please help, here below is my coding.


'-----------------------------------------------------------------------------
Private con As ADODB.Connection
Private rsOEINVH As ADODB.Recordset
Private rsOEINVD As ADODB.Recordset
Private rsARSAP As ADODB.Recordset
Private rsICLOC As ADODB.Recordset

Private crysapp As New CRAXDDRT.Application
Private crysrep As CRAXDDRT.Report
Private crysrep1 As CRAXDDRT.Report
Private crysdb As CRAXDDRT.Database
Private crysdb1 As CRAXDDRT.Database
Private crystbl As CRAXDDRT.DatabaseTable

'----------------------------------------------------
Dim abc, astr1, astr2, astr3, astr4 As String
Dim sWaybill, sInvoice As String
Dim inti As Integer
Dim provStr As String
'-------------------------------------- READING CREDENTIALS FROM XML
Dim sXmlFile As String
sXmlFile = App.Path & "\example.xml"
Dim oXML As New DOMDocument
oXML.Load sXmlFile
If oXML.parseError.reason <> "" Then
MsgBox oXML.parseError.reason
Exit Sub
End If
Dim oNode As IXMLDOMNode
Dim oAttr As IXMLDOMAttribute
Dim r As Integer
r = 1

For Each oNode In oXML.documentElement.childNodes

Set oAttr = oNode.Attributes.getNamedItem("vconfirm")

If r = 1 Then
'astr1 = oNode.nodeName & ": " & oNode.Text
astr1 = oNode.Text
ElseIf r = 2 Then
'astr2 = oNode.nodeName & ": " & oNode.Text
astr2 = oNode.Text
ElseIf r = 3 Then
astr3 = oNode.Text
ElseIf r = 4 Then
astr4 = oNode.Text

ElseIf r = 5 Then

mINVH1 = oNode.Text
ElseIf r = 6 Then

mINVH2 = oNode.Text

ElseIf r = 7 Then
mINVH3 = oNode.Text

ElseIf r = 8 Then
mWBH1 = oNode.Text

ElseIf r = 9 Then
mWBH2 = oNode.Text

ElseIf r = 10 Then
mWBH3 = oNode.Text

ElseIf r = 11 Then
mWBH4 = oNode.Text

End If

r = r + 1

Next oNode
'--------------------------------------------- CONNECTION STRING AND DATA
Set con = New ADODB.Connection
Set rsOEINVH = New ADODB.Recordset
Set rsOEINVD = New ADODB.Recordset
Set rsARSAP = New ADODB.Recordset
Set rsICLOC = New ADODB.Recordset


Set crysrep = Nothing

con.Provider = "sqloledb"
MsgBox ("Server info " & astr1 & astr2 & astr3 & astr4)

provStr = "Provider=MSDASQL; DRIVER=Sql Server; SERVER=" & astr1 & "; DATABASE=" & astr2 & "; UID=" & astr3 & "; PWD=" & astr4 & ";" ' xml PC
MsgBox (provStr)
con.Open provStr

MsgBox ("Opened")

sWaybill = App.Path & "\ENG\TSWAYBIL01.RPT"
sInvoice = App.Path & "\ENG\TSINV01.RPT" ' server

abc = ""
abc = mFromInvNo
'MsgBox (sInvoice)

Set crysrep = Nothing
'Set crysrep1 = Nothing
'MsgBox ("before the line crysapp.OpenReport(sInvoice, 1)")
'-------------------------------------------------------
Dim crysapp2 As New CRAXDDRT.Application
Dim crysrep2 As New CRAXDDRT.Report
Set crysapp2 = CreateObject("CrystalRuntime.Application.11")
Set crysrep2 = crysapp2.OpenReport(sInvoice, 1)

'If rsOEINVH.State = 1 Then rsOEINVH.Close

rsOEINVH.Open "select * from OEINVH", con, adOpenKeyset
'MsgBox ("rsOEINVH.RecordCount" & rsOEINVH.RecordCount)

If Not rsOEINVH.EOF Then

With crysrep2
'MsgBox ("INSIDE WITH BEFIRE .Database.Tables(1).SetDataSource (rsOEINVH)")

' MsgBox (.Database.Tables(0).SetDataSource(rsOEINVH))
.Database.Tables(1).SetDataSource (rsOEINVH)
.DiscardSavedData
End With
End If

'If rsOEINVD.State = 1 Then rsOEINVD.Close

rsOEINVD.Open "select * from OEINVD", con, adOpenKeyset

'MsgBox ("rsOEINVD.RecordCount" & rsOEINVD.RecordCount)

If Not rsOEINVD.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsOEINVD)
.DiscardSavedData
End With
End If

rsARSAP.Open "select * from ARSAP", con, adOpenKeyset

'MsgBox ("rsARSAP.RecordCount" & rsARSAP.RecordCount)

If Not rsARSAP.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsARSAP)
.DiscardSavedData
End With
End If

rsICLOC.Open "select * from ICLOC", con, adOpenKeyset

'MsgBox ("rsICLOC.RecordCount" & rsICLOC.RecordCount)

If Not rsICLOC.EOF Then
With crysrep2
.Database.Tables(1).SetDataSource (rsICLOC)
.DiscardSavedData
End With
End If


'---------------------------------------------------------------

'MsgBox ("***after the line crysapp.OpenReport(sInvoice, 1)")


inti = 1
MsgBox ("JUST BEFORE DO UNTIL-" & astr1 & astr2 & astr3 & astr4)

'MsgBox ("crysrep2.Database.Tables.Count " & crysrep2.Database.Tables.Count)

Do Until inti = crysrep2.Database.Tables.Count + 1
' MsgBox ("inside DO UNTIL- crystal logon" & astr1 & astr2 & astr3 & astr4)
'crysrep2.Database.Tables.Item(inti).SetLogOnInfo strdblocation, astr2, astr3, astr4
crysrep2.Database.Tables.Item(inti).SetLogOnInfo astr1, astr2, astr3, astr4
' MsgBox (crysrep2.Database.Tables.Item(inti).Name)
crysrep2.Database.Tables.Item(inti).SetDataSource (crysrep2.Database.Tables.Item(inti).Name)
' MsgBox ("inside DO UNTIL CR SetDataSource" & crysrep2.Database.Tables.Item(inti).Name)

inti = inti + 1

Loop



'crysapp2.LogOnServer "crdb_odbc.dll", astr1, astr2, astr3, astr4
crysrep2.DiscardSavedData
crysrep2.ParameterFields(1).AddCurrentValue (CStr(abc))
crysrep2.ParameterFields(2).AddCurrentValue (mINVH1)

crysrep2.ReadRecords


Dim copies As Integer
copies = 3


With CRViewer1

' MsgBox ("inside with CRViewer1 ")
'.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight
'.ReportSource = Nothing

.Zoom (1)
.EnableExportButton = True
.EnablePrintButton = True
'.RefreshEx (True)
' .Refresh
.ReportSource = crysrep2
'.Refresh
.ViewReport


End With



ProcExit:

Exit Sub

ProcError:

MsgBox Err.Description
Resume ProcExit

END SUB

 

[Jay Converse]

That's not how you update Accpac reports, it uses RDO ODBC's, not ADODB.

Sage 300 Whisperer

 

[DjangMan]

I recently had to look at some code I had written in the past to run Crystal reports outside of Sage's desktop. I came across a posting on Stack Overflow (

http://stackoverflow.com/questions/204611/crystal-report-datasource-remap/217756#217756)

that suggested code similar to what I have below. In this example the code is setting the connection information for each table in each report. There might be more efficient ways to run this but, for now, it did what I needed it to do. I've created an ADO connection to the database prior to calling my routine.

Public Sub connectReportToDatabase(aReport As Object) 'CRAXDRT.Report)

10        On Error GoTo connectReportToDatabase_Error

          Dim table As Object 'CRAXDRT.DatabaseTable
          
30        For Each table In aReport.Database.Tables
50            If table.dllname <> "crdb_ado.dll" Then
70                table.dllname = "crdb_ado.dll"
80            End If
          
100           table.ConnectionProperties.DeleteAll
          
120           table.ConnectionProperties.Add "Provider", ADOConn.Provider
130           table.ConnectionProperties.Add "Data source", ADOConn.Properties("Data source").Value
140           table.ConnectionProperties.Add "Database", ADOConn.DefaultDatabase
              
150           If Trim$(ADOConn.Properties("Integrated security").Value) = "" Then
170               table.ConnectionProperties.Add "User ID", ADOConn.Properties("User ID").Value ' "appaccpac"
180               table.ConnectionProperties.Add "Pwd", ini_Password 'ADOConn.Properties("Password").Value 
190           Else
210               table.ConnectionProperties.Add "Integrated security", ADOConn.Properties("Integrated security").Value
220           End If
240           table.ConnectionProperties.Add "Persist Security Info", ADOConn.Properties("Persist Security Info").Value
260           table.ConnectionProperties.Add "Initial Catalog", ADOConn.Properties("Initial Catalog").Value
          
280           table.SetTableLocation table.location, "", ADOConn.ConnectionString
         
300           table.TestConnectivity
          
310       Next table
330       Exit Sub    

340       On Error GoTo 0
350       Exit Sub

connectReportToDatabase_Error:

360       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure connectReportToDatabase of Sub frmMain @ line " & Erl

End Sub

Private Sub PrepReport(aRptName As String)

    Dim n As Integer
    Dim i As Integer
    
    Set Report = CreateObject("CrystalRuntime.Report") ' & CRVersion)
    Set Report = CRApp.OpenReport(aRptName)
    
    connectReportToDatabase Report

    Set Sections = Report.Sections
    For n = 1 To Sections.Count
        Set Section = Sections.Item(n)
        Set RepObjs = Section.ReportObjects
        For i = 1 To RepObjs.Count
            If RepObjs.Item(i).Kind = 5 Then
                Set SubReportObj = RepObjs.Item(i)
                Set SubReport = SubReportObj.OpenSubreport               
                connectReportToDatabase SubReport
            End If
        Next i
    Next n

End Sub

 

[sarojaramkumar]

Dear DjangMan (Programmer)

Thank you........... very much......... for your code help.. .......
started working..

I also tried the following steps: it started working,

Set datasource location-> click on the table, it will display properties, in the properties last option "Overridden Qualified table name". when i clicked on that, it asked for table name, I just typed the same table name (without spelling mistake)

Then it started working..

 

[sarojaramkumar]

Dear tuba2007 (Programmer)...

Thank you very much for your advise..