run time error 3709 database connection problem

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
igotnosmokeAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>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
 
Ryan ChongCommented:
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
 
igotnosmokeAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ryan ChongCommented:
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
 
igotnosmokeAuthor Commented:
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
 
Ryan ChongCommented:
>>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
 
igotnosmokeAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Ryan ChongCommented:
>>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
 
igotnosmokeAuthor Commented:
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
 
igotnosmokeAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Ryan ChongConnect With a Mentor Commented:
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
 
igotnosmokeAuthor Commented:
I got it working, thanks for your help guys...

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

Cheers,
Spiz
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.