Big Time Help Needed...

[redfingers]

Trying to get a vb app to spit out an XLS file using crystal reports.
Works fine when I run it, but my user account has access to all tables and databases.

So I 've created a new user that has same rights as I do and I need to pass these parameters to the Crystal Report.
Below is how I am doing it and I am getting a '
Failed to Open the connection' error.

Any ideas as to what I can do?
Thanks in advance.

Public Sub Export2XLS()
 
Dim sDestinationPath As String
sDestinationPath = destination path
 
'Open the report
Set CrystalReport = CrystalApp.OpenReport(rptpath)
 
 
With CrystalReport.Database
.LogOnServer "PDSODBC.DLL", "SCOPACESQLP1", "crystal", "VBapp_2006", "PACE_VBpw1"
End With
 
Set CrystalExportOptions = CrystalReport.ExportOptions
DoEvents
CrystalExportOptions.ExcelMaintainColumnAlignment = True
DoEvents
CrystalExportOptions.ExcelConstantColumnWidth = 500
DoEvents
CrystalExportOptions.FormatType = crEFTExcel80
DoEvents
CrystalExportOptions.ExcelPageBreaks = False
DoEvents
CrystalExportOptions.DestinationType = crEDTDiskFile
DoEvents
CrystalExportOptions.DiskFileName = sDestinationPath
'Export the report without prompting the user
DoEvents
CrystalReport.Export False
End Sub

[Jacob Roman]

First of all, you are using DoEvents waaaaay too many times. DoEvents is one of the slowest executing functions in VB, and should be used once in your case, or if used in an infinite loop, once per frame. Also, in VBForums, try putting your code in [vbcode] [/vbcode] tags so it looks more readable like so:

Public Sub Export2XLS()
 
     Dim sDestinationPath As String
 
     sDestinationPath = destination path
 
     'Open the report
     Set CrystalReport = CrystalApp.OpenReport(rptpath)
 
 
     With CrystalReport.Database
 
         .LogOnServer "PDSODBC.DLL", "SCOPACESQLP1", "crystal", "VBapp_2006",  "PACE_VBpw1"
 
    End With
 
    Set CrystalExportOptions = CrystalReport.ExportOptions
 
        DoEvents
 
        CrystalExportOptions.ExcelMaintainColumnAlignment = True
 
        DoEvents
 
       CrystalExportOptions.ExcelConstantColumnWidth = 500
 
       DoEvents
 
       CrystalExportOptions.FormatType = crEFTExcel80
 
       DoEvents
 
       CrystalExportOptions.ExcelPageBreaks = False
 
    DoEvents
 
    CrystalExportOptions.DestinationType = crEDTDiskFile
 
    DoEvents
 
    CrystalExportOptions.DiskFileName = sDestinationPath
 
    'Export the report without prompting the user
    DoEvents
 
    CrystalReport.Export False
 
End Sub

[RobDog888]

Also add some error handling code to identify the exact issue.

Moved

[redfingers]

I added an error.description and it read: 'Failed to open the connection'

Which I know is the problem, but when I open the crystal report and type in the exact same info that I am passing through via VB, the report connects, opens, and looks beautiful.

Some I'm wondering if there is something I am missing in the connection string in VB (Which I've posted) or is there some trick in Crystal that I might not know about that needs to be done?

Thanks again. And sorry about the ugly VB code posted in the Forum. I just did a cut and paste and didn't realize it looked so ugly.

[RobDog888]

Looks like your using an ODBCX connection on your system but do you also have it created on the other system?

[redfingers]

Could you explain what you mean by that?
I've created VB apps before that can export files to xls format using the crystal, but this is the first time I've had to pass a connection string to the crystal and I'm afraid I'm starting to get totally lost here.

Could you please explain it to me like I was 5 years old?

Thanks.

[RobDog888]

Ok, in CR when you were creating your report, how did you create your reports datasource?

[redfingers]

ODBC - Enter Connection String (driver=sql server;server=servername;database=databasename

Then, next screen it asks for user id and password. As well as if trusted connection, which I reply no.

which part am I doing wrong?

[RobDog888]

Then you need to create that same ODBC connection on the other system(s). Its part of the pain of using ODBC.