• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

SQL SErver 2008 r2/VB6 Object Closed Issue

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
Bob Schneider
Asked:
Bob Schneider
4 Solutions
 
eemitCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
add "option explicit" at the top of your module. you will find that conn does not exist outside your DBConn method.
0
 
eemitCommented:
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
 
shorvathCommented:
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
 
Bob SchneiderCo-OwnerAuthor Commented:
Thanks very much!!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now