DataSource class help

microsoft.public.vb.general.discussion

22 Oct 2005 1:01 AM

Alastair MacFarlane

Dear all,

If I have created a DataSource class with the following datamembers:

Private Sub Class_GetDataMember(DataMember As String, Data As Object)
Select Case DataMember
Case "Primary"
    Set Data = adoPrimaryRS
Case "Secondary"
    Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
Case "ExpenseFees"
    Set Data = adoPrimaryRS("ChildFeeExps").UnderlyingValue
End Select
End Sub

How can I set the "adoPrimaryRS("ChildFees").UnderlyingValue" to a recordset
in my data consumer application, eg.

Private Sub GetData()
Dim myDataClass as cDataClass
Set myDataClass = New cDataClass
Dim rsTemp as ADODB.Recordset
Set rsTemp=New ADODB.Recordset
Set rsTemp = myDataClass.adoPrimaryRS("ChildFees").UnderlyingValue
End Sub

I have seen many examples of using the BindingsCollection but how can I set
the contents of a ADODB.Recordset to be the same as the datamember called
"ChildFees" in my dataclass?

Thanks again...

Alastair MacFarlane

p.s. In my class initialize I have adde the following code:

Private Sub Class_Initialize()
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
Source=" & App.Path & "\dbdata.mdb;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open _
"SHAPE { SELECT JobNo FROM tblFees } AS ParentFees " & _
        "APPEND ({SELECT FeeItemID, JobNo, FeeNo, FeeDate, FeeAmount " & _
                    "FROM tblFeeItems Order by JobNo } " & _
                    "AS ChildFees RELATE JobNo TO JobNo) AS ChildFees, " & _
               "({SELECT FeeExpItemID, JobNo, FeeExpDate, FeeExpAmount " & _
                    "FROM tblFeeExpenses Order by JobNo } " & _
                    "AS ChildFeeExps RELATE JobNo TO JobNo) AS ChildFeeExps
", db, adOpenStatic, adLockOptimistic
DataMembers.Add "Primary"
End Sub

22 Oct 2005 12:43 PM

Ralph

Show quote Hide quote

"Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
news:elMudQq1FHA.3816@TK2MSFTNGP14.phx.gbl...
> Dear all,
>
> If I have created a DataSource class with the following datamembers:
>
> Private Sub Class_GetDataMember(DataMember As String, Data As Object)
>   Select Case DataMember
>   Case "Primary"
>     Set Data = adoPrimaryRS
>   Case "Secondary"
>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
>   Case "ExpenseFees"
>     Set Data = adoPrimaryRS("ChildFeeExps").UnderlyingValue
>   End Select
> End Sub
>
> How can I set the "adoPrimaryRS("ChildFees").UnderlyingValue" to a
recordset
> in my data consumer application, eg.
>
> Private Sub GetData()
> Dim myDataClass as cDataClass
> Set myDataClass = New cDataClass
> Dim rsTemp as ADODB.Recordset
> Set rsTemp=New ADODB.Recordset
> Set rsTemp = myDataClass.adoPrimaryRS("ChildFees").UnderlyingValue
> End Sub
>
> I have seen many examples of using the BindingsCollection but how can I
set
> the contents of a ADODB.Recordset to be the same as the datamember called
> "ChildFees" in my dataclass?
>
> Thanks again...
>
> Alastair MacFarlane
>
> p.s. In my class initialize I have adde the following code:
>
> Private Sub Class_Initialize()
>   Set db = New Connection
>   db.CursorLocation = adUseClient
>   db.Open "PROVIDER=MSDataShape;Data

PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
Show quoteHide quote

> Source=" & App.Path & "\dbdata.mdb;"
>   Set adoPrimaryRS = New Recordset
>   adoPrimaryRS.Open _
>   "SHAPE { SELECT JobNo FROM tblFees } AS ParentFees " & _
>         "APPEND ({SELECT FeeItemID, JobNo, FeeNo, FeeDate, FeeAmount " &
_
>                     "FROM tblFeeItems Order by JobNo } " & _
>                     "AS ChildFees RELATE JobNo TO JobNo) AS ChildFees, " &
_
>                "({SELECT FeeExpItemID, JobNo, FeeExpDate, FeeExpAmount " &
_
>                     "FROM tblFeeExpenses Order by JobNo } " & _
>                     "AS ChildFeeExps RELATE JobNo TO JobNo) AS
ChildFeeExps
> ", db, adOpenStatic, adLockOptimistic
>   DataMembers.Add "Primary"
> End Sub
>

Alastair,

What is wrong with the example you showed?

> Case "Secondary"
> Set Data = adoPrimaryRS("ChildFees").UnderlyingValue

This should work, IF Data evaluates to a RecordSet.
In other words, if oSomeControl has a 'dataSource' property, does the
following work?
oSomeControl.DataSource = adoPrimaryRS("ChildFees").UnderlyingValue

Shaped queries can be problematic because not all the data is contained in
the recordset, information is given to provide discovery on request to the
Shape Provider. Not all Controls understand this. (A silly way to put it,
but descriptive.)
It might just be the control.

[You might want to change the cursor from adOpenStatic to adOpenKeySet. Also
avoid adOpenDynamic, probably superstition but I have never had any luck
with it and a shape provider.]

The following may help:
http://support.microsoft.com/kb/q213856/
http://support.microsoft.com/kb/q288409/

-ralph

22 Oct 2005 1:02 PM

Alastair MacFarlane

Ralph

Thanks for the reply. The code works well when I set the datasource of a
control to the Shaped Recordset but the problem arises when you want to take
a part of this recordset and apply it to another 'normal' recordset. What I
am trying to do is take a part of the shaped recordset and pass that through
to another class, ie a data consumer. The second class will not use the
whole shaped recordset but only a part of it.

At the moment I have just added another function in the class that extracts
the data. I would just like to have done it all in one.

Data Class (Shaped Recordset) ==> Main App
Dat Class ("Secondary" Datamember) ==>Main App ===> goes to another class
via a public Function

I hope I have not confused you and thanks for the reply.

Alastair MacFarlane

Show quoteHide quote

"Ralph" <nt_consultin***@yahoo.com> wrote in message
news:2JydnTuL9ek-rsfeRVn-3w@arkansas.net...
>
> "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
> news:elMudQq1FHA.3816@TK2MSFTNGP14.phx.gbl...
>> Dear all,
>>
>> If I have created a DataSource class with the following datamembers:
>>
>> Private Sub Class_GetDataMember(DataMember As String, Data As Object)
>>   Select Case DataMember
>>   Case "Primary"
>>     Set Data = adoPrimaryRS
>>   Case "Secondary"
>>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
>>   Case "ExpenseFees"
>>     Set Data = adoPrimaryRS("ChildFeeExps").UnderlyingValue
>>   End Select
>> End Sub
>>
>> How can I set the "adoPrimaryRS("ChildFees").UnderlyingValue" to a
> recordset
>> in my data consumer application, eg.
>>
>> Private Sub GetData()
>> Dim myDataClass as cDataClass
>> Set myDataClass = New cDataClass
>> Dim rsTemp as ADODB.Recordset
>> Set rsTemp=New ADODB.Recordset
>> Set rsTemp = myDataClass.adoPrimaryRS("ChildFees").UnderlyingValue
>> End Sub
>>
>> I have seen many examples of using the BindingsCollection but how can I
> set
>> the contents of a ADODB.Recordset to be the same as the datamember called
>> "ChildFees" in my dataclass?
>>
>> Thanks again...
>>
>> Alastair MacFarlane
>>
>> p.s. In my class initialize I have adde the following code:
>>
>> Private Sub Class_Initialize()
>>   Set db = New Connection
>>   db.CursorLocation = adUseClient
>>   db.Open "PROVIDER=MSDataShape;Data
> PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
>> Source=" & App.Path & "\dbdata.mdb;"
>>   Set adoPrimaryRS = New Recordset
>>   adoPrimaryRS.Open _
>>   "SHAPE { SELECT JobNo FROM tblFees } AS ParentFees " & _
>>         "APPEND ({SELECT FeeItemID, JobNo, FeeNo, FeeDate, FeeAmount " &
> _
>>                     "FROM tblFeeItems Order by JobNo } " & _
>>                     "AS ChildFees RELATE JobNo TO JobNo) AS ChildFees, "
>> &
> _
>>                "({SELECT FeeExpItemID, JobNo, FeeExpDate, FeeExpAmount "
>> &
> _
>>                     "FROM tblFeeExpenses Order by JobNo } " & _
>>                     "AS ChildFeeExps RELATE JobNo TO JobNo) AS
> ChildFeeExps
>> ", db, adOpenStatic, adLockOptimistic
>>   DataMembers.Add "Primary"
>> End Sub
>>
>
> Alastair,
>
> What is wrong with the example you showed?
>>   Case "Secondary"
>>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
> This should work, IF Data evaluates to a RecordSet.
> In other words, if oSomeControl has a 'dataSource' property, does the
> following work?
> oSomeControl.DataSource = adoPrimaryRS("ChildFees").UnderlyingValue
>
> Shaped queries can be problematic because not all the data is contained in
> the recordset, information is given to provide discovery on request to the
> Shape Provider. Not all Controls understand this. (A silly way to put it,
> but descriptive.)
> It might just be the control.
>
> [You might want to change the cursor from adOpenStatic to adOpenKeySet.
> Also
> avoid adOpenDynamic, probably superstition but I have never had any luck
> with it and a shape provider.]
>
> The following may help:
> http://support.microsoft.com/kb/q213856/
> http://support.microsoft.com/kb/q288409/
>
> -ralph
>
>
>
>

22 Oct 2005 2:12 PM

Ralph

Show quote Hide quote

"Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
news:OHt60jw1FHA.3188@TK2MSFTNGP14.phx.gbl...
> Ralph
>
> Thanks for the reply. The code works well when I set the datasource of a
> control to the Shaped Recordset but the problem arises when you want to
take
> a part of this recordset and apply it to another 'normal' recordset. What
I
> am trying to do is take a part of the shaped recordset and pass that
through
> to another class, ie a data consumer. The second class will not use the
> whole shaped recordset but only a part of it.
>
> At the moment I have just added another function in the class that
extracts
> the data. I would just like to have done it all in one.
>
> Data Class (Shaped Recordset) ==> Main App
> Dat Class ("Secondary" Datamember) ==>Main App ===> goes to another class
> via a public Function
>
> I hope I have not confused you and thanks for the reply.
>
> Alastair MacFarlane
>
>
> "Ralph" <nt_consultin***@yahoo.com> wrote in message
> news:2JydnTuL9ek-rsfeRVn-3w@arkansas.net...
> >
> > "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
> > news:elMudQq1FHA.3816@TK2MSFTNGP14.phx.gbl...
> >> Dear all,
> >>
> >> If I have created a DataSource class with the following datamembers:
> >>
> >> Private Sub Class_GetDataMember(DataMember As String, Data As Object)
> >>   Select Case DataMember
> >>   Case "Primary"
> >>     Set Data = adoPrimaryRS
> >>   Case "Secondary"
> >>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
> >>   Case "ExpenseFees"
> >>     Set Data = adoPrimaryRS("ChildFeeExps").UnderlyingValue
> >>   End Select
> >> End Sub
> >>
> >> How can I set the "adoPrimaryRS("ChildFees").UnderlyingValue" to a
> > recordset
> >> in my data consumer application, eg.
> >>
> >> Private Sub GetData()
> >> Dim myDataClass as cDataClass
> >> Set myDataClass = New cDataClass
> >> Dim rsTemp as ADODB.Recordset
> >> Set rsTemp=New ADODB.Recordset
> >> Set rsTemp = myDataClass.adoPrimaryRS("ChildFees").UnderlyingValue
> >> End Sub
> >>
> >> I have seen many examples of using the BindingsCollection but how can I
> > set
> >> the contents of a ADODB.Recordset to be the same as the datamember
called
> >> "ChildFees" in my dataclass?
> >>
> >> Thanks again...
> >>
> >> Alastair MacFarlane
> >>
> >> p.s. In my class initialize I have adde the following code:
> >>
> >> Private Sub Class_Initialize()
> >>   Set db = New Connection
> >>   db.CursorLocation = adUseClient
> >>   db.Open "PROVIDER=MSDataShape;Data
> > PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
> >> Source=" & App.Path & "\dbdata.mdb;"
> >>   Set adoPrimaryRS = New Recordset
> >>   adoPrimaryRS.Open _
> >>   "SHAPE { SELECT JobNo FROM tblFees } AS ParentFees " & _
> >>         "APPEND ({SELECT FeeItemID, JobNo, FeeNo, FeeDate, FeeAmount "
&
> > _
> >>                     "FROM tblFeeItems Order by JobNo } " & _
> >>                     "AS ChildFees RELATE JobNo TO JobNo) AS ChildFees,
"
> >> &
> > _
> >>                "({SELECT FeeExpItemID, JobNo, FeeExpDate, FeeExpAmount
"
> >> &
> > _
> >>                     "FROM tblFeeExpenses Order by JobNo } " & _
> >>                     "AS ChildFeeExps RELATE JobNo TO JobNo) AS
> > ChildFeeExps
> >> ", db, adOpenStatic, adLockOptimistic
> >>   DataMembers.Add "Primary"
> >> End Sub
> >>
> >
> > Alastair,
> >
> > What is wrong with the example you showed?
> >>   Case "Secondary"
> >>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
> > This should work, IF Data evaluates to a RecordSet.
> > In other words, if oSomeControl has a 'dataSource' property, does the
> > following work?
> > oSomeControl.DataSource = adoPrimaryRS("ChildFees").UnderlyingValue
> >
> > Shaped queries can be problematic because not all the data is contained
in
> > the recordset, information is given to provide discovery on request to
the
> > Shape Provider. Not all Controls understand this. (A silly way to put
it,
> > but descriptive.)
> > It might just be the control.
> >
> > [You might want to change the cursor from adOpenStatic to adOpenKeySet.
> > Also
> > avoid adOpenDynamic, probably superstition but I have never had any luck
> > with it and a shape provider.]
> >
> > The following may help:
> > http://support.microsoft.com/kb/q213856/
> > http://support.microsoft.com/kb/q288409/
> >
> > -ralph
> >

'Confused' is a perpetual state of mind for me.

I left out a few very minor* comments I wanted to make on your first post.

1) Don't open an ADO Connection in the Class_Initialize event.

ADO provides connection pooling, but to take maximum advantage of it, you
should always open and close connections as close to where you need them as
possible. By placing the open in the Initialize event, you effectively tie
the connection to the lifecycle of the object itself. This will not present
a problem in a single/simple client/server application, or where the object
has a limited lifetime - but you are likely to get burned if you use this
technique in a distributed application with long-life objects.

However, this would require providing a 'constructor' call and another step
to making your Data Source valid, which does destroy the elegance somewhat.
Also you aren't actually keeping a connection open that long anyway, as ADO
will close it after about 6 seconds of inactivity. It is usually best to
manage When and Where yourself, but perhaps only a matter of 'style'. <g>

2) Always explicitly define your libraries.
Set adoPrimaryRS = New ADODB.Recordset, instead of ...
Set adoPrimaryRS = New Recordset

This is minor, but if you ever load a DAO or another data access module -
you may run into a conflict. (Depending on the order of References) Again,
perhaps an unlikely occurrence in your problem domain - but as Murphy tends
to rule these things - if it does happen the timing is likely to be
inconvenient. <g>

*again very minor points

-ralph

22 Oct 2005 3:19 PM

Alastair MacFarlane

Hi Ralph

Your links from your email have given me a starter. The links led me to a
MSDN article called "How To Extract Child Recordsets from Shaped
Recordset" - Q191744, which is along the lines of what I am trying to do. My
ultimate decider will be, as usual, time. I suppose with a lot of thought I
will eventaully find a 'solution' to my problem, but I have a quick fix and
this quick fix will get me by.

I appreciate your help and comments and will take them into consideration
for further projects. Thanks again.

Alastair MacFarlane

Show quoteHide quote

"Ralph" <nt_consultin***@yahoo.com> wrote in message
news:5qqdnRBtoefh1cfeRVn-hg@arkansas.net...
>
> "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
> news:OHt60jw1FHA.3188@TK2MSFTNGP14.phx.gbl...
>> Ralph
>>
>> Thanks for the reply. The code works well when I set the datasource of a
>> control to the Shaped Recordset but the problem arises when you want to
> take
>> a part of this recordset and apply it to another 'normal' recordset. What
> I
>> am trying to do is take a part of the shaped recordset and pass that
> through
>> to another class, ie a data consumer. The second class will not use the
>> whole shaped recordset but only a part of it.
>>
>> At the moment I have just added another function in the class that
> extracts
>> the data. I would just like to have done it all in one.
>>
>> Data Class (Shaped Recordset) ==> Main App
>> Dat Class ("Secondary" Datamember) ==>Main App ===> goes to another class
>> via a public Function
>>
>> I hope I have not confused you and thanks for the reply.
>>
>> Alastair MacFarlane
>>
>>
>> "Ralph" <nt_consultin***@yahoo.com> wrote in message
>> news:2JydnTuL9ek-rsfeRVn-3w@arkansas.net...
>> >
>> > "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message
>> > news:elMudQq1FHA.3816@TK2MSFTNGP14.phx.gbl...
>> >> Dear all,
>> >>
>> >> If I have created a DataSource class with the following datamembers:
>> >>
>> >> Private Sub Class_GetDataMember(DataMember As String, Data As Object)
>> >>   Select Case DataMember
>> >>   Case "Primary"
>> >>     Set Data = adoPrimaryRS
>> >>   Case "Secondary"
>> >>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
>> >>   Case "ExpenseFees"
>> >>     Set Data = adoPrimaryRS("ChildFeeExps").UnderlyingValue
>> >>   End Select
>> >> End Sub
>> >>
>> >> How can I set the "adoPrimaryRS("ChildFees").UnderlyingValue" to a
>> > recordset
>> >> in my data consumer application, eg.
>> >>
>> >> Private Sub GetData()
>> >> Dim myDataClass as cDataClass
>> >> Set myDataClass = New cDataClass
>> >> Dim rsTemp as ADODB.Recordset
>> >> Set rsTemp=New ADODB.Recordset
>> >> Set rsTemp = myDataClass.adoPrimaryRS("ChildFees").UnderlyingValue
>> >> End Sub
>> >>
>> >> I have seen many examples of using the BindingsCollection but how can
>> >> I
>> > set
>> >> the contents of a ADODB.Recordset to be the same as the datamember
> called
>> >> "ChildFees" in my dataclass?
>> >>
>> >> Thanks again...
>> >>
>> >> Alastair MacFarlane
>> >>
>> >> p.s. In my class initialize I have adde the following code:
>> >>
>> >> Private Sub Class_Initialize()
>> >>   Set db = New Connection
>> >>   db.CursorLocation = adUseClient
>> >>   db.Open "PROVIDER=MSDataShape;Data
>> > PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
>> >> Source=" & App.Path & "\dbdata.mdb;"
>> >>   Set adoPrimaryRS = New Recordset
>> >>   adoPrimaryRS.Open _
>> >>   "SHAPE { SELECT JobNo FROM tblFees } AS ParentFees " & _
>> >>         "APPEND ({SELECT FeeItemID, JobNo, FeeNo, FeeDate, FeeAmount
>> >> "
> &
>> > _
>> >>                     "FROM tblFeeItems Order by JobNo } " & _
>> >>                     "AS ChildFees RELATE JobNo TO JobNo) AS ChildFees,
> "
>> >> &
>> > _
>> >>                "({SELECT FeeExpItemID, JobNo, FeeExpDate, FeeExpAmount
> "
>> >> &
>> > _
>> >>                     "FROM tblFeeExpenses Order by JobNo } " & _
>> >>                     "AS ChildFeeExps RELATE JobNo TO JobNo) AS
>> > ChildFeeExps
>> >> ", db, adOpenStatic, adLockOptimistic
>> >>   DataMembers.Add "Primary"
>> >> End Sub
>> >>
>> >
>> > Alastair,
>> >
>> > What is wrong with the example you showed?
>> >>   Case "Secondary"
>> >>     Set Data = adoPrimaryRS("ChildFees").UnderlyingValue
>> > This should work, IF Data evaluates to a RecordSet.
>> > In other words, if oSomeControl has a 'dataSource' property, does the
>> > following work?
>> > oSomeControl.DataSource = adoPrimaryRS("ChildFees").UnderlyingValue
>> >
>> > Shaped queries can be problematic because not all the data is contained
> in
>> > the recordset, information is given to provide discovery on request to
> the
>> > Shape Provider. Not all Controls understand this. (A silly way to put
> it,
>> > but descriptive.)
>> > It might just be the control.
>> >
>> > [You might want to change the cursor from adOpenStatic to adOpenKeySet.
>> > Also
>> > avoid adOpenDynamic, probably superstition but I have never had any
>> > luck
>> > with it and a shape provider.]
>> >
>> > The following may help:
>> > http://support.microsoft.com/kb/q213856/
>> > http://support.microsoft.com/kb/q288409/
>> >
>> > -ralph
>> >
>
> 'Confused' is a perpetual state of mind for me.
>
> I left out a few very minor* comments I wanted to make on your first post.
>
> 1) Don't open an ADO Connection in the Class_Initialize event.
>
> ADO provides connection pooling, but to take maximum advantage of it, you
> should always open and close connections as close to where you need them
> as
> possible. By placing the open in the Initialize event, you effectively tie
> the connection to the lifecycle of the object itself. This will not
> present
> a problem in a single/simple client/server application, or where the
> object
> has a limited lifetime - but you are likely to get burned if you use this
> technique in a distributed application with long-life objects.
>
> However, this would require providing a 'constructor' call and another
> step
> to making your Data Source valid, which does destroy the elegance
> somewhat.
> Also you aren't actually keeping a connection open that long anyway, as
> ADO
> will close it after about 6 seconds of inactivity. It is usually best to
> manage When and Where yourself, but perhaps only a matter of 'style'. <g>
>
> 2) Always explicitly define your libraries.
>    Set adoPrimaryRS = New ADODB.Recordset, instead of ...
>    Set adoPrimaryRS = New Recordset
>
> This is minor, but if you ever load a DAO or another data access module -
> you may run into a conflict. (Depending on the order of References) Again,
> perhaps an unlikely occurrence in your problem domain - but as Murphy
> tends
> to rule these things - if it does happen the timing is likely to be
> inconvenient. <g>
>
> *again very minor points
>
> -ralph
>
>