Problems When Connecting and Disconnecting to an Access Database in VB6!! HELP PLEASE!!

[Joner]

Hello,

I'm having trouble with a little programme of mine where I connect to
an access database. It seems to connect fine, and disconnect fine, but
then after it won't reconnect, I get the error "operation is not
allowed when object is open" so I take out the line of code:

BookDetails.Connection1.Open

and it comes up with the error "operation is not allowed when object
is closed"

I put my hands up to the fact that I'm not an expert at this (as you
may see in the code!!) so I could quite possibly be using the wrong
code!!! But this is becoming quite frustrating now coz I can't seem to
find any help anywhere!!!

The programme is for entering details of books into an Access database
by using a more user friendly GUI created with VB6!

The code below is from two forms one for entering the book details and
one for a main menu. The problem occurs quite often in the programme,
but if I can get some help with this problem then I can use what I've
learnt to correct the other problems!! The example I'm going to give
is for the fill forms button and delete records button.

When you load the programme you get the main menu where you click to
go to the books form then you click the fill forms button and type the
reference number and then the amount of records. This fills all the
refs so you don't have to type them in manually, this works fine! then
when you click the close button and go back to the main menu and click
the delete records button it deletes all the records (up to this point
all is well) then when you go back into the books form and click the
fill refs button again that's when you get the error I stated above!
What am I doing wrong???

The Code:

On frmBooks:

Private Sub cmdFillForms_Click()
On Error GoTo err
MsgBox "PLEASE NOTE: You MUST clear out all previous books to use
this function and the Ref. No. fields should be empty."

Dim CurrentRef, NumberRecords, NumberCount As Integer
NumberCount = 0
CurrentRef = InputBox("Please enter your first reference number.")
If CurrentRef = "" Then
MsgBox "Sorry the number you entered is invalid! Please try
again.", vbExclamation
Exit Sub
Else
NumberRecords = InputBox("Please enter the amount of reference
numbers to be added.")

frmPleaseWait.Show
For i = 0 To 20
DoEvents
Next i
Do While NumberCount <= NumberRecords - 1

BookDetails.rstblBooks.AddNew
txtRef.Text = CurrentRef
NumberCount = NumberCount + 1
CurrentRef = CurrentRef + 1
Loop
BookDetails.rstblBooks.UpdateBatch
Unload frmPleaseWait
BookDetails.rstblBooks.MoveFirst
End If
Exit Sub

err:
MsgBox err.Description
Unload frmPleaseWait
End Sub

On frmMainMenu:

Private Sub cmdClear_Click()
On Error GoTo err
Dim response
Dim rs As Recordset

response = MsgBox("WARNING! There is NO way to undo this action
please make sure that you are sure you want to clear your data!", 4 +
48, "WARNING!")
If response = vbNo Then
Exit Sub
End If

response = MsgBox("FINAL WARNING! You are about to delete all
data!! There is no way to undo this action! Are you sure?", 4 + 48,
"FINAL WARNING!")
If response = vbNo Then
Exit Sub
Else
frmPleaseWait.Show
For i = 0 To 20
DoEvents
Next i

BookDetails.comClear
BookDetails.Connection1.Close

For i = 0 To 20
DoEvents
Next i
'Now run DB compact routine
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\IB\IBook.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\IB\IBook1.mdb;Jet OLEDB:Engine Type=4"

Kill ("c:\IB\IBook.mdb")
Name "c:\IB\IBook1.mdb" As "c:\IB\IBook.mdb"
Set JRO = Nothing
Unload frmPleaseWait
MsgBox ("All data has been deleted!")
BookDetails.Connection1.Open
BookDetails.rstblBooks.Open

End If
Exit Sub
err:
Unload frmPleaseWait
MsgBox err.Description
MsgBox "PLEASE NOTE: YOUR DATABASE HAS NOT BEEN CLEARED!"
End Sub

Any help you can give would be greatly appreciated!!

Thanks,

Ben

[Armin Zingler]

"Joner" <be*********@hotmail.com> schrieb

I'm having trouble with a little programme of mine where I connect
to an access database. It seems to connect fine, and disconnect fine,
but then after it won't reconnect, I get the error "operation is
not allowed when object is open" so I take out the line of code:

This group (micrsoft.public.dotnet.languages.vb) is about VB.NET ("dotnet",
see group name). Older VB versions are handled at one of the
microsoft.public.vb.* groups.
--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

[Herfried K. Wagner [MVP]]

* be*********@hotmail.com (Joner) scripsit:
[...]

This is a VB.NET language group. You may want to turn to one of the
microsoft.public.vb.data.* groups.

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>

[Sparky]

Heh heh.

What seems to be happening is this:

In your original, you might not have been disconnecting properly
(Connection.Close), which meant that when you tried to reconnect you got the
"operation is not allowed when object is open" error. Then you took out the
"Connection.Open" method, and something else (I'm guessing a recordset
operation or similar) is complaining that the connection closed.

The long and the short of it is to put the "Connection1.Open" back in, and
find out why the disconnect doesn't work properly.

Hope this helps,

Sparky.
"Joner" <be*********@hotmail.com> wrote in message
news:43**************************@posting.google.c om...

Hello,

I'm having trouble with a little programme of mine where I connect to
an access database. It seems to connect fine, and disconnect fine, but
then after it won't reconnect, I get the error "operation is not
allowed when object is open" so I take out the line of code:

BookDetails.Connection1.Open

and it comes up with the error "operation is not allowed when object
is closed"

I put my hands up to the fact that I'm not an expert at this (as you
may see in the code!!) so I could quite possibly be using the wrong
code!!! But this is becoming quite frustrating now coz I can't seem to
find any help anywhere!!!

The programme is for entering details of books into an Access database
by using a more user friendly GUI created with VB6!

The code below is from two forms one for entering the book details and
one for a main menu. The problem occurs quite often in the programme,
but if I can get some help with this problem then I can use what I've
learnt to correct the other problems!! The example I'm going to give
is for the fill forms button and delete records button.

When you load the programme you get the main menu where you click to
go to the books form then you click the fill forms button and type the
reference number and then the amount of records. This fills all the
refs so you don't have to type them in manually, this works fine! then
when you click the close button and go back to the main menu and click
the delete records button it deletes all the records (up to this point
all is well) then when you go back into the books form and click the
fill refs button again that's when you get the error I stated above!
What am I doing wrong???

The Code:

On frmBooks:

Private Sub cmdFillForms_Click()
On Error GoTo err
MsgBox "PLEASE NOTE: You MUST clear out all previous books to use
this function and the Ref. No. fields should be empty."

Dim CurrentRef, NumberRecords, NumberCount As Integer
NumberCount = 0
CurrentRef = InputBox("Please enter your first reference number.")
If CurrentRef = "" Then
MsgBox "Sorry the number you entered is invalid! Please try
again.", vbExclamation
Exit Sub
Else
NumberRecords = InputBox("Please enter the amount of reference
numbers to be added.")

frmPleaseWait.Show
For i = 0 To 20
DoEvents
Next i
Do While NumberCount <= NumberRecords - 1

BookDetails.rstblBooks.AddNew
txtRef.Text = CurrentRef
NumberCount = NumberCount + 1
CurrentRef = CurrentRef + 1
Loop
BookDetails.rstblBooks.UpdateBatch
Unload frmPleaseWait
BookDetails.rstblBooks.MoveFirst
End If
Exit Sub

err:
MsgBox err.Description
Unload frmPleaseWait
End Sub

On frmMainMenu:

Private Sub cmdClear_Click()
On Error GoTo err
Dim response
Dim rs As Recordset

response = MsgBox("WARNING! There is NO way to undo this action
please make sure that you are sure you want to clear your data!", 4 +
48, "WARNING!")
If response = vbNo Then
Exit Sub
End If

response = MsgBox("FINAL WARNING! You are about to delete all
data!! There is no way to undo this action! Are you sure?", 4 + 48,
"FINAL WARNING!")
If response = vbNo Then
Exit Sub
Else
frmPleaseWait.Show
For i = 0 To 20
DoEvents
Next i

BookDetails.comClear
BookDetails.Connection1.Close

For i = 0 To 20
DoEvents
Next i
'Now run DB compact routine
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\IB\IBook.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\IB\IBook1.mdb;Jet OLEDB:Engine Type=4"

Kill ("c:\IB\IBook.mdb")
Name "c:\IB\IBook1.mdb" As "c:\IB\IBook.mdb"
Set JRO = Nothing
Unload frmPleaseWait
MsgBox ("All data has been deleted!")
BookDetails.Connection1.Open
BookDetails.rstblBooks.Open

End If
Exit Sub
err:
Unload frmPleaseWait
MsgBox err.Description
MsgBox "PLEASE NOTE: YOUR DATABASE HAS NOT BEEN CLEARED!"
End Sub

Any help you can give would be greatly appreciated!!

Thanks,

Ben

[Joner]

Thanks for the help Spark but i already knew that, the problem is cant
seem to get the bugger to dissconnect and the file is still open!!!

Can anybody eles help me!! :(

"Sparky" <ne**********@hotmail.com> wrote in message news:<bu**********@newsg3.svr.pol.co.uk>...

Heh heh.

What seems to be happening is this:

In your original, you might not have been disconnecting properly
(Connection.Close), which meant that when you tried to reconnect you got the
"operation is not allowed when object is open" error. Then you took out the
"Connection.Open" method, and something else (I'm guessing a recordset
operation or similar) is complaining that the connection closed.

The long and the short of it is to put the "Connection1.Open" back in, and
find out why the disconnect doesn't work properly.

Hope this helps,

Sparky.

[scorpion53061]

Why are you posting to a .NET group?

[Frank Adam]

On Thu, 15 Jan 2004 06:52:11 -0600, "scorpion53061" <Its the end of
the world as we know [e-mail]> wrote:

Why are you posting to a .NET group?

It's a payback for all the .Net questions we get here, on CLVBM. ;-)

--

Regards, Frank

[Sparky]

So you're saying that even calling Connection.Close doesn't close the
connection? Just a guess, but does setting the connection object to nothing
after disconnecting help at all?

Sparky.

"Joner" <be*********@hotmail.com> wrote in message
news:43**************************@posting.google.c om...

Thanks for the help Spark but i already knew that, the problem is cant
seem to get the bugger to dissconnect and the file is still open!!!

Can anybody eles help me!! :(

"Sparky" <ne**********@hotmail.com> wrote in message

news:<bu**********@newsg3.svr.pol.co.uk>...

Heh heh.

What seems to be happening is this:

In your original, you might not have been disconnecting properly
(Connection.Close), which meant that when you tried to reconnect you got the "operation is not allowed when object is open" error. Then you took out the "Connection.Open" method, and something else (I'm guessing a recordset
operation or similar) is complaining that the connection closed.

The long and the short of it is to put the "Connection1.Open" back in, and find out why the disconnect doesn't work properly.

Hope this helps,

Sparky.

[Joner]

Nah sorry mate didn't help I had tried that before but it didn't work
then either, i just tried it agin but not surprisingly...nope!!

This snip of code:

Kill ("C:\Program Files\IBook Outworker\ibook.mdb")
Name "C:\Program Files\IBook Outworker\ibook1.mdb" As
"C:\Program Files\IBook Outworker\ibook.mdb"
Set JRO = Nothing
Unload frmPleaseWait
MsgBox ("All data has been deleted!")
BookDetails.Connection1.Open
'BookDetails.rstblBooks.Open

if you run the code with the last line not commented out, then it
works the first time but when you run it again you get this error
(below)!! so many errors grrrr!!

Run-time error '3709':
the connection cannot be used to perform this operation. It is either
closed or invalid in this context

im starting to lose hope! any other ideas spark?

Thanks
Joner
"Sparky" <ne**********@hotmail.com> wrote in message news:<bu**********@news5.svr.pol.co.uk>...

So you're saying that even calling Connection.Close doesn't close the
connection? Just a guess, but does setting the connection object to nothing
after disconnecting help at all?

Sparky.

[Sparky]

After you call connection.close, does the lockfile (ldb) disappear, or is it
still there?

Sparky.

"Joner" <be*********@hotmail.com> wrote in message
news:43**************************@posting.google.c om...

Nah sorry mate didn't help I had tried that before but it didn't work
then either, i just tried it agin but not surprisingly...nope!!

This snip of code:

Kill ("C:\Program Files\IBook Outworker\ibook.mdb")
Name "C:\Program Files\IBook Outworker\ibook1.mdb" As
"C:\Program Files\IBook Outworker\ibook.mdb"
Set JRO = Nothing
Unload frmPleaseWait
MsgBox ("All data has been deleted!")
BookDetails.Connection1.Open
'BookDetails.rstblBooks.Open

if you run the code with the last line not commented out, then it
works the first time but when you run it again you get this error
(below)!! so many errors grrrr!!

Run-time error '3709':
the connection cannot be used to perform this operation. It is either
closed or invalid in this context

im starting to lose hope! any other ideas spark?

Thanks
Joner
"Sparky" <ne**********@hotmail.com> wrote in message

news:<bu**********@news5.svr.pol.co.uk>...

So you're saying that even calling Connection.Close doesn't close the
connection? Just a guess, but does setting the connection object to nothing after disconnecting help at all?

Sparky.

[Herfried K. Wagner [MVP]]

* "Sparky" <ne**********@hotmail.com> scripsit:

I don't see the original post, so I am replying here...

Kill ("C:\Program Files\IBook Outworker\ibook.mdb")
Put a 'Call' in front of the 'Kill' or get rid of the "(". ")"!
MsgBox ("All data has been deleted!")

Dito.

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>

[Joner]

Nah its still there buddy!!

"Sparky" <ne**********@hotmail.com> wrote in message news:<bu**********@news5.svr.pol.co.uk>...

After you call connection.close, does the lockfile (ldb) disappear, or is it
still there?

Sparky.

[Sparky]

So it's definately not disconnecting. I've tried it myself and can't seem
to replicate it. I take it this occurs when stepping through, and that the
conn.close definately gets called?

Check your version of MDAC, and update if necessary (I heard this was a free
download) and let me know...

Sparky.

"Joner" <be*********@hotmail.com> wrote in message
news:43**************************@posting.google.c om...

Nah its still there buddy!!

"Sparky" <ne**********@hotmail.com> wrote in message

news:<bu**********@news5.svr.pol.co.uk>...

After you call connection.close, does the lockfile (ldb) disappear, or is it still there?

Sparky.

[Joner]

Hey Buddy,

Sorry I haven't replied for a while but I've been quite busy with other things!

I updated my MDAC to 2.8 from 2.7 but still dont work!!

I have stepped through the code and conn.close definatly get called!

Im not having much luck with this am I!! :)

cheers,
Joner

"Sparky" <ne**********@hotmail.com> wrote in message news:<bv**********@news5.svr.pol.co.uk>...

So it's definately not disconnecting. I've tried it myself and can't seem
to replicate it. I take it this occurs when stepping through, and that the
conn.close definately gets called?

Check your version of MDAC, and update if necessary (I heard this was a free
download) and let me know...

Sparky.