error connecting password protected access database to crystal reports from visual ba

[dipakmishra]

hello everybody
this problem is bothering me for last two days,i have an application in VB
with a password secured access database, in my Visual Basic application i
have added a form and am viewing crystal report... i made connection and
added fields to report but when i run it logon fails.. i have simply added
connection via wizard and have given pass word there...but when i opens the
report via Visual basic application it fails to open..i am using microsoft access as database
also i tried giving connection string : to my form load but still giving
error:
string is:
REPORT.DATABASE.LOGONSERVEREx "p2sOLEDB.DLL","PROJ.MDB
;"","","","OLEDB","PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA
SOURCE=PROJ.MDB;PERSIST
SECURITY INFO=FALSE;JET OLEDBATABASE PASSWORD=PASSWRD"
IT GIVES DAO ERROR CODE 0XBD7
SOURCE = DAO WORKSPACE
DESCRIPTION : NOT A VALID PASSWORD..
I CHNGED CONNECTION TO ODBC AND CHANGED OLEDB ABOVE TO ODBC BUT THEN IT
GIVES ERROR SAYING ERROR IN PROVIDER IF I RUN ONLY A CRYSTAL REPORT MADE IN
CRYSTAL REPORTS 9 WITHOUT USING VISUAL BASIC IT RUNS WELL ONLY WHEN I RUN IT
FROM VB IT GIVES ERROR... PLZ HELP
AM REALLY STUCK

[pnish]

Are you using the Crystal OCX? If so do this

Report.Password = Chr$(10) & "mypassword"

[dipakmishra]

hii
thanks for the reply
ifu can plz tell me how to pass recordselection values using this method it works fine .. but am not able to view report selected as per selection formulae which i want to pass thru my vb form ,
thanks in advance

[pnish]

I'm not sure exactly what you mean, something like this:

Dim rSQL As String
 
rSQL = "{MyTable.MyField}='" & MyCondition & "'"
Report.Password = Chr$(10) & "MyPassword"
Report.SelectionFormula = rSQL
Report.PrintReport

[dipakmishra]

hello pnish
see i connected report to access database as told u and was successfull thanks for that,
secondily i want to pass selection formula which too u gave..
but after clickin report. selectionformulae is not the option there which sld have come....

thirdly how to specify path of report so that after installation it looks for database in application.path also... i want to know how to to do calculation on fields.......in that too i am having small problems.......

i am thankful to u a lot..
its urgent plzzzzzzzzzzz

[dipakmishra]

it gives error on recordselection saying member or method not found
also plz tell me how to give app.path
thanks
plz help

[pnish]

Are you using the Crystal OCX? I need to know if I'm to help you properly. For the time being I'm assuming you are.

To change the database path, do this:

Report.DataFiles(0) = App.Path & "\myDB.mdb"
Report.DataFiles(1) = App.Path & "\myother.mdb"

DataFiles(0) refers to the first table on your report, DataFiles(1) refers to the second (if you have more than one table) and so on. You must refer to the tables according to their ordinal positions on the report. To determine this, open the report in Crystal and select Database from the main menu then select Database Expert. A list of tables will be shown in their ordinal positions. So the first table in the list will be DataFiles(0), the second will be DataFiles(1) etc.

[dipakmishra]

Thanks Pete for the reply
I dont know whether am using OCX or what but the code u gave for password worked just very well
Report.Database.Tables(1).SetSessionInfo "", Chr$(10) & "password"

what i am doing:
in Visual Basic Project click on 'Project'--------->Components---------->designers--------->crystal reports------>'ok' then in Project Explorer--------->rt click --------->ADD--------> Crystal Rports 9-------->then i gets Crystal Report Gallery to chose from existing or new reports ask for database tables, grouping etc.,(wizard)then ---------> it adds a form and crystal report1.dsr(designer files)----->form has coding

Dim Report As New CrystalReport1

Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Resize()
CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth

End Sub

and i add to it the line told by you:
Report.Database.Tables(1).SetSessionInfo "", Chr$(10) & "password"
prior to adding this line it use to say logon failed.
it runs well...now i want your help in selection formulae and giving application path code.....
as i tried : report.datafiles(0).... but it does not recognises the datafiles key word................
whether this gonna work:
report.database.tables(0)=App.Path & "\myDB.mdb"

also for selection formulae: the lines suggested by you are not working:
as doesnot identifies function .selectionformulae

Dim rSQL As String

rSQL = "{MyTable.MyField}='" & MyCondition & "'"
Report.Password = Chr$(10) & "MyPassword"
Report.SelectionFormula = rSQL
Report.PrintReport

but it has .........report.RecordSelectionFormula
SO HOW TO PROCEED next..........HOPE I AM ABLE TO MAKE U UNDERSTAND IT ALL.........
PLZ HELP ME

[pnish]

OK. You're using the Report Designer Component & the Report Viewer. Try adding this code (in red):

Dim Report As New CrystalReport1
[color=red]Dim crDB As CRAXDRT.DatabaseTable[/color]
 
Private Sub Form_Load()
 
    Screen.MousePointer = vbHourglass
 
    [color=red]Set crDB = Report.Database.Tables(1)
    crDB.Location = App.Path & "\myDB.mdb"
 
    Report.RecordSelectionFormula = "{mytable.myfield}='condition'"[/color]
 
    CRViewer91.ReportSource = Report
    CRViewer91.ViewReport
    Screen.MousePointer = vbDefault
 
End Sub

Hope that helps.

[dipakmishra]

Thanks a lot.. for ur quick reply and helping me out when i was really stuck.....i will try this out and will let u know.......
thanks a lot again

[dipakmishra]

and what abt password
shall i use it in form load 2nd line...?????
Report.Password = Chr$(10) & "MyPassword"

[pnish]

I thought you had the password bit solved and were using Report.Database.Tables(1).SetSessionInfo "", Chr$(10) & "mypassword".

If that's what you're doing and it works OK, put it here:

Dim Report As New CrystalReport1
Dim crDB As CRAXDRT.DatabaseTable
 
Private Sub Form_Load()
 
    Screen.MousePointer = vbHourglass
 
    Set crDB = Report.Database.Tables(1)
    crDB.Location = App.Path & "\myDB.mdb"
 
    [color=red]Report.Database.Tables(1).SetSessionInfo "", Chr$(10) & "mypassword"[/color]
    Report.RecordSelectionFormula = "{mytable.myfield}='condition'"
 
    CRViewer91.ReportSource = Report
    CRViewer91.ViewReport
    Screen.MousePointer = vbDefault
 
End Sub

[pnish]

I've just discovered another way to do it:

Option Explicit
 
Dim Report As New CrystalReport1
[color=red]Dim crProp As CRAXDRT.ConnectionProperties[/color]
 
Private Sub Form_Load()
 
    Screen.MousePointer = vbHourglass
    
    [color=red]Set crProp = Report.Database.Tables(1).ConnectionProperties
    crProp.Item("Database Name") = App.Path & "\mydb.mdb"
    crProp.Item("Database Password") = "mypassword"[/color]
    
    Report.RecordSelectionFormula = "{mytable.myfield}='condition'"
    
    CRViewer91.ReportSource = Report
    CRViewer91.ViewReport
    Screen.MousePointer = vbDefault
 
End Sub

Download this document from the Crystal web site:
http://support.businessobjects.com/c...erties.pdf.asp
It explains it all pretty well.

[dipakmishra]

dear pnish am facing a typical problem...
i successfully tried with the solution provided by you in connecting crystal reports: but since i was bogged by this for too long i tried solution from mazzard posted on this forum itself...
and am using selection formulae etc... in this method am making crystal reports in Crystal Report 9, again the database is secured one ...and am using Visual Basic 6.0, am connecting to reports made in crystal reports...by the following method...but when i make setup package to deploy it gives error if i try opening other report after opening any report once....it says memory referrenced click OK to terminate program and Cancel to debug it and package shuts downn.. i have used on error resume next too...i am not able to make out y this happening...u have a look at it and suggest if sthg can be done else i will make al reports via crystal report designer adding the reports already made but again then i will have problem of how to use the selection formulae which am using in this method on selection from list box and datetime picker...as i dont know how can we select and pass the formulae by the method of report designer.....and have done it this way after all my efforts as yesterday only was able to work well with the code given by u.. i had problem accessing secured database in crystal reports in my Visual Basic application i successfully did it after all the help from this forum now i am having this small problem

what i have done is that i used same line of code in all forms but changed the report source to report1 , report 2 , report3 respectively rest all lines are same
its running well in development envt but when i run it after installing the first report runs well while i click on next report it gives error memory referrenced ..click ok to terminate program or cancel to debug it..

plz help me where i am faltering..
i have added crystal reports report1 report 2 and report3 to my package as well while making deployment package...

help me its urgent

Private m_App As New CRAXDDRT.Application
Private m_Rep As New CRAXDDRT.Report
Public mRepName As String
Public mstrW As String
Dim strw As String
Public mstrUName As String
Public msRepTitle As String
Private Sub Command1_Click()
On Error Resume Next
If List1.Text = "" Then
MsgBox ("Select Job No")
Exit Sub
Else
If dtp1.Value = "" And dtp2.Value = "" Then
MsgBox ("Select Dates from two Calendars")
Exit Sub
Else
On Error Resume Next
'strW = "{assignjob.cusname} = '" & cboStudyAreas.Text & ""
''to use''''''''mstrW = "{ customerdetails.csno} = 9 "
Dim ConnectionInfo As CRAXDDRT.ConnectionProperties
Dim test As String

mRepName = App.Path & "\report1.rpt"
test = App.Path & "\ proj1.mdb"

Set m_Rep = m_App.OpenReport(mRepName, 1)
m_Rep.RecordSelectionFormula = mstrW
Dim nm As String
nm = List1.Text
Dim dt1 As String
Dim dt2 As String
dt1 = Format(dtp1.Value, "yyyy,mm,dd,0,0,0")
dt2 = Format(dtp2.Value, "yyyy,mm,dd,0,0,0")
strw = "{assignjob.jobno} = '" & List1.Text & "' and {assignjob.releaseddate} in DateTime (" & dt1 & ") to DateTime (" & dt2 & ")" ..........and other conditions too am using...

Set ConnectionInfo = m_Rep.Database.Tables(1).ConnectionProperties
'On Error Resume Next
m_Rep.Database.Tables(1).DllName = "crdb_dao.dll"
Set ConnectionInfo = m_Rep.Database.Tables(2).ConnectionProperties
m_Rep.Database.Tables(2).DllName = "crdb_dao.dll"
ConnectionInfo.DeleteAll
ConnectionInfo.Add "proj.mdb", test
ConnectionInfo.Add "session UserID", "admin"
ConnectionInfo.Add "Session Password", "dips"


Form1.mstrW = strw
List1.Visible = False
Command1.Visible = False
CRViewer91.ReportSource = m_Rep
'CRViewer91.Visible = True
Command2.Visible = True
CRViewer91.ViewReport
CRViewer91.Zoom 1
CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth
Set m_Rep = Nothing
End If
End If
End Sub



Private Sub Command2_Click()
main.Show
Unload Me
End Sub

Private Sub Form_Load()
On Error Resume Next
CRViewer91.Visible = False
Command2.Visible = False
Set cn = New ADODB.Connection
With cn
.Provider = "microsoft.jet.oledb.4.0"
.Properties("jet oledb:database password") = "dips"
.Open App.Path & "\proj.mdb"
End With
Set rs1 = New ADODB.Recordset
sql1 = "select distinct jobno from jobassign"
rs1.Open sql1, cn, adOpenDynamic, adLockOptimistic
List1.Visible = True
rs1.MoveFirst
Do Until rs1.EOF
List1.AddItem (rs1.Fields(0))
rs1.MoveNext
Loop
rs1.Close
cn.Close
End Sub


plz help me out.
thanks in advance

[pnish]

I'm not sure if this has anything to do with your problem but the following:

Private m_App As New CRAXDDRT.Application
Private m_Rep As New CRAXDDRT.Report

should be:

Private m_App As New CRAXDRT.Application
Private m_Rep As New CRAXDRT.Report

Note the single D in CRAXDRT, not double D as you had it. Also make sure all references are set to Nothing when you've finished with them.

[dipakmishra]

Thanks pnish,
i have set m_rep= nothing, what all referrences do i need to set to nothing..
plz tell in detail as am worried....with this problem a lot.............
thanks

[dipakmishra]

ok i neeed to set M_App=Nothing too.......
thanks i will try this on my project make a setup exe and will let u know............

[tansarbh]

What if the database is a SQL Server and we need to change the 'Server' in the connection string at run time?

Thanks.

[dipakmishra]

thats a good question,
well i know how to change datasource location in report....
this i have done with user sa and pwd=" " ((((no password default login in sql server))))
in report click on right click on data base option select SET Datasource Option .... in lower pane select the new datasource and click update this will change connection to new datasource.....

well with password thing what all changes needed in Visual Basic Code i will try and let u know...........

[JSteele94]

crProp.Item("Database Name") = App.Path & "\mydb.mdb"

I tried this and it says that "Item" is read only

??

[pnish]

It must have changed from Crystal v9