Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

run time error 3709 database connection problem

Posted on 2004-04-23
14
Medium Priority
?
42,485 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 53

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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 53

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 53

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 600 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 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 1400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

670 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