Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How do I get back a Recordset (Read 2559 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
How do I get back a Recordset
14.03.07 at 10:40:05
How do I get back a Recordset
Graham from the UK asked the following question.
Access from the VBA side.  How do I get back a Recordset using your connectivity tools? (i.e. I want to return a recordset, then iterate around this in VBA doing other stuff. I don't want ot have to manage my own connection to do this)
This is exactly how SQL*XL can be used. You can use it to do the difficult stuff so you can concentrate on what you need to do.
SQL*XL exposes the recordset through the statement object. When you execute a select statement you give the statement to the clsSQL object in SQL*XL: SQLXL.SQL.SetText "select * from emp"
SQL*XL will put the statement in the statements collection. Since you only put one statement in it is now available as statement 1. The statement will be of type clsStmtDQL. It has a Dynaset property where you can get our ADO recordset wrapper object clsDynasetADO or our OO4O dynaset object wrapper clsDynasetOO4O in case you used OO4O to connect to Oracle.
Dim dyn as Object
Set dyn = SQLXL.Sql.Statements(1).Dynaset
Now, most of the ADO recordset methods and properties are exposed and wrapped with code to enable further services within SQL*XL. If you can use the methods and properties of these objects that would be best. E.g. use MoveNext to step through the records until the EOF propery is set to false in which case you have reached the end of the recordset.
If you must get access to the underlying ADO recordset you can reference it through the recordset property:
Dim rs as Object
Set rs = SQLXL.Sql.Statements(1).Dynaset.Recordset
An example can be found in this topic. See macro2.
Back to top
« Last Edit: 16.03.07 at 10:43:04 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1