Data report

[n1a2v3i3n]

Hi,
I would like to close the database connection when i close the data report. I am using the following to call the data report to show the report.
Private Sub Command5_Click()
Dim st As String
Dim good As String
Dim a As Date, b As Date
good = Chr(35)
a = sdt1
b = edt2

str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Vechdetail.mdb;Persist Security Info=False"
st = " select * from service where dat between " & good & sdt1 & good & "and" & good & edt2 & good & ""

Adodc1.Open st, str, adOpenDynamic, adLockOptimistic

If Adodc1.EOF = True Or Adodc1.BOF = True Then
MsgBox "No Records"
Exit Sub
Else
Do While Not Adodc1.EOF
Adodc1.MoveNext
Loop
End If


Set expdr.DataSource = Adodc1

expdr.Show

End Sub

This code runs for the first time but when i want to run the same for the second time i get and error "Operation is not allowed when the object is open".

Any one could tell me a possible solution for this.
Thanks

 

[Andrzejek]

How did you declare Adodc1?
Is that the data control? ADO RecordSet?

If ADO recordset, I would be tempted to try:

Set expdr.DataSource = Adodc1
[blue]Adodc1.Close[/blue]
expdr.Show

End Sub

What is that supposed to do?
Just going thru the records, but why?

Do While Not Adodc1.EOF
    Adodc1.MoveNext
Loop

Have fun.

---- Andy

 

[n1a2v3i3n]

Adodc1 is declared as adodb recordset.
i have tried on using
Set expdr.DataSource = Adodc1
Adodc1.Close
expdr.Show

End Sub

but still i get the error as "Operation is not allowed when the object is closed"
Thanks

 

[SBerthold]

Adodc1.ActiveConnection.Close

 

[SBerthold]

and to leave it open and not get the error you could also use:

Dim bOpenConn as boolean

bOpenConn =(Adodc1.ActiveConnection is Nothing)
If (not bOpenConn ) then bOpenConn=(Adodc1.ActiveConnection.State = adStateClosed )

If bOpenConn Then
str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Vechdetail.mdb;Persist Security Info=False"
st = " select * from service where dat between " & good & sdt1 & good & "and" & good & edt2 & good & ""

Adodc1.Open st, str, adOpenDynamic, adLockOptimistic
End If

 

[SBerthold]

I'm sorry, I mis-typed.

Replace everywhere in my previous two posts:

Adodc1.ActiveConnection

With

Adodc1.Recordset.ActiveConnection

 

[SBerthold]

Oh no...it's late Friday afternoon

Forget what I posted. I read too fast.


n1a2v3i3n, you cannot close the recordset connection when showing the Report unless you use a local cursor. See if this works:

Adodc1.CursorLocation=adUseClient
Adodc1.Open st, str, adOpenStatic, adLockOptimistic

Set expdr.DataSource = Adodc1
expdr.Show

Set Adodc1.ActiveConnection = Nothing