[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SErver 2008 r2/VB6 Object Closed Issue

Posted on 2013-01-05
5
Medium Priority
?
485 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 900 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 200 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 900 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 900 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
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…

872 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