Persisting a recordset

[JustJim]

Having just re-read Adezii's article on the above subject, I thought I'd have a play.

I think there must be a "Reference" that I need to set to use ADODB.Recordset? Otherwise I get a "User-defined type not defined" error.

Jim

[nico5038]

Try the "Microsoft AcitveX Data Objects" library.
It's selected by default from A2000 or A2003...

Nic;o)

[MMcCarthy]

Having just re-read Adezii's article on the above subject, I thought I'd have a play.

I think there must be a "Reference" that I need to set to use ADODB.Recordset? Otherwise I get a "User-defined type not defined" error.

Jim

You need a reference to ...

Microsoft ActiveX Data Objects 2.X library
Microsoft ADO Ext. 2.X for DDL and Security

[JustJim]

You need a reference to ...

Microsoft ActiveX Data Objects 2.X library
Microsoft ADO Ext. 2.X for DDL and Security

Thank you both. (Each individually and together (Dang 20 character limit))

Jim

[MMcCarthy]

Thank you both. (Each individually and together (Dang 20 character limit))

Jim

You're welcome.

[JustJim]

Having just re-read Adezii's article on the above subject, I thought I'd have a play.

<Snip>

Jim

OK, I'm all Referenced up and playing, one of the immediate uses I thought of was to store a recordset as it was at a particular date and then re-use it even if the data in the database had changed.

when I try the following...

Private Sub Report_Open(Cancel As Integer)
Dim rsSample As ADODB.Recordset
Dim strFile As String
 
Set rsSample = New ADODB.Recordset
 
'Construct a file name to use
strFile = CurrentProject.Path & "\Sample.adtg"
 
'Make sure the file exists
  If Len(Dir(strFile)) > 0 Then
     'Open the recordset from the file
      rsSample.Open strFile, , adOpenDynamic, adLockOptimistic
     'Reconnect the recordset to the database
      rsSample.ActiveConnection = CurrentProject.Connection
     'Make the recordset the recordsource of this subreport
      Set Me.Recordset = rsSample
  End If
  rsSample.Close
    Set rsSample = Nothing
 
End Sub

which is pretty much the code that Adezii posts (except for the cursor type) I get an error at the

      Me.Recordset = rsSample

line which says Error 2593, This feature is not available in an MDB.

There ain't nuffin' in the help files for the Recordset property saying it's only for an ADP. Is this a problem because of the ADODB.Recordset or am I missing something?

Jim

[ADezii]

OK, I'm all Referenced up and playing, one of the immediate uses I thought of was to store a recordset as it was at a particular date and then re-use it even if the data in the database had changed.

when I try the following...

Private Sub Report_Open(Cancel As Integer)
Dim rsSample As ADODB.Recordset
Dim strFile As String
 
Set rsSample = New ADODB.Recordset
 
'Construct a file name to use
strFile = CurrentProject.Path & "\Sample.adtg"
 
'Make sure the file exists
  If Len(Dir(strFile)) > 0 Then
     'Open the recordset from the file
      rsSample.Open strFile, , adOpenDynamic, adLockOptimistic
     'Reconnect the recordset to the database
      rsSample.ActiveConnection = CurrentProject.Connection
     'Make the recordset the recordsource of this subreport
      Set Me.Recordset = rsSample
  End If
  rsSample.Close
    Set rsSample = Nothing
 
End Sub

which is pretty much the code that Adezii posts (except for the cursor type) I get an error at the

      Me.Recordset = rsSample

line which says Error 2593, This feature is not available in an MDB.

There ain't nuffin' in the help files for the Recordset property saying it's only for an ADP. Is this a problem because of the ADODB.Recordset or am I missing something?

Jim

Just subscribing for now, but I'll definately drop in later and hopefully we'll get this ironed out.

[ADezii]

OK, I'm all Referenced up and playing, one of the immediate uses I thought of was to store a recordset as it was at a particular date and then re-use it even if the data in the database had changed.

when I try the following...

Private Sub Report_Open(Cancel As Integer)
Dim rsSample As ADODB.Recordset
Dim strFile As String
 
Set rsSample = New ADODB.Recordset
 
'Construct a file name to use
strFile = CurrentProject.Path & "\Sample.adtg"
 
'Make sure the file exists
  If Len(Dir(strFile)) > 0 Then
     'Open the recordset from the file
      rsSample.Open strFile, , adOpenDynamic, adLockOptimistic
     'Reconnect the recordset to the database
      rsSample.ActiveConnection = CurrentProject.Connection
     'Make the recordset the recordsource of this subreport
      Set Me.Recordset = rsSample
  End If
  rsSample.Close
    Set rsSample = Nothing
 
End Sub

which is pretty much the code that Adezii posts (except for the cursor type) I get an error at the

      Me.Recordset = rsSample

line which says Error 2593, This feature is not available in an MDB.

There ain't nuffin' in the help files for the Recordset property saying it's only for an ADP. Is this a problem because of the ADODB.Recordset or am I missing something?

Jim

You are creating and destroying a Recordset Variable (rstSample) before the Report has time to display its data. Aside from this, your mysterious Error will still appear. To complicate matters further, this code, except for declaring rstSample Globally and not closing the Recordset in the Procedure, works flawlessly in a Form's Open() Event. It seems as though it will not work within the context of a Report but don't give up hope, I'll look at it later.