Solved

run time error 3709 database connection problem

Posted on 2004-04-23
14
42,312 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:igotnosmoke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 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.OLEDB.4.0;Data Source=" & Replace(App.Path & "\CoastandCountry.mdb","\\,"\")

ConnectionString references:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet
http://www.connectionstrings.com

Hope this helps
0
 

Author Comment

by:igotnosmoke
ID: 10906297
Thanks for your help, but no go... im increasing the Point Value to 300

Another thing... when the run time error comes up, the following code is highlighted in the Customer form when i debug.

mrsCustomers.Open pstCustomersSQL, gcnCoastandCountry, adOpenStatic, adLockOptimistic, adCmdText

Thanks,
Spiz
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 10906363
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:igotnosmoke
ID: 10906429
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
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 10906438
>>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

0
 

Author Comment

by:igotnosmoke
ID: 10906483
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_got_no_smoke/assignmentsysimp.zip
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10908012
The problem is that you are never opening your connection.  You need to make Main your Startup Object (Project | projectname Properties)

Also (and unrelated) the following:
If mrsCustomers.RecordCount > 0 Then

May not be what you want, as RecordCount = -1
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 10908140
>>gcnLibrary.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
??
is gcnCoastandCountry or gcnLibrary? is both Connection Object?

try:
..
gcnCoastandCountry.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = CoastandCountry.mdb"
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
..
0
 

Author Comment

by:igotnosmoke
ID: 10910653
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_got_no_smoke/assignmentsysimp.zip

thanks,
Spiz
0
 

Author Comment

by:igotnosmoke
ID: 10910849
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_got_no_smoke/
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10911991
>>acperkins: the Main form always was my start up object.<<
You are missing my point.  I am not referring to your Main Form.  I am referring to your Sub Main in your bas module.  Your connection is never getting opened, because that code is simply not getting executed.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 10912006
>>I Added that statement before yet nothing happens when i try to execute the application... the same errors come up<<
Exactly.  Again that code is never executed, because Sub Main is never executed.
0
 
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 350 total points
ID: 10912598
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!CustomerNumber
'txtlastname.Text = Format(mrsCustomers!lastname, "String")
txtlastname.Text = Format(mrsCustomers!Surname, "String")
txtfirstname.Text = Format(mrsCustomers!firstname, "String")
txtstreet.Text = Format(mrsCustomers!street, "String")
txtsuburb.Text = Format(mrsCustomers!suburb, "String")
txtstate.Text = Format(mrsCustomers!State, "String")
'txtpostcode.Text = mrsCustomers!postcode
txtcode.Text = mrsCustomers!postcode
txtphone.Text = mrsCustomers!phone
txtemail.Text = Format(mrsCustomers!email, "String")
End Sub

* customerno is Not a valid field in the table.
* txtpostcode TextBox is Not Found, i found txtcode instead..

>>Format(mrsCustomers!lastname, "String")
You can write it like : "" & mrsCustomers!lastname instead.
0
 

Author Comment

by:igotnosmoke
ID: 10915719
I got it working, thanks for your help guys...

respond to my next post and ill another 50 points each :)

Cheers,
Spiz
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question