Solved

run time error 3709 database connection problem

Posted on 2004-04-23
14
42,098 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 49

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 49

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
 

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 49

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

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 49

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now