?
Solved

Cannot pass adodb.Connection to subform?

Posted on 2004-04-22
13
Medium Priority
?
608 Views
Last Modified: 2011-10-03
Hi,
I have the following code. It gives an error "the connection cannot be used to perform this operation. It is either closed or invalid in this context", when i try to open a table using the cnAgent connection.
Is it because I cannot pass an ado connection variable to a subform?

calling form
------------------------
    Set frmPswd.CallingForm = Me
    frmPswd.setCnAgent = cnAgent
    frmPswd.Show vbModal

called form
------------------------
Dim cnAgent As New adodb.Connection
Public Property Let setCnAgent(ByVal data As adodb.Connection)
    cnAgent = data                                      <-------------------- I verified that it gets here and value looks ok
End Property
etc
userAgent.Open scmd, cnAgent, adOpenKeyset, adLockOptimistic  <--- stmt generates error

Thank you
0
Comment
Question by:spoowiz
13 Comments
 
LVL 1

Accepted Solution

by:
lalithaw earned 150 total points
ID: 10886871
Hi.,

try
userAgent.Open scmd, cnAgent,adOpenStatic, adLockReadOnly

And it is not recommended that passing object as parameter.

U better keep ur connection generation globaly (in a dll better) and call it in the form.

as follows.
Dim objconnection As EMPSProcesses.DBAssign

Set objconnection = New EMPSProcesses.DBAssign
'Connecting to Database
Set cnPARAM = objconnection.AssignDB

KLW
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10887840
You can pass a ADODB.Connection as a parameter but you should use ByRef instead of ByVal.
0
 
LVL 9

Expert Comment

by:dancebert
ID: 10888577
You can pass a ADODB.Connection as a parameter as long as the caller and the callee are in the same process.  Can't pass an open connection across a process boundry.
0
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.

 
LVL 39

Assisted Solution

by:appari
appari earned 75 total points
ID: 10888839
try changing the property to set property, now its let

change it like this

calling form
------------------------
    Set frmPswd.CallingForm = Me
    Set frmPswd.setCnAgent = cnAgent
    frmPswd.Show vbModal

called form
------------------------
Dim cnAgent As New adodb.Connection
Public Property Set setCnAgent(ByVal data As adodb.Connection)
    Set  cnAgent = data                                      <-------------------- I verified that it gets here and value looks ok
End Property
0
 

Author Comment

by:spoowiz
ID: 10890304
Jim, dacebert - Are you sure? Maybe I did something wrong but I tried ByRef and it didn't work. Forms are in same project.
lalithaw- still don't know how to create or call dll. I'll try to learn.
Appari - That didn't do it.
0
 
LVL 11

Assisted Solution

by:jmwheeler
jmwheeler earned 75 total points
ID: 10890541
Why not just declare the connection in a module as public and then you won't have to worry about passing it.  It would just be available to all forms and you could use the same name for the connection across all the forms.
0
 

Author Comment

by:spoowiz
ID: 10892703
jmwheeler - have you tried it? i tried that before but it doesn't work for me. perhaps i'm doing something wrong?
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10894172
I do it all the time.  I use a standard set of Subs I wrote for working with ADODB.


'Example Module

Public db As New ADODB.Connection
Public rs As New ADODB.Recordset
Public sql As String

Public Sub OpenDB (ByRef db As ADODB.Connection, MySource As String)
    If db.State <> 1 Then
       db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MySource & ";"
    End If
End Sub

Public Sub OpenRS (ByRef rs As ADODB.Recordset, ByRef db As ADODB.Connection, sql As String)
    If rs.State <> 1 Then
       rs.Open sql, db
    End If
End Sub

Public Sub CloseRS (ByRef rs As ADODB.Recordset)
    If rs.State = 1 Then
       rs.close
       Set rs = Nothing
    End If
End Sub

Public Sub CloseDB (ByRef db As ADODB.Connection)
    If db.State = 1 Then
       db.close
       Set db = Nothing
    End If
End Sub


'Then you can use these calls anywhere in your program

sql = "SELECT * FROM YourTableName"
OpenDB db, "C:\PathToYourDb\dbfilename.mdb"
OpenRS rs, db, sql


Oh yeah, I should ask this.  You did add a referece to 'Microsoft ActiveX Data Object 2.? Library'
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10894180
oh yeah you can also use

CloseRS rs
CloseDB db
0
 

Assisted Solution

by:itdanushka
itdanushka earned 75 total points
ID: 10897854
You can do this like this too

 in Parent form you can open connection to batabase your using. connection variable in parent form should declare as public variable.

you have to declare a connection variable in sub form too and it has to be public

then when loading sub form you have to set parent form connection to sub form connection

set [connection variable] = [sub form].[connection variable name]
 
 set Conn = frmSub.Conn

Good luck
0
 

Author Comment

by:spoowiz
ID: 10900602
jimwheeler,
your example, i think, only works in the "current" form?
if you use it in subforms, what would be the syntax in the subforms?
and yes to "'Microsoft ActiveX Data Object 2"

itdanushka,
in your example, it looks like you're passing the subform variable to the parent variable?
i need to do the opposite, parent to subform.
i tried a similar statement for parent to subform already before and it hadn't worked for me.
the error says "the connection cannot be used to perform this operation. It is either closed or invalid in this context" when i try to use the connection variable in the subform.
i declare BOTH cnVC publilc:
Public cnVC As New adodb.Connection
and use this stmt in the parent:
Set frmFtp.cnVC = cnVC
i try to use cnVC in frmFtp(subform) and it fails.
0
 

Author Comment

by:spoowiz
ID: 10956809
no more feedback?
0
 

Author Comment

by:spoowiz
ID: 11095331
what you all suggest must work, but i can't seem to get it. now i don't have the time to try and try. what i'm doing now (very inefficient) works so i must close this now and go on. thank you all for the responses. i'm going to try to split the points. thanks again.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 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