Solved

SQL SErver 2008 r2/VB6 Object Closed Issue

Posted on 2013-01-05
5
467 Views
Last Modified: 2013-01-07
I have moved my connection string to a sub in a module and now when I try to run the app I get an "can't perform when object is closed..."  I assume I have messed up my parameters.  Here is what I have done:

1) I have declared a Public variable conn as ADODB.Connection in the top of my module

2) I have the following call to the procedure:
    Call DBConn

    Set rs = New ADODB.Recordset
    sql = "SELECT ..."
    rs.Open sql, conn, 1, 2

Open in new window


3) Here is the procedure:
Public Sub DBConn()
    'On Error GoTo ErrorHandler
    
    Dim conn As ADODB.Connection
    
    Set conn = New ADODB.Connection
    
    If sWhichSrvr = "Remote" Then
        conn.Open "Provider=SQLNCLI10;Server=216.185.199.1,1433\SQLExpress;Database=CCMeet;Uid=uid;Pwd=pwd;"
    Else
        conn.Open "Provider=SQLNCLI10;Server=VIRA-5\SQLExpress;Database=CCMeet;Trusted_Connection=yes;"
    End If
    
    Exit Sub
ErrorHandler:
    Beep
    ErrorHandler
End Sub

Open in new window

0
Comment
Question by:Bob Schneider
5 Comments
 
LVL 15

Assisted Solution

by:eemit
eemit earned 225 total points
ID: 38747524
Try:
Private m_oConn As ADODB.Connection

Public Sub DBConn()
    'On Error GoTo ErrorHandler
    
    Set m_oConn = New ADODB.Connection
    
    If sWhichSrvr = "Remote" Then
        m_oConn.Open "Provider=SQLNCLI10;Server=216.185.199.1,1433\SQLExpress;Database=CCMeet;Uid=uid;Pwd=pwd;"
    Else
        m_oConn.Open "Provider=SQLNCLI10;Server=VIRA-5\SQLExpress;Database=CCMeet;Trusted_Connection=yes;"
    End If
    
    Exit Sub
ErrorHandler:
    Beep
    ErrorHandler
End Sub 

Open in new window


Than:
Call DBConn

    Set rs = New ADODB.Recordset
    sql = "SELECT ..."
    rs.Open sql, m_oConn, 1, 2 

Open in new window

0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 50 total points
ID: 38747530
add "option explicit" at the top of your module. you will find that conn does not exist outside your DBConn method.
0
 
LVL 15

Assisted Solution

by:eemit
eemit earned 225 total points
ID: 38747532
Or simply comment this line (in Sub DBConn):
Dim conn As ADODB.Connection

Open in new window


Dim conn As ADODB.Connection
does explicitly dimension the variable conn as Local.


This is a good example why we need Variable Scope Prefixes

Global:   g or g_
Module-level:   m or m_
Local to procedure:   None
0
 
LVL 9

Accepted Solution

by:
shorvath earned 225 total points
ID: 38750436
How about this in your Module


Option Explicit
Global conn As ADODB.Connection

Public Sub DBConn_Open()
    'On Error GoTo ErrorHandler
   
    Set conn = New ADODB.Connection
    
    If sWhichSrvr = "Remote" Then
        conn.Open "Provider=SQLNCLI10;Server=216.185.199.1,1433\SQLExpress;Database=CCMeet;Uid=uid;Pwd=pwd;"
    Else
        conn.Open "Provider=SQLNCLI10;Server=VIRA-5\SQLExpress;Database=CCMeet;Trusted_Connection=yes;"
    End If
    
    Exit Sub
ErrorHandler:
    Beep
    ErrorHandler
End Sub


Public Sub DBConn_Close()
    'On Error GoTo ErrorHandler
   
    If conn.State <> adStateClosed  Then
         conn.Close
    End If
    
    Exit Sub
ErrorHandler:
    Beep
    ErrorHandler
End Sub

Open in new window



Then from your call

    Call DBConn_Open

    Set rs = New ADODB.Recordset
    sql = "SELECT ..."
    rs.Open sql, conn, 1, 2
    
    'Do all your work

    rs.Close
    Call DBConn_Close

Open in new window

0
 

Author Closing Comment

by:Bob Schneider
ID: 38750498
Thanks very much!!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
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…

896 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

14 Experts available now in Live!

Get 1:1 Help Now