[RESOLVED] Data Environment connection problem

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

VB Code:

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

Interesting update:

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

VB Code:

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

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.

VB Code:

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

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

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.

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 :)