Solved

Cannot pass adodb.Connection to subform?

Posted on 2004-04-22
13
596 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 50 total points
Comment Utility
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
Comment Utility
You can pass a ADODB.Connection as a parameter but you should use ByRef instead of ByVal.
0
 
LVL 9

Expert Comment

by:dancebert
Comment Utility
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
 
LVL 39

Assisted Solution

by:appari
appari earned 25 total points
Comment Utility
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
Comment Utility
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 25 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:spoowiz
Comment Utility
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
Comment Utility
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
Comment Utility
oh yeah you can also use

CloseRS rs
CloseDB db
0
 

Assisted Solution

by:itdanushka
itdanushka earned 25 total points
Comment Utility
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
Comment Utility
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
Comment Utility
no more feedback?
0
 

Author Comment

by:spoowiz
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

771 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

12 Experts available now in Live!

Get 1:1 Help Now