[RESOLVED] Data Environment connection problem

[Capp]

Hey guys,
I am using the DataEnvironment and DataReport to show a simple little printout.

If I use the database connection wizard for the DataEnvironment, it connects just fine. But the problem is that when run from another system, the file path is different.

I tried doing App.path method, but that didnt' work so I figured I would have to code the connection.

This is the same bit of code you guys helped me with last week. Here is what I've got.

I have a DataEnvironment called dePrint
Inside that, I have a DEConnection called con1
Inside that, I have a DECommand called Print

My DataReport is called datPrint
- The DataMember property is set to Print
- The DataSource Property is set to dePrint

Private Sub cmdPrint_Click()
 
    Dim strDate As String
    Dim strSQL As String
 
'   Make sure the connection isn't left open from last printing
 
    If dePrint.rsPrint.State = adStateOpen Then
        dePrint.rsPrint.Close
    End If
 
'   If I leave out these next 4 lines it works fine
 
    dePrint.con1.Provider = "Microsoft.Jet.OLEDB.4.0"
    dePrint.con1.CursorLocation = adUseClient
    dePrint.con1.Properties("Data Source") = App.Path & "\EmpLic.mdb"
    dePrint.con1.Open
    
'   Get date information. This is what you guys helped me with and it works fine.
 
    strDate = "#" & Format(Now, "mm/dd/yyyy") & "#"
    strSQL = "SELECT * FROM Licenses WHERE DateDiff(" _
       & Chr(34) & "d" & Chr(34) _
       & ", " & strDate & ", [LicenseExpires]) <= 30 ORDER BY EmpName"
 
    
'   Open the report with the selected info. Works fine if left to default.    
 
    dePrint.rsPrint.Open strSQL
    datPrint.WindowState = vbMaximized
    datPrint.Show
    
    
End Sub

Under the DataEnvironment, I have the connection (con1). If I use the built-in wizard to select the database, it works fine, but I need it to be App.Path so it can work on any system.

Any ideas where I screwed up or what I'm forgetting?

Thanks

[Capp]

Interesting update:

If I comment out the first few lines to make sure the connection is closed:

If dePrint.rsPrint.State = adStateOpen Then
        dePrint.rsPrint.Close
    End If

It works just fine, but as soon as I close out the report and click print again, I get the error telling me it can't perform this while the connection is open.

If I add .close to the end of the Sub, it errors out completely.

What a pain

[Capp]

I think I figured it out. It seems to be working.

Can someone double-check my code to make sure I didn't miss anything? It works fine on my pc, but I want to make sure it is going to jive on another system.

Private Sub cmdPrint_Click()
 
    If dePrint.rsPrint.State = adStateOpen Then
        dePrint.rsPrint.Close
    End If
 
    Dim strDate As String
    Dim strSQL As String
    
    Call MainCon      'open connection to database
    Set RS = New ADODB.Recordset
 
    strDate = "#" & Format(Now, "mm/dd/yyyy") & "#"
    strSQL = "SELECT * FROM Licenses WHERE DateDiff(" _
       & Chr(34) & "d" & Chr(34) _
       & ", " & strDate & ", [LicenseExpires]) <= 30 ORDER BY EmpName"
       
    RS.Open strSQL, Con, adOpenStatic, adLockOptimistic
    
    dePrint.rsPrint.Open RS
    datPrint.WindowState = vbMaximized
    datPrint.Show
    
    RS.Close          'close recordset
    Con.Close        'close connection
 
End Sub
 
 
 
Public Sub MainCon()
 
    Set Con = New ADODB.Connection
    Con.CursorLocation = adUseClient
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Properties("Data Source") = App.Path & "\EmpLic.mdb"
    Con.Open
 
End Sub

[Capp]

That didn't fix it.

Once I compile it and run it on a different machine, when I click the Print button, it still keeps trying to access the database from the original location.

Here is the message box that appears. If I manually enter the path, it works for as long as the program is still running. If I shut it down and relaunch it, it goes back to this.

Any ideas, based on the code above?

Thanks

[leinad31]

The GUI wrapper of ADO is supposed to make things easy... if your going out of your way to make it work then I suggest you drop the point-and-click GUI and go straight to ADO code. Below has sample on how to show data report without using the data environment.

http://www.vbforums.com/showthread.p...ataenvironment

Do additional search for sample code on using/setting up the ADO connection object... you will only have to change the path info in the connection string before you open the connection.

[Capp]

Thank you for your help.

That fixed it. I combined what you gave me with what I already had and got it to work without using a DataEnvironment.

Kudos and thanks again