Cannot pass adodb.Connection to subform?

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
spoowizAsked:
Who is Participating?
 
lalithawConnect With a Mentor Commented:
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
 
jmwheelerCommented:
You can pass a ADODB.Connection as a parameter but you should use ByRef instead of ByVal.
0
 
dancebertCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
appariConnect With a Mentor Commented:
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
 
spoowizAuthor Commented:
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
 
jmwheelerConnect With a Mentor Commented:
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
 
spoowizAuthor Commented:
jmwheeler - have you tried it? i tried that before but it doesn't work for me. perhaps i'm doing something wrong?
0
 
jmwheelerCommented:
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
 
jmwheelerCommented:
oh yeah you can also use

CloseRS rs
CloseDB db
0
 
itdanushkaConnect With a Mentor Commented:
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
 
spoowizAuthor Commented:
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
 
spoowizAuthor Commented:
no more feedback?
0
 
spoowizAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.