Design and Open Reports that connects to Password Protected Database

[Mirak]

Hi,
I need some urgent help. I have created a database application in VB that opens a password protected access
database. I have manage to code the password for the DAO to open it. But I am having major league problems with the connection
for the designer for the reports. Each time I want to connect to the connections in design mode, I get prompted for a password.
When I try to supply the password, it says that the workgroup file is missing and reports that the connection failed.
Why does it keep prompting for passwords at run time and design time when I try to access reports.

Help! I have fallen and I can't get up!

Mirak

 

[TomKane]

Maybe if you open the file with an odbc connection string? We do that and have the password embedded in the connection string. Probably not the cleverest from a security point of view....

 

[Mirak]

Thanks Tomkane,

But I have tried that scenario I think.
Here are the codes I have:

This one connects the data evironment to the database: (I need to code the password somewhere in here but I can't seem to get the syntax correct)

EnvInsurance.SupervisorOfInsurance.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\SupervisorOfInsurance97.mdb"

This one connects the DAO to the database and works fine:

Public Login As New frmLogin
Public Luser As String
'Define database object to be used by all routines and functions
Public dbosi As DAO.Database
Public strPassword As String
Sub Main()
'Open the database using password
strPassword = "creative"
Set dbosi = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\SupervisorOfInsurance97.mdb", dbDriverComplete, False, "; PWD=" & strPassword)

Login.txtConfpasswd.Visible = False
Login.lblConfirm.Visible = False
Login.dtalog.Refresh
Set Login.dtalog.Recordset = dbosi.OpenRecordset("Select * From tblUsers;&quot
Login.Show
End Sub

 

[Imhoteb]

Are you using an access database?
If you are you can connect using ado like this
Put this in a module

Option Explicit
Public myConn As New ADODB.Connection
Public RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command

Public Sub CloseConn()
RS.Close
myConn.Close
End Sub

Public Sub OpenConn(myDatabase As String, myPath As String, sqlCode As String, strPwd As String)
Dim AccessConnect As String
Dim stQuery As String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                    "Dbq=" & myDatabase & ";" & _
                    "DefaultDir=" & myPath & ";" & _
                    "Uid=;Pwd=" & strPwd & ";"
myConn.ConnectionString = AccessConnect
myConn.Open

    With Cmd
       Set .ActiveConnection = myConn
       .CommandType = adCmdText
       .CommandText = sqlCode
    End With

    With RS
      .LockType = adLockPessimistic
      .CursorType = adOpenKeyset
      .Open Cmd
    End With

End Sub

You can use the module like this

OpenConn "myDB.mdb",App.Path & "\Database\", "select*from myTable",myPasw   'myPasw is a variable where the psword is stored
RS.Requery

How do you connect to your report?
Are you using the CR OCX?
If you are you can open your report like this
Say rptReport is the ocx you put on your form wich allows you to connect to the report

rptReport.ReportFileName =App.Path & "myReport.rpt"
rptReport.SelectionFormula="{myTable.Name}='" & txtName & "'"
rptReport.Connect = "PWD=" & myPasw
rptReport.PrintReport

Hope this helps

 

[Mirak]

Thanks Imhoteb , that sounds like a possible solution in creating the connection.
But I don't want to design the reports in access, because that would open the database in order to view the report and thus users(hackers wanna be) would then have access to other database objects through this operation. I am trying to use the report uitility in VB to design the report as well (I don't have Crystal). I would need to tie the report to the command. How do we cross that bridge....

Mirak
There is no perfect man, only perfect intentions.

 

[Imhoteb]

The solution I suggested here before uses a report created with crystal reports. You can find crystal reports on the third CD from VB 6.0. If you install this program you will be able to create a report with crystal reports (version 4.6)and open this report in VB when putting the crystal report OCX on the form