Solved

SQL SErver 2008 r2/VB6 Object Closed Issue

Posted on 2013-01-05
5
465 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

757 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

19 Experts available now in Live!

Get 1:1 Help Now