ADO recordset as record source

[Rob Rutherford]

In Access 2002, is there a way of using an ADO recordset as a form's record source?

If not, and I base my form on a local table, is there a quick way of populating the table using the ADO recordset by using a single
statement (SQL, GetRows or whatever) rather than looping through each record?

 

[Allen Browne]

You can set the Recordset property of the Form. Requires keeping the
Recordset open, and cleaning up after yourself later, of course.

A simpler approach is just to set the RecordSource property of the form
using a SQL statement. For example, to just load the one record:
Me.RecordSoucce = "SELECT * FROM tblClient WHERE ClientID = 99;"

 

[Rob Rutherford]

Thanks Allen. As a matter of interest, do you know of a simple way to populate an Access table from an ADO recordset without looping
through all the records?

Regards,
Rob Rutherford

 

[Allen Browne]

It's much easier to work the other way around.

Start by opening a recordset using a SQL statement. Then add records to your
recordset, and Access writes them to the table.

 

[Rob Rutherford]

Thanks Allen. Sorry if I'm missing the point but I'm not clear how that enables me to add a whole bunch of records to the
recordset/table with a single statement. My motivation is that, having fetched, say, 10,000 records from SQL Server into an ADO
recordset, how do I quickly stuff them into an Access table. I know that an alternative way of doing this is to use an INSERT INTO
statement based on a pass-through query that fetches the records, but I just thought there ought to be a way of doing the same thing
with an ADO recordset and I'd be interested to know what it is.

Regards,
Rob Rutherford

 

[Allen Browne]

The choices that come to mind are:
- execute an Append query statement ("INSER INTO ..."),
- loop through the records,
- attach a view.

 

[Rob Rutherford]

Thanks, I'll give it a try.

The choices that come to mind are:
- execute an Append query statement ("INSER INTO ..."),
- loop through the records,
- attach a view.