bind a standalone recordset to a form

Hello All,

I am looking to whip a recordset out of thin air through a series of
database record manipulation (so there is no physical equivalence to
any database entities such as a table or compounded query) and then
bind that standalone recordset to a form to display the result of such
operations.

I could seem to find a good way of doing it, as I try to change the
recordsource property of the form with no success.

So here is what I have got,

Private Sub Form_Open(Cancel As Integer)
Dim rsRecSet As New ADODB.Recordset
rsRecSet.Fields.Append "Account ID", adVarChar, 20
rsRecSet.Fields.Append "Num1", adInteger
rsRecSet.Fields.Append "Num2", adInteger
rsRecSet.Open
rsRecSet.AddNew
rsRecSet("Account ID") = "ABCDEFG"
rsRecSet("Num1") = 99
rsRecSet("Num2") = 99
rsRecSet.Update
RecordSource = "rsRecSet"
End Sub

so it doesn't seem to work - is there actually anyway to do this
(besides actually create a table then write the recrodset into the
table and then read off the table)?

thank you so much for your assistance!

re: bind a standalone recordset to a form

I think you can search the archives of this newsgroup at
http://groups.google.com and find an extensive discussion of this subject. I
found that discussion very informative, though I never had occasion to use
it in paying work.

Larry Linson
Microsoft Access MVP

"Henry Su" <su.henry@gmail.com> wrote in message
news:53bc397d.0408271317.4d2dc90c@posting.google.c om...[color=blue]
> Hello All,
>
> I am looking to whip a recordset out of thin air through a series of
> database record manipulation (so there is no physical equivalence to
> any database entities such as a table or compounded query) and then
> bind that standalone recordset to a form to display the result of such
> operations.
>
> I could seem to find a good way of doing it, as I try to change the
> recordsource property of the form with no success.
>
> So here is what I have got,
>
> Private Sub Form_Open(Cancel As Integer)
> Dim rsRecSet As New ADODB.Recordset
> rsRecSet.Fields.Append "Account ID", adVarChar, 20
> rsRecSet.Fields.Append "Num1", adInteger
> rsRecSet.Fields.Append "Num2", adInteger
> rsRecSet.Open
> rsRecSet.AddNew
> rsRecSet("Account ID") = "ABCDEFG"
> rsRecSet("Num1") = 99
> rsRecSet("Num2") = 99
> rsRecSet.Update
> RecordSource = "rsRecSet"
> End Sub
>
> so it doesn't seem to work - is there actually anyway to do this
> (besides actually create a table then write the recrodset into the
> table and then read off the table)?
>
> thank you so much for your assistance![/color]

re: bind a standalone recordset to a form

Hello Larry,

Thanks for your pointer. Believe you me, I have tried many searches
and yielded very poor results - I think it might be due to I'm not
using the common semantic for these terms.

Are there specific search terms that you could tell me to research by?

Thanks,
Henry


"Larry Linson" <bouncer@localhost.not> wrote in message news:<5FSXc.228$Gr2.122@trnddc07>...[color=blue]
> I think you can search the archives of this newsgroup at
> http://groups.google.com and find an extensive discussion of this subject. I
> found that discussion very informative, though I never had occasion to use
> it in paying work.
>
> Larry Linson
> Microsoft Access MVP
>
> "Henry Su" <su.henry@gmail.com> wrote in message
> news:53bc397d.0408271317.4d2dc90c@posting.google.c om...[color=green]
> > Hello All,
> >
> > I am looking to whip a recordset out of thin air through a series of
> > database record manipulation (so there is no physical equivalence to
> > any database entities such as a table or compounded query) and then
> > bind that standalone recordset to a form to display the result of such
> > operations.
> >
> > I could seem to find a good way of doing it, as I try to change the
> > recordsource property of the form with no success.
> >
> > So here is what I have got,
> >
> > Private Sub Form_Open(Cancel As Integer)
> > Dim rsRecSet As New ADODB.Recordset
> > rsRecSet.Fields.Append "Account ID", adVarChar, 20
> > rsRecSet.Fields.Append "Num1", adInteger
> > rsRecSet.Fields.Append "Num2", adInteger
> > rsRecSet.Open
> > rsRecSet.AddNew
> > rsRecSet("Account ID") = "ABCDEFG"
> > rsRecSet("Num1") = 99
> > rsRecSet("Num2") = 99
> > rsRecSet.Update
> > RecordSource = "rsRecSet"
> > End Sub
> >
> > so it doesn't seem to work - is there actually anyway to do this
> > (besides actually create a table then write the recrodset into the
> > table and then read off the table)?
> >
> > thank you so much for your assistance![/color][/color]

re: bind a standalone recordset to a form

"Disconnected recordset" is the term for an ADP.

While you can open a recordset in an MDB, you can't "disconnect" it, so it
would be no use to use that as the RecordSource of a Form or Report -- you
are just as well off to use the SQL or Query that you'd use to open the
Recordset as the RecordSource.

And, IMNSHO, that is also often best in an ADP. There are some situations in
which using a "disconnected recordset" is useful, but the technique can be
problematic, too. What happens if multiple users, each using a disconnected
recordset, are updating the same data and then they all try to "batch
update" the underlying tables?

Larry Linson
Microsoft Access MVP



"Henry Su" <su.henry@gmail.com> wrote in message
news:53bc397d.0408290944.54420ae9@posting.google.c om...[color=blue]
> Hello Larry,
>
> Thanks for your pointer. Believe you me, I have tried many searches
> and yielded very poor results - I think it might be due to I'm not
> using the common semantic for these terms.
>
> Are there specific search terms that you could tell me to research by?
>
> Thanks,
> Henry
>
>
> "Larry Linson" <bouncer@localhost.not> wrote in message[/color]
news:<5FSXc.228$Gr2.122@trnddc07>...[color=blue][color=green]
> > I think you can search the archives of this newsgroup at
> > http://groups.google.com and find an extensive discussion of this[/color][/color]
subject. I[color=blue][color=green]
> > found that discussion very informative, though I never had occasion to[/color][/color]
use[color=blue][color=green]
> > it in paying work.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> > "Henry Su" <su.henry@gmail.com> wrote in message
> > news:53bc397d.0408271317.4d2dc90c@posting.google.c om...[color=darkred]
> > > Hello All,
> > >
> > > I am looking to whip a recordset out of thin air through a series of
> > > database record manipulation (so there is no physical equivalence to
> > > any database entities such as a table or compounded query) and then
> > > bind that standalone recordset to a form to display the result of such
> > > operations.
> > >
> > > I could seem to find a good way of doing it, as I try to change the
> > > recordsource property of the form with no success.
> > >
> > > So here is what I have got,
> > >
> > > Private Sub Form_Open(Cancel As Integer)
> > > Dim rsRecSet As New ADODB.Recordset
> > > rsRecSet.Fields.Append "Account ID", adVarChar, 20
> > > rsRecSet.Fields.Append "Num1", adInteger
> > > rsRecSet.Fields.Append "Num2", adInteger
> > > rsRecSet.Open
> > > rsRecSet.AddNew
> > > rsRecSet("Account ID") = "ABCDEFG"
> > > rsRecSet("Num1") = 99
> > > rsRecSet("Num2") = 99
> > > rsRecSet.Update
> > > RecordSource = "rsRecSet"
> > > End Sub
> > >
> > > so it doesn't seem to work - is there actually anyway to do this
> > > (besides actually create a table then write the recrodset into the
> > > table and then read off the table)?
> > >
> > > thank you so much for your assistance![/color][/color][/color]

re: bind a standalone recordset to a form

On 27 Aug 2004 14:17:57 -0700, su.henry@gmail.com (Henry Su) wrote:
[color=blue]
>Hello All,
>
>I am looking to whip a recordset out of thin air through a series of
>database record manipulation (so there is no physical equivalence to
>any database entities such as a table or compounded query) and then
>bind that standalone recordset to a form to display the result of such
>operations.
>[/color]
....

Hi
One way you can use a set of expressions as a bindable recordsource is
to link to some grounded table. What follows is from a post I made in
1998.

The idea is to have just one table, of numbers only, which must be big
enough. I used a table called "integers" with one primary key field
called "I" of type long. (SQL integer!) In the examples I assume this
runs from 0 to 9999. Now you can simulate virtual tables with queries,
provided you can associate an integer with each of your calculated
expressions. For example

VTableDates:
SELECT DISTINCTROW DateAdd("d",[i],CVDate("1 January 1990")) AS
DateValue, Integers.i
FROM Integers
ORDER BY Integers.i;

You can create the table "integers" by running:

Public Function VSetup()
Dim rs As Recordset
Dim j As Long
Dim db As DATABASE
On Error Resume Next ' (!)
Set db = DBEngine(0)(0)
db.Execute "CREATE TABLE integers (i INTEGER );"
Set rs = db.OpenRecordset("integers", dbOpenTable)
DBEngine(0).BeginTrans
For j = 0 To 9999
rs.AddNew
rs!i = j
rs.UPDATE
Next j
DBEngine(0).CommitTrans
rs.Close
db.Execute "CREATE INDEX PrimaryKey ON integers (i) WITH PRIMARY;"
End Function

David Schofield

re: bind a standalone recordset to a form

On Aug 27 2004, 05:17 pm, su.henry@gmail.com (Henry Su) wrote in
news:53bc397d.0408271317.4d2dc90c@posting.google.c om:
[color=blue]
> I am looking to whip a recordset out of thin air through a series of
> database record manipulation (so there is no physical equivalence to
> any database entities such as a table or compounded query) and then
> bind that standalone recordset to a form to display the result of such
> operations.
>
> I could seem to find a good way of doing it, as I try to change the
> recordsource property of the form with no success.[/color]

You need to set the Recordset property, not RecordSource, and this is only
possible in A2000 and later. See this post for more details:

http://www.google.com/groups?as_umsg...3u03b9gvb72b@4
ax.com

--
remove a 9 to reply by email