Cannot add form.recordset to adodb.recordset

[Tore]

I have an Access 2000 adp client working with SQL Server 2000.
I have an adp form and a subform. The subform is populated from an ado
recordset:
Dim RS as new adodb.Recordset
Then i get some data to fill in the recordset, and finally updates recordset
of subform SF:
Set Me.SF.Form.Recordset = RS
This works fine.

I export the subform to excel by pressing a button on the main form. Here is
the code:

Private Sub CmdExport_Click()
On Error GoTo Errorhandler

Dim RS As New ADODB.Recordset
RS.CursorLocation = adUseClient 'has no effect
RS.CursorType = adOpenStatic 'has no effect
Set RS = Me.SF.Form.Recordset
Call PublicExportToExcel(RS)
Errorhandler:
End Sub

If the subform is not sorted everything works fine. If the subform has been
sorted Access crashes on the line:
Set RS = Me.SF.Form.Recordset

I get the following error:

Runtime error 429
ActiveX component can't create object

What may be missing here?

Regards

Tore

 

[Sylvain Lafontaine]

First, you are setting the variable RS to a reference to Form.Recordset, so
all the instructions New, CursorLocation and CursorType just before that are
pointless. So first, you should replace "Dim RS As New ADODB.Recordset"
with "Dim RS As ADODB.Recordset" and remove the two following lines.

If this doesn't work, then try creating a clone using either
Form.Recordset.Clone or Form.RecordsetClone. Personally, I found the former
(Form.Recordset.Clone) much less buggy than the later but I don't know which
one could work best in your guest.

If this doesn't work, than create a disconnected empty recordset and copy
everything from Form.Recordset to this one.

 

[Robert Morley]

There are a LOT of problems with Access 2000 and setting form recordsets to
ADO recordsets. You may never get it to work. I remember trying several
years ago, and I got it to work, but it was the touchiest thing I'd ever
dealt with, and I ultimately re-designed the app to create a temporary table
instead.



Rob

 

[5th Amendment]

I fully agree

this was the worst combination possible, Access 2000 ADO bound recordset
forms and reports are unusable at best.

I just am glad that things are better in 2002 / 2003

 

[5th Amendment]

tempTable.. you mean SQL tempTable?

I'm impressed Robert; I hadn't realized that you've actually used ADP

I love them more than I can describe

 

[Tore]

Thanks for your advice. I have tried all of your suggestions but I always get
the same error when I try to read form.recordset into an adodb.recordset
after having sorted a column in the form. Before sorting everything works ok.

You mention a disconnected empty recordset. How do I declare this, and how
should the copy-operation be performed? It seems copy recordset after sorting
a column gets me in trouble any way, and that the trouble seem to be within
the form itself.

Regards

Tore

 

[Tore]

Thanks for you experience. One solution for me could be to keep two copies of
the data, one in form.recordset and another in an adodb.recordset in the
forms vba module. Then if the user press the "Export to excel button" I will
export the unsorted adodb.recordset and not the one that I try to get from
form.recordset.

Tore

 

[Sylvain Lafontaine]

ADP 2000 is really buggy, so you should upgrade to Access 2003; you're
losing your time by trying to stick with the 2000 version.

For an example about disconnected recordset, look at:
http://www.4guysfromrolla.com/webtech/080101-1.2.shtml

 

[Tore]

Thanks again. In this case the organization where my solution is implemented
is still stuck with Office 2000 and Access 2000. They will upgrade later this
year.

Regards

Tore

 

[Susie DBA [MSFT]]

then get a copy of the runtime

re-do it in Access 2007 and SQL 2005.. and get ready for the June
release of the free Access Runtime Tools

 

[e;o]

I have an Access 2000 adp client working with SQL Server 2000.
I have an adp form and a subform. The subform is populated from an ado
recordset:
Dim RS as new adodb.Recordset
Then i get some data to fill in the recordset, and finally updates
recordset
of subform SF:
Set Me.SF.Form.Recordset = RS
This works fine.

I export the subform to excel by pressing a button on the main form. Here
is
the code:

Private Sub CmdExport_Click()
On Error GoTo Errorhandler

Dim RS As New ADODB.Recordset
RS.CursorLocation = adUseClient 'has no effect
RS.CursorType = adOpenStatic 'has no effect
Set RS = Me.SF.Form.Recordset
Call PublicExportToExcel(RS)
Errorhandler:
End Sub

If the subform is not sorted everything works fine. If the subform has
been
sorted Access crashes on the line:
Set RS = Me.SF.Form.Recordset

I get the following error:

Runtime error 429
ActiveX component can't create object

What may be missing here?

Regards

Tore