Solved

SQL SErver 2008 r2/VB6 Object Closed Issue

Posted on 2013-01-05
5
479 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
[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
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 70

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

691 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