Hello,
Im trying to connect an access file to my application and im recieving the following run time error... "The connection cannot be used to perform this operation. It is either closed or invalid in this context"
My application basically consists of a form titled Customers, a module and MS Access database file called CoastandCountry
Here is my code for both the form and module, im wondering what the problem is..
frmCustomers
Option Explicit
Private mrsCustomers As New ADODB.Recordset
Private mcocontrol As Control
Private mstmode As String
Private mstcustomerno As String
Private Sub Form_Load()
Dim pstCustomersSQL As String
frmcustomers.WindowState = 2
Call SetInactiveTextBoxes
pstCustomersSQL = "SELECT * FROM tblCustomers ORDER BY tblcustomerno"
mrsCustomers.Open pstCustomersSQL, gcnCoastandCountry, adOpenStatic, adLockOptimistic, adCmdText
If mrsCustomers.RecordCount > 0 Then
Call DisplayData
Else
'Call ClearData
End If
End Sub
Heres the code for the Module
Option Explicit
Public gcnCoastandCountry As New ADODB.Connection
Sub Main()
If Left(App.Path, 2) <> "\\" Then
ChDrive App.Path
End If
ChDir App.Path
gcnCoastandCountry.Open "Provider = Microsoft.jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
frmcustomers.Show 1
End Sub
Sub CloseConnection()
gcnCoastandCountry.Close
Set gcnCoastandCountry = Nothing
End Sub
Thanks,
Spiz
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
What's the exact error? From what i know, the error comes from 2 possibilities:
1. the connection to database failed
2. the statement syntax is error.
I'm more concern on the 1st one, if 1st one is ok, before the line of your Open statement, output the pstCustomersSQL like:
debug.print pstCustomersSQL
exit sub '<< quit for debug purpose
mrsCustomers.Open pstCustomersSQL, gcnCoastandCountry, adOpenStatic, adLockOptimistic, adCmdText
then check the SQL statement syntax, even you can copy it and paste it into the Access Query Builder to verify the statement.
regards
Hi.
I added the following code
debug.print pstCustomersSQL
exit sub '<< quit for debug purpose
mrsCustomers.Open pstCustomersSQL, gcnCoastandCountry, adOpenStatic, adLockOptimistic, adCmdText
It enables access to the Customer Form... though does not show any of the records... and when I try to click on any of the command buttons such as "next record" or "previous record" the run time error "3407" comes on screen "Operation is not allowed when the object is closed" ... does that mean that the connection to database failed or that its a syntax error?
Spiz
>>Operation is not allowed when the object is closed
Mean the connection to the database is closed, try check the state of gcnCoastandCountry.
like as posted above:
if gcnCoastandCountry.state = adStateClosed then
msgbox "Connection valid"
elseif gcnCoastandCountry.state = adStateClosed then
msgbox "Connection failed"
else
msgbox "something wrong here.."
end if
I Added that statement before yet nothing happens when i try to execute the application... the same errors come up
This is what my Module currently looks like
Option Explicit
'**Declare a New ADODB connection object variable
Public gcnCoastandCountry As New ADODB.Connection
Sub Main()
If Left(App.Path, 2) <> "\\" Then
ChDrive App.Path
End If
ChDir App.Path
'**Open the connection to the database
gcnLibrary.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
If gcnCoastandCountry.State = adStateClosed Then
MsgBox "Connection valid"
ElseIf gcnCoastandCountry.State = adStateClosed Then
MsgBox "Connection failed"
Else
MsgBox "something wrong here.."
End If
End Sub
Sub CloseConnection()
gcnCoastandCountry.Close
Set gcnCoastandCountry = Nothing
End Sub
Another thing... if i upload the Application on a webserver you can check it out it will make it easier for you, and if you fix it... ill give you the full 500 points :)
here is the site
http://www.geocities.com/i
>>gcnLibrary.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
??
is gcnCoastandCountry or gcnLibrary? is both Connection Object?
try:
..
gcnCoastandCountry.Connect
gcnCoastandCountry.Open
If gcnCoastandCountry.State = adStateClosed Then
MsgBox "Connection valid"
ElseIf gcnCoastandCountry.State = adStateClosed Then
MsgBox "Connection failed"
Else
MsgBox "something wrong here.."
End If
..
acperkins: the Main form always was my start up object.
my program consists of a Main Form... which is meant to take the user to the Customer Form...
ryancys: i tryed that with the correction... yet still does not work
if u can check out the following application and get it to work, paste the correct code on the forums ill award 600 :)
http://www.geocities.com/i
thanks,
Spiz
Im sorry if you have had difficulty loading the files, the application is available to download in the following site.
http://www.geocities.com/i
Do what as what acperkins suggested. Go to menu Project > ProjectName Properties, at the General Tab's Startup Object, select Sub Main, so that your code in Sub Main is First to be executed.
I also found some inconsistancy in your application, like in frmCustomers.'s Form Load Event:
Private Sub Form_Load()
Dim pstCustomersSQL As String
frmcustomers.WindowState = 2
Call SetInactiveTextBoxes
'pstCustomersSQL = "SELECT * FROM tblCustomers ORDER BY tblcustomerno"
pstCustomersSQL = "SELECT * FROM tblCustomers ORDER BY CustomerNumber"
mrsCustomers.Open pstCustomersSQL, gcnCoastandCountry, adOpenStatic, adLockOptimistic, adCmdText
If mrsCustomers.RecordCount > 0 Then
Call DisplayData
Else
'Call ClearData
End If
End Sub
tblcustomerno is Not a valid field in the table tblCustomers, wherei found to the most similar field is CustomerNumber.
As well as at DisplayData Sub-routine in the same form:
Sub DisplayData()
'txtcustomerno.Text = mrsCustomers!customerno
txtcustomerno.Text = mrsCustomers!CustomerNumbe
'txtlastname.Text = Format(mrsCustomers!lastna
txtlastname.Text = Format(mrsCustomers!Surnam
txtfirstname.Text = Format(mrsCustomers!firstn
txtstreet.Text = Format(mrsCustomers!street
txtsuburb.Text = Format(mrsCustomers!suburb
txtstate.Text = Format(mrsCustomers!State,
'txtpostcode.Text = mrsCustomers!postcode
txtcode.Text = mrsCustomers!postcode
txtphone.Text = mrsCustomers!phone
txtemail.Text = Format(mrsCustomers!email,
End Sub
* customerno is Not a valid field in the table.
* txtpostcode TextBox is Not Found, i found txtcode instead..
>>Format(mrsCustomers!last
You can write it like : "" & mrsCustomers!lastname instead.
Business Accounts
Answer for Membership
by: ryancysPosted on 2004-04-23 at 10:02:16ID: 10901742
in your module:
try check whether the connection to db is established or not:
..
gcnCoastandCountry.Open "Provider = Microsoft.jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
if gcnCoastandCountry.state = adStateClosed then
msgbox "Connection valid"
elseif gcnCoastandCountry.state = adStateClosed then
msgbox "Connection failed"
else
msgbox "something wrong here.."
end if
..
For the connectionString, you may put a Full path too.. like:
change:
gcnCoastandCountry.Open "Provider = Microsoft.jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
to:
gcnCoastandCountry.Open "Provider=Microsoft.Jet.OL
ConnectionString references:
http://www.able-consulting
http://www.connectionstrin
Hope this helps