Password CR9 Access.mdb

[VB4fun]

Has ANYONE had any luck opening a Crystal Report through VB6, which is based on an Access.mdb, that is password protected?

I am getting an error (CR type error):
Run-time error '-2147189176(80047e48
Logon failed.
Details: DAO Error Code: Oxbd7
Source: DAO Workspace
Description: Not a valid password.

strConnectS = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\DB\MyDb.mdb;" & _
        "Persist Security Info=True;" & _
        "Jet OLEDB:Database Password=MyPassword"
    With CnnStatusS
        .CursorLocation = adUseClient
        .Open strConnectS
    End With
    Set rsS = New ADODB.Recordset
    Set objApp = New CRAXDRT.Application
    Set objReport = objApp.OpenReport(App.Path & "\Db\" & RptSelect & ".rpt")
       With objReport
            Set objExportOptions = .ExportOptions
        With objExportOptions
            .DestinationType = crEDTDiskFile
            .DiskFileName = App.Path & "\Temp\Report.pdf"
            .FormatType = crEFTPortableDocFormat
            .PDFExportAllPages = True
        End With
        
        .ReadRecords

the error occurs on the .ReadRecords

yes, I have checked the password.

[ARPRINCE]

Why not try to check if your connection is actually working since the problem relates to LOGIN.

With CnnStatusS
.CursorLocation = adUseClient
.Open strConnectS
End With
'CHECK IF YOUR CONNECTION IS ACTUALLY GOOD (1=CONNECTED, 0=FAILED)
msgbox CnnStatusS.State

[VB4fun]

Thanks for your reply.

I am getting a 1..which is connected.

However, when I changed the drive location of the program (and associated files)....it is continueing to look at the OLD location.

shouldnt this be being set, with the :

Set objReport = objApp.OpenReport(App.Path & "\Db\" & RptSelect & ".rpt")
       With objReport

??

[ARPRINCE]

I believe that the "app.path" is directly relative to where your vb program executable (exe) file is located. So even if you move the access table from:

from: 
c:\program\Db\table.mdb
to
d:\program\Db\table.mdb

and you are running the exe under c:\program, it will still take the table.mdb at c:

Also, you have to look at two paths here, 1 is your connection string and 2 is for your CR.

****

BTW, I dont use access but I may in the future so I decided to investigate a little further on the problem you have.

I was able to simulate your LOGIN error by creating a simple access 2000 table with a password. I tried creating a connection string using JET but was not suucessful. I always got that login error even if the password was correct.

However, I created a MS Access DSN and used ODBC to connect to the table and it worked just fine.

.ConnectionString = "DSN=DAO_TEST;DBQ=C:\TEST.mdb;UID=admin;Password=123456"

[VB4fun]

thanks for the thoughts....

the scenario....with app.path
I am building on a zip disk, I have two drives attached. yesterday it was in one drive...today I placed it in the other. So program, db, report are all working of the same disk..which is in a different drive.

However, I created a MS Access DSN and used ODBC to connect to the table and it worked just fine.

I would have to have Access installed for this to work, wouldnt I ? or have the workgroups book? Which I am trying to stay away from....

I would like to just attach via JET.

[ARPRINCE]

I would have to have Access installed for this to work, wouldnt I ? or have the workgroups book? Which I am trying to stay away from....

You don't need the access program for this. You just need the table. You also need to create a DSN using an MS-Access ODBC driver then build an ODBC connection string using the DSN instead of JET.

[VB4fun]

not sure where to even begin.....

can you push start me....?

[ARPRINCE]

I'm using WIN2K so the steps may differ a little depending on the OS you are using. Also, this is how I test
my connection string so it may be a little awkward and long for you but it works for me just fine.

(1) Create an MS-Access DSN.
- Start
- Settings
- Control Panel
- Administrative Tools
- Data Sources (ODBC)
- Click on the <SYSTEM DSN> tab
- Click on <ADD>
- Pick <Microsoft Access Driver (*.mdb)> my version is 4.00.6200.00
- Click on <SELECT> and locate your access database.
- Click on <OK>
- Create a name for this DSN (ex. TESTMDB) to be used later
- Click on <OK>
- Click on <OK> again.

(2.A) Create a VB dummy project.

- Open VB with a new project.
- With Form1, right click on the toolbox and click on <COMPONENTS>
- Under the <CONTROLS> tab, add MICROSOFT ADO DATA CONTROL 6.0 (OLEDB)
- Also add MICROSOFT DATAGRID CONTROL 6.0 (OLEDB)
- Click on <OK>
- Place an <ADODC1> control and a <DataGrid1> control on FORM1
- bind the <DataGrid1> to <Adodc1>.


(2.B) Using ADODC1, create an ODBC connections string
- Click on <Adodc1>. Right Click and choose <ADODC Properties>.
- Choose <USE CONNECTION STRING>
- Under the connection string, Click on <BUILD>
- Choose <Microsoft OLEDB PROVIDER for ODBC Drivers>
- Click on <NEXT>
- Choose <USE CONNECTION STRING>
- Click on <BUILD>
- Click on the <MACHINE DATA SOURCE>
- Locate and pick the DSN you created under (1) which is <TESTMDB>
- Click on <OK>
- Type the DB login name <Admin>
- Type the DB password <123456>
- Click on <DATABASE>
- locate the your DB the and click <OK>.
- Click on <OK> again

You should see a connection string. Copy and save this since you can use this later for you VB

project.

DSN=TESTMDB;DBQ=C:\test.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;
PageTimeout=5;PWD=123456;UID=admin;

- Enter User name again <Admin>
- Enter Password again <123456>
- Place a check on <ALLOW SAVING PASSWORD>
- Under the <initial catalog to use>, us the drop down to pick you <DATABASE>
- Test you connection.
- If successful, click on <OK>

You should be back under the "PROPERTIES PAGE".

- Click on the <RECORD SOURCE> tab
- under the <COMMAND TYPE>, pick #2 - adcmdtable.
- Under the <TABLE OR STORED PROCEDURE NAME>, pick the table you want to view.
- Click on <OK>
- Run your project (you should be able to view the table in the grid)!!!


You basically just used the project to build and test the connection string that you can use later. You can further trim down the connection string if you want to:

FROM:

DSN=TESTMDB;DBQ=C:\test.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;
PageTimeout=5;PWD=123456;UID=admin;

TO:
DSN=TESTMDB;DBQ=C:\test.mdb;PWD=123456;UID=admin;

Hope this helps!!

[VB4fun]

Dude! You Rock!

Wont be able to work on this project until later tonite, probably. But thanks for taking the time to make such a complete set of instructions!

I should be able to follow this step by step, and I will let you know how it goes.

Thanks for opening my eyes to a new way of doing things. I can always use new advice!

Thanks again!

[VB4fun]

ARPRINCE, I bow to the master !

I used your step by step, and it worked great.

I used your information from
http://www.vbforums.com/showthread.p...hreadid=235961

and it worked without a hitch!!!

I have to admit, I am beginning to regain my faith in ol' CR !!!

This is great for working with recordsets.

Question on the DSN (YES, I am a noob on this one)

Do I have to deploy anything with my app, to get this to work?
(again, user machine may not have Access...)

definately worth 5 vbf awards for the knowledge passing on this one!

[VB4fun]

ok...getting to the end here....
taking what I learned, with what I knew....
to come up with a report that is exported to a PDF.

I am getting a runtime error
Logon failed
Details: DAO Error Code: 0xbe3
Source: DAO.Workspace
Description: Disk or network error

I have gone over this code and it is not jumping out at me..

Dim Cap1 As String
   Dim CNSTR As String
   Dim RptSelect As String
   Dim srcSQL As String
    
   CNSTR = "DSN=MYString;DBQ=C:\VBPrograms\Db\MyDb.mdb;DefaultDir=C:\VBPrograms\Db;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=MyPassword;UID=admin;"
 
    Set adoCN = New ADODB.Connection
    Set ADOrs = New ADODB.Recordset
 
    ReportName = "Weekly.rpt"
    
    Set objApp = New CRAXDRT.Application
    
    With adoCN
        .ConnectionString = CNSTR
        .CursorLocation = adUseClient
        .Open
    End With
 
    RptSelect = cboReport.text
    srcSQL = "SELECT * FROM [tblInput]  WHERE [Invoice] = '" & RptSelect & "' ORDER BY [DATE]"
 
    With ADOrs
        .ActiveConnection = adoCN
        .CursorType = adOpenDynamic
        .Open srcSQL
    End With
    
    Set objReport = objApp.OpenReport(App.Path & "\Db\" & ReportName)
 
    'With objReport.Database.Tables(1).ConnectionProperties
        '.Item("Provider") = "XXXX" 
     '   .Item("Data source") = "XXXX"
        '.Item("Initial Catalog") = "XXXX"
      '  .Item("User ID") = "XXXX"
       ' .Item("Password") = "XXXX"
    'End With
 
        With objReport
        Set objExportOptions = .ExportOptions
        With objExportOptions
            .DestinationType = crEDTDiskFile
            .DiskFileName = App.Path & "\Temp\Report.pdf"
            .FormatType = crEFTPortableDocFormat
            .PDFExportAllPages = True
        End With
        
        .ReadRecords
        .DisplayProgressDialog = False
        .Export (False)
    
    End With
    
    
    GItems = ""
    Cap1 = ""
    frmPdf.Visible = True
    PCaption = "Reports"
    PFile = "Report"
    PDir = "Temp"
    Call PDFView
    
    MousePointer = vbNormal
    
    Set objTable = Nothing
    Set objExportOptions = Nothing
    Set objReport = Nothing
    Set objApp = Nothing
    
    
 Exit Sub

I commented out the Database.Tables because I am unsure of the exact syntax. Each time I try something in there I get messages telling me it doesnt like the value.

The error is arriving at the .ReadRecords line.

Can you help on this ???

[ARPRINCE]

Do I have to deploy anything with my app, to get this to work?

Not really as long as the DSN is configured on the client PCs.

I believe that you should be able include the DSN configuration on your deploy package but
I have no idea how to do that


Do you have records in your RS?

With ADOrs
        .ActiveConnection = adoCN
        .CursorType = adOpenDynamic
        .Open srcSQL
    End With
 'TEST IF YOU HAVE RECORDS
    msgbox adors.recordcount

if yes, try this:

Set objReport = objApp.OpenReport(App.Path & "\Db\" & ReportName)
 
   objReport.database.setdatasource adors, 3, 1
 
   With objReport.ExportOptions
            .DestinationType = crEDTDiskFile
            .DiskFileName = App.Path & "\Temp\Report.pdf"
            .FormatType = crEFTPortableDocFormat
            .PDFExportAllPages = True
   End With
   objReport.Export (False)

[VB4fun]

worked like a charm! THANKS!

Not really as long as the DSN is configured on the client PCs.

I believe that you should be able include the DSN configuration on your deploy package

looks like I have some research to do...

this will work on this application, because I can create it on the end machine....however other apps this wont be possible.

Thanks for the assistance! I will let you know what I find out.

[VB4fun]

another question...(dont mean to be a pain)

I have a second report that I have created, based on two tables. which seems to be printing fine...

however when I run it through the same print routine, the only thing that changes is a variable for the report name....

it is leaving the .mdb open....

the first one closes the .mdb as soon as the pdf is created...but the second report seems to print AND leave the db open.

I am closing the ADOrs and the ADOcn....and setting them = nothing.

Why would this work for one..and not the other..??

[ARPRINCE]

What specific error do you get?
Are you using the same code?
How do you run the report one after the other (i.e do you have a menuy report or user types in the name of the report)?

[VB4fun]

thanks for the reply.
not getting an error code at all. everything seems to be printing fine.

The user has a form, with 3 option buttons on it. they select an option button, which has the report name as the .tag. This .tag is then used in a variable to select the report. (Printing one at a time)..but all using the same print routine as posted earlier.

While running the routine, reports #1 and #3, which are based on one table only print through the crystal report routine fine and as soon as the .PDF is created, (while watching through Windows Explorer), the .ldb (associated to the .mdb) is released.

However, when I run the #2 report, which I have based on two tables, it prints fine....however, the .ldb stays there (and if I try to delete it, it windows tells me there is someone using it). Even if I reboot the computer, the .ldb is still there.

Do I have to do something different with the connection, etc if my report is based on two tables...??

[ARPRINCE]

Running report#1, you load the CR form. Do you unload this before calling the next report? If not, try unloading the CR form and loading it only when you start calling the next report.

[VB4fun]

no luck.

even if I start the program (in IDE) and run the report (#2) first and then unload all forms...it still shows the database open.

I am lost, here. I went to CR to change the database location to a different database (for grins)...and now when I try to run the routine...it is opening both of the databases..the old one and the new one....

since I am doing this in the code (the DSN you provided) why would it matter which one was set at design time...and why would it still be trying to access it..??

[VB4fun]

still no luck...

I tried deploying to the final machine, to see if by chance the locking problem would go away...however....now I can't get the reports to work...

Not really as long as the DSN is configured on the client PCs.

I have tried step by step setting up the DSN and no luck...it keeps telling me the Driver (unknown) is not a valid path.

The final machine is a W98..and I am developing on XP...is that going to make a difference with this..?

EVERYTHING else is working fine with this program....except the reporting...

[ARPRINCE]

Originally posted by VB4fun
no luck.

even if I start the program (in IDE) and run the report (#2) first and then unload all forms...it still shows the database open.

I am lost, here. I went to CR to change the database location to a different database (for grins)...and now when I try to run the routine...it is opening both of the databases..the old one and the new one....

since I am doing this in the code (the DSN you provided) why would it matter which one was set at design time...and why would it still be trying to access it..??

There are some issues with CR9 when you re-direct reports to another DB. You may neet to update your CR with the "HOTFIXES".

This might help you: GO HERE!!

[ARPRINCE]

Originally posted by VB4fun
still no luck...
The final machine is a W98..and I am developing on XP...is that going to make a difference with this..?

CR9 Supports WIN98 Second Edition only.