passing recordset from an activex to calling program

Hi,
We are trying to create an activeX that creates and manipulates the recordset, then the calling program can read the recordset fields. We did fine in an activeX dll with a public recordset. But are not having luck with the same logic in an activeX exe.. Seems that the recordset in the activex can pick up a new record & reflect the new records fields.. But the calling program does not stay in sync with the public recordset. Infact it does not reflect any info unless a .requery is used then both projects share the same information.. But when the activeX does a .movenext the activeX info changes, & the calling program remains stuck on the info from the requery.

I thought a public recordset meant everyone shares the common info.. What is making the public recordset act quirky in an ActiveX exe?? How can we fix this problem???
Thanks
Wenda & Ann

Did you set the one thread per instance option? This makes the ActiveX exe use separate threads and separate data. You may have to change the interface declarations - this is usually a bad thing (breaking binary compatibility and all that)

Can you show us how you declared the property for your recordset? It is a property, right?

We shortened some of the code in our ActiveX Exe Class module.. It is below, hope it helps. We looked & in properties of the project there was a choice of Threadpe Object or Threadpool.. We have Threadpool as 1 thread... This is a default we did not change.. Also in the properties list we have Instancing as multiuse and persistable = notpersistable..

Here is the code ...

Option Explicit

Dim strConn As String
Dim ConnTemp As ADODB.Connection
' **** Recordset Trying to make it public to sendto calling project!
Public CommQry As ADODB.Command
Public rsQry As ADODB.Recordset
' *****
Private ParmName0 As String
Private ParmType0 As String
Private ParmEntry0 As Variant
Private ParmLength0 As Integer
Private dspSPName As String

Public Enum Enum_OpenCursorType
ForwardOnly = 0
Keyset = 1
Dynamic = 2
StaticSnapshot = 3
End Enum
Public Enum Enum_ParmType
PTChar = 129
PTVarChar = 200
PTDate = 7
PTInteger = 3
End Enum
Private chkValid As Single
Public Function OpenSP(ConnectionName As String, StoredProcedureName As String, OpenCursorType As Enum_OpenCursorType) As Single
On Error GoTo ErrorRtn
OpenSP = 1
'open temp
Set ConnTemp = New ADODB.Connection
strConn = ConnectionName ' Connection passed from main programs public function.
ConnTemp.ConnectionString = strConn
ConnTemp.Open
'open Temp4
Set CommQry = New ADODB.Command

'Create and set the Recordset for tempCredit
CommQry.CommandType = adCmdStoredProc
CommQry.CommandText = StoredProcedureName ' Store Procedure passed from main program..
ConnTemp.CursorLocation = adUseClient
CommQry.ActiveConnection = ConnTemp


Set rsQry = New ADODB.Recordset
rsQry.CursorType = OpenCursorType ' stropenCursorType is an enum set up the same as the VB
' logics own enum for CursorType so that our integer # for
' Dynaset matches VB's Enum integer.. We both pass integers,
' but use understandable words to the user..
' rsQry.CursorType = adOpenDynamic

rsQry.LockType = adLockOptimistic
Set rsQry.Source = CommQry

' if first parameter setitup...
If Len(ParmName0) > 0 Then
chkValid = SetParameter(ParmName0, ParmType0, ParmEntry0, ParmLength0)
End If

rsQry.Open
dspSPName = StoredProcedureName
OpenSP = 0

Exit Function
ErrorRtn:
MsgBox "Error in opening recset for " & StoredProcedureName & " " & Err.Description & " " & Err.Number

End Function
Public Function CloseSP()
rsQry.Close
Set rsQry = Nothing
End Function

Private Function SetParameter(Name As String, ParmType As String, PassingEntry As Variant, Optional ParmLength) As Single
Dim SetParm As Parameter

Set SetParm = New ADODB.Parameter
With SetParm
.Name = Name
.Type = ParmType
.Direction = adParamInput
.Value = PassingEntry
If Not IsMissing(ParmLength) Then
.Size = ParmLength
End If
End With

CommQry.Parameters.Append SetParm

End Function
Public Function Parameter0(ParmName As String, ParmType As Enum_ParmType, ParmEntry As Variant, Optional ParmLength As Integer) As Single
ParmName0 = ParmName
ParmType0 = ParmType
ParmEntry0 = ParmEntry
If Not IsMissing(ParmLength) Then
ParmLength0 = ParmLength
End If
End Function
Public Function AlterParameter(WhichParameterNumber As Integer, ParmEntry As Variant) As Single
Dim strParmEntry As String
Dim WPN As Integer
On Error GoTo Errormsg

'** This changes the parameter and requeries the
'** recordset set using the new value. This recordset
'** uses a Stored Procedure which has a parameter.**
AlterParameter = 1
If WhichParameterNumber < 0 Or WhichParameterNumber > 3 Then
MsgBox "Parameters range from 0 to 3)"
Exit Function
End If

WPN = WhichParameterNumber
' sometimes string type are numberic & confuse the issue, if using PTChar make sure program knows that
If (WPN = 0 And ParmType0 = 129) Or (WPN = 1 And ParmType1 = 129) Or _
(WPN = 2 And ParmType2 = 129) Or (WPN = 3 And ParmType3 = 129) Then
strParmEntry = ParmEntry
CommQry.Parameters.Item(WhichParameterNumber).Value = strParmEntry
Else
CommQry.Parameters.Item(WhichParameterNumber).Value = ParmEntry
End If
rsQry.Requery
AlterParameter = 0
Exit Function
Errormsg:
If Err.Number = 3421 Then
MsgBox "Error in altering Parameter" & WhichParameterNumber & vbCrLf & _
"Stored procedure name = " & dspSPName & vbCrLf & _
"Error Number & Description = " & Err.Number & " " & Err.Description & vbCrLf & vbCrLf & _
"When setting up the parameter you must have set it up as a date or number (vbdate or vbnumber) and the entry passed, (" & _
ParmEntry & ") is not valid for that type."
ElseIf 3219 Then
AlterParameter = 0
Else
MsgBox "Error in altering Parameter" & WhichParameterNumber & " " & dspSPName & " " & Err.Description & " " & Err.Number
End If
End Function
' ** The last two were not necessary for the .dll We could do it with rs1.rsqry.movenext
Public Function MoveNext()
rsQry.MoveNext
End Function
Public Function EOF() As Boolean
If rsQry.EOF Then
EOF = True
Else
EOF = False
End If
End Function

My partner thought you would like to see the calling programs stuff on this... This logic will pick up about 5 records for the rep.
The bottom do list runs thru 5 times until EOF is hit.. But the Inv#
always remains the same.. But if we put a display in the ActiveX it is pulling 5 differnent invoice#

option explicit
Private rsCreditMaint As New ULStoredProcedure ' stored procedure routine (with parameters)
.
.
.
' PCCredit table maintenance
Answer = rsCreditMaint.Parameter0("@RepNumber", PTChar, "", 4)

Answer = rsCreditMaint.OpenSP(FuncConnRepCom(), "ACMCreditMaintenance", Dynamic)
.
.
.
' set up the parameter for the Specific Rep Number...
Answer = rsCreditMaint.AlterParameter(0, "0010") ' repnumber
cmbInv.Clear

' This should list all the diff Invoice numbers it only shows the
'first, though ActiveX recordset does show all the diff Inv# in
' the same commonly shared public variable.
Do Until rsCreditMaint.EOF
cmbInv.AddItem rsCreditMaint.rsQry!crInvoiceNumber
rsCreditMaint.MoveNext
Loop

How about a suggestion? You've obviously put a lot of work into this project. And done some very interesting things.

Go back to the dll - in process model.

Why? Um... Er....

You kinda broke all the rules for how to implement a COM server object. I'm not sure how I would make what you have work as an ActiveX EXE, without a major overhaul. It will probably work okay as in-process dll.

Without going through everything blow-by-blow:

When you have public variables like you have, any connected client can change them, and they will be changed for everybody.
So if John does something while Susie is connected, they will both foul each other up.

The reason the invoice number doesn't change is that the recordset exists on your PC - as a pointer to an object in the PC's memory. When you do a movenext, it doesn't respond because it isn't connected to anything. .movenext is a function pointer to a method (probably a NULL pointer) that doesn't exist on the PC. This is why it worked as an in process dll, but not now. Via inheritance. You were sharing the same process memory space.
Not now, though.

The .movenext and all the other recordset methods you are calling probably need to be implemented as methods of the ActiveX Server process. They aren't now. I'm surprised you got along as well as you did.

Your least expensive man-hour choice is to revert back to dll.
If not - try T. Lewis 'VB COM' book from Wrox. It's written strictly for VB programmers.

Thanks... That is funny.. We got a few chuckles over here.. See we are learning all this on our own & books always seem to take you just to the edge & dump you on your -you-know-what-.

Anyway we had to laugh because the book you mentioned we have.. But, only got to chapter 3 & then got involved in some on-line training courses that worked in & out of objects.. But still only to a point..

We will take your advice and return to the animal farm.. (Also yesterday before reading this I already returned to the .dll since it worked and left the .exe for it will be nice if we ever get it to work.)

Thanks
Wenda:eek: and Ann:D