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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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
Anthony PerkinsCommented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.