Setting the DataSource for a Disconnected Recordset.

Setting the DataSource for a Disconnected Recordset.

Post by bali » Mon, 13 Jun 2005 21:03:14


There is a scenario in my application,where you have to read an exce
file and import the Values to a SQL Server Table.

The methodoly that i have implemented is:

Step 1: Open an Excel Connection using Microsoft Jet Oledb Provider.
2. Read the Excel file and store the values in a recordset (Rs1).
3. Open a Connection to SQL Server Table and Map it to a Secon
Recordset (Rs2).
4. Assign the values of the Excel Recset(Rs1) to SQL Server mappe
5. Perform BatchUpdate using UpdateBatch on Rs2.

My question is, is it possible to perform the UpdateBatch on th
Recordset which contains the Excel File values RS1 ?

since the Excel file reflects exactly the SQL Server Table Structure!

I hope we can kill the Rs1's Active connection and map to SQL Server
but how to map this one to a specified Table ?

I believe this is logically possible ?

Thanks a lot.


Posted via

Setting the DataSource for a Disconnected Recordset.

Post by Mark J. Mc » Tue, 14 Jun 2005 13:56:31

Not via ADO standard interfaces, I think the only way to do this is by
persisting the recordset to XML, and working with it at the XML level. I'm
not even certain it's viable across different providers, to investigate,
call the recordset.Save method on both the recordset opened on the Excel
file, and the one you use to call UpdateBatch (after it is connected,) to
see what the differences are.

For a SQL Server-based recordset, there are rs:basecatalog, rs:baseschema,
rs:basetable and rs:basecolumn attributes for each field, that you'd have to
create; there is also a rs:writeunknown attribute for each field (must be
true) and an rs:updatable attribute for the recordset.

Maintaining those would be the minimum you'd get away with. There may be
other inherent differences, but I've never dumped such a recordset out to
XML, so couldn't say.