how to pass a password protected .mdb datasource to crystal reports

[stingrae]

Ad you can all guess from this post, i'm still drowning in crystal reports.I've looked everywhere on how to do this, but can't figure it out to save my life.

How do you connect a Crystal Report Viewer to a password protected .mdb file?

For example, here's my connection string:

        gconDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath & ";" & _
                                        "Persist Security Info=False;" & _
                                        "Jet OLEDB: Database Password=" & gcDatabasePassword & ";"

now when I attempt this:

        Dim rpt0001 As New ReportDocument()
        Dim tbCurrent As CrystalDecisions.CrystalReports.Engine.Table
        Dim tliCurrent As CrystalDecisions.Shared.TableLogOnInfo
        Try
            rpt0001.Load("..\sts_rpt0001.rpt")

            rpt0001.SetDataSource(gconDatabase)
            crv01.ReportSource = rpt0001

        Catch Exp As LoadSaveReportException
            MsgBox("Incorrect path for loading report.", _
                    MsgBoxStyle.Critical, "Load Report Error")

        Catch Exp As Exception
            MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
        End Try

it brings up a dialog box asking me the database location, user id and password etc..........

please help (says begging on his kees without any hair left)

[Edneeis]

This doesn't directly answer your question but it seems easier sometimes to just base the report on a database schema so you can pass in a dataset for the data. That way you handle getting the data on your own and don't have to mess with Crystal Reports on that part.

[stingrae]

edneeis,

thanks heaps for the reply. the only reason i do it that way, is because in previous versions of vb, it was the only way i could develop an app that ran over an .mdb file, contained reports, and that .mdb file could be located anywhere on the pc.

i guess what i'm trying to say is, how do i pass in the connection string to the report viewer? i can develop the report on my PC, but i want to make sure it works on all PCs.

does this make sense?

cheers

[Edneeis]

You use the SetDataSource method, but it can be problematic. What I meant was when you make the report instead of using an actual database you use the schema of a database, then the report works the same but it isn't really tied to any give db. Then you use the report at runtime you simply fill a dataset with the data for the report and pass in the dataset (which will match the schema) and thats it. Its the same concept really but it seems easier because the actual database connection is handled outside of the report.

[stingrae]

thanks mate, i'll give that a try.

p.s. where do I send your cheque?

[Edneeis]

I'm on direct deposit now

Here is an FAQ on how to do it from another site:
http://www.vbcity.com/forums/faq.asp...ports#TID10205

[stingrae]

i just increased your tip! i was going through a microsoft walkthrough and it is, well how can we say, "typically microsoft"?

[stingrae]

ok, after one hell of a late night going through walkthrough after walkthrough, i've finally pieced together:

* my first xml schema
* attaching my first ever c.report to the schema
* getting the c.r.viewer to actually display the report.

now, one thing left to do that i just can't get right. can you please give me a sample code snipet of what you meant when you said:

you use the report at runtime you simply fill a dataset with the data for the report and pass in the dataset

i just can't seem to get it right.

[Edneeis]

Sure here you go. I just used an xml file as my datasource but you can use whatever as the data and just fill it into the dataset.

The schema should represent the structure and data of the report, so a table or a query. So then you just execute that query, fill it into a dataset, then pass the dataset to the report as the data. Let me know if you have any questions.

[stingrae]

thanks ed. i now have my first report going. i have it working sweet for a one table report, but once i start to make my sql more complex, it stuffs up. i'll keep plugging away and figure it out. it's fustrating, but like anything i guess, it's all just confusion at first.

[Edneeis]

Good to hear it. Its all about getting the schema to match the query to make it work.

[stingrae]

in terms of schemas and so forth, are you able to explain something to me in the following:

        Dim ds As New DataSet()
        Dim strSQLStm As String = "SELECT * " & _
                                  "FROM tbl_ClientMaster " & _
                                  "WHERE (clm_First LIKE '" & DelimiterCheck(txtFirst.Text) & "%') " & _
                                    "AND (clm_Last LIKE '" & DelimiterCheck(txtSurname.Text) & "%') " & _
                                  "ORDER BY clm_First, clm_Last"
        Dim daClients As New OleDb.OleDbDataAdapter(strSQLStm, gconDatabase)
        daClients.Fill(ds, "tbl_ClientMaster")

        Dim rpt As New sts_rpt0001()
        rpt.SetDataSource(ds)

        Dim frmrpt As New frmReportViewer()
        frmrpt.CrystalReportViewer1.ReportSource = rpt
        frmrpt.Show()

when i run the above as is, it works perfectly (ok the report looks ****, but it works none-the-less). however, when i change the following line:
daClients.Fill(ds, "tbl_ClientMaster ")
to:
daClients.Fill(ds, "tbl_ClientMaster2 ")
it doesn't.

my question is: what is the function of the second parameter? in other queries that i use to fill datasets and so forth, i change the parameter and all is ok. i have changed the top level in the xml and it doesn't mind. this paramater has me confused.

cheers (as his father gives him a glass of champagne for xmas)

[Edneeis]

I haven't really messed with this part but the 2nd parameter is the tablename, which since it is giving you trouble sounds like it is stored in the schema and therefore must match. Is 'tbl_ClientMaster' the table name in the database?

[stingrae]

thanks for the prompt reply. don't you sleep?

yes, tbl_ClientMaster is the name of a table. In another query, I join tables together and just make up a name and it works ok, but in this case i'm using the crystal report (that you helped me create). i thought it was somewhere in the schema too, and am now trying to figure out how to make a report work of a joined query.

cheers,

[Edneeis]

Sleep? Whats that?

Each Element 'block' is a table in the schema and the schema can make a dataset. You can even convert it to a strongly typed dataset if you want and use that for filling. View the Schema in the IDE then right click and hit 'Generate Dataset'. Then you should be able to declare a strongly typed dataset from that. The dataset name is in the schema properties.

Try that maybe you'll have better luck.

[stingrae]

d'oh! i thought that i'd cracked it big time, but alas no luck.

looking at this fact sheet:

http://support.crystaldecisions.com/...s/c2010371.asp

you'd think it was the direct answer to my question. unfortunately though, it doesn't work.

can somebody else please try and let me know if works?