473,224 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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

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
Nov 20 '05 #1
13 4279
"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

Nov 20 '05 #2
* 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>
Nov 20 '05 #3
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

Nov 20 '05 #4
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.

Nov 20 '05 #5
Why are you posting to a .NET group?
Nov 20 '05 #6
On Thu, 15 Jan 2004 06:52:11 -0600, "scorpion53061" <Its the end of
the world as we know it@here.com> 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
Nov 20 '05 #7
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.

Nov 20 '05 #8
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.

Nov 20 '05 #9
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.

Nov 20 '05 #10
* "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>
Nov 20 '05 #11
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.

Nov 20 '05 #12
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.

Nov 20 '05 #13
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.

Nov 20 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sérgio Almeida | last post by:
Greetings I'm having problems adding records to an access database. What I want to do is very simple. Here is my code. __CODE_START__ dim sqlStatement sqlStatement="insert into...
0
by: Asim | last post by:
I seem to be having a lot of problems with running/developing web site on my XP Professional OS. I have a simple web site (using ASP/VB), which retrieves information from an Access 2000...
1
by: andrew.newell | last post by:
Hi, I use MS Access to connect to and SQL Database this has been working fine until recently. Whenever I add a new record (post record 723) it does not allow me to change it. My DBA has...
1
by: Tom Wild | last post by:
Hi I am trying to create a webform that connects to an Access database. If I use the connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Gizmo\Gizmo.mdb" Then the application...
9
by: Nathan Sokalski | last post by:
I am trying to connect to a Microsoft Access Database from my ASP.NET Application. I use the following code to create my connection string: cmdSelect.Connection = New...
11
by: CM Manager via DotNetMonster.com | last post by:
I am very frustrated due to this exception error I am receiving. I've tried searching numerous user support groups, Microsoft Support Net, Google, etc. and haven't found exactly my situation....
0
by: Rex | last post by:
Hi I am getting this error message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user." when I am trying to connect to access...
3
by: firozfasilan | last post by:
pCnString = "Data Source=" & App.Path & "\database\WO.mdb;Provider=Microsoft.jet.oledb.4.0; uID=sa;Pwd=subhanalla" i am used the above code for providing a password to access database when i am...
0
by: pawera | last post by:
Hi Everybody ! I`ve got a problem with developing application which is to be run on PDA (MIO A701 with WIndows Mobile 5). I`m trying to create application in Visual Basic NET using NET Compact...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.