Perfect; thank you. Using this I now have the concepts down. But I may
be biting off more than I can chew: I need to create a SQL CURSOR and
FETCH data from its result set and plop the results into VBA variables.
Am I asking VBA via ADO too much here? Any thoughts on this? Thanks
much.
> Am I asking VBA via ADO too much here?
No, but there may be a terminology gap if you are used to using SQL CURSORS
etc.
In ADO you would typically
a. open a Connection
b. open a RecordSet by specifying a COnnection, a piece of SQL, and a
couple of other parameters
The RecordSet is in effect a set of rows. You can step through the rows
using the Recordset's .MoveFirst, .MoveNext methods and so on. You can then
get the values of columns in the "current" row via the Fields collection of
the Recordset object, e.g.
myVBAvariablename = objRecordSet.Fields("mycolumnname").Value
Here's an example of a sinmple piece of ADO code - sorry, there are lots of
red herrings in here but I don't have anything simpler to hand. This code is
intended to do a roll-your-own Word mailmerge to email using addresses from
an Excel worksheet.
Sub SendWithXLAddresses()
Const strMergeTemplateFolder = "amergetemplate"
Const strXLWorkbookFullname = "c:\xlfiles\eaddresses.xls"
Const strEAddressColumn = "Emailaddress"
Const strQuery = "SELECT " & strEAddressColumn & " FROM [eaddr$]"
Dim objMailItem As Outlook.MailItem
Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recordset
Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset
' Specify Excel 8.0 by using the Extended Properties
' property, and then open the Excel file specified by
' strDBPath. This should work for Excel 97 and later
With objConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strXLWorkbookFullname
With objRecordset
.Open _
Source:=strQuery, _
ActiveConnection:=objConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly
.MoveFirst
While Not .EOF
Set objMailItem =
Outlook.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders(strMergeTemplateFolder).Items(1).Copy
With objMailItem
.To = objRecordset.Fields(strEAddressColumn).Value
.Send
End With
Set objMailItem = Nothing
.MoveNext
Wend
.Close ' the recordset
End With
.Close ' the connection/workbook
End With
Set objRecordset = Nothing
Set objConnection = Nothing
End Sub

Signature
Peter Jamieson
http://tips.pjmsn.me.uk
> Perfect; thank you. Using this I now have the concepts down. But I may
> be biting off more than I can chew: I need to create a SQL CURSOR and
[quoted text clipped - 13 lines]
>>
>> http://msdn2.microsoft.com/en-us/library/aa140082(office.10).aspx
Rick Charnes - 30 Aug 2007 16:41 GMT
Works perfectly. THANK YOU VERY MUCH.
> > Am I asking VBA via ADO too much here?
>
[quoted text clipped - 5 lines]
> b. open a RecordSet by specifying a COnnection, a piece of SQL, and a
> couple of other parameters
Rick Charnes - 31 Aug 2007 19:25 GMT
I have my ADO connection working fine now. But can anyone help me
figure out why .RecordCount is returning -1 here? MyValue is assigned
correctly. Thanks.
Dim rstcount As Integer
Dim myvalue as string
objRecordset.Source = "SELECT * FROM mytable WHERE name = 'UE'"
objRecordset.Open
myvalue = objRecordset.Fields("gen_value")
rstcount = objRecordset.RecordCount 'returns -1
> > Am I asking VBA via ADO too much here?
>
[quoted text clipped - 5 lines]
> b. open a RecordSet by specifying a COnnection, a piece of SQL, and a
> couple of other parameters
Peter Jamieson - 01 Sep 2007 09:23 GMT
Without going too deeply into areas that I'm not actualy that familiar with
anyway, try using
objRecordSet.MoveLast
before testing the recordcount.
If you can't MoveLast, then the provider you are using probably defaults to
using an "adOpenForwardOnly" cursortype (you can use the CursorType
parameter of the Open method to try to specify the CursorType, but the
provider may change the type if it doesn't support the one you asked for.
For example, the Access/Jet OLE DB provider doesn't (or didn't) support
dynamic cursors and will give you a Keyset cursor. You can check the cursor
type post-Open by looking at
objRecordSet.CursorType
If you have an adOpenForwardONly cursor type, try changing it to any of the
others - if you need to be able to update the recordset, use adOpenDynamic
or adOpenKeyset, otherwise you may be able to use adOpenStatic. Then, if
necessary, try using .MoveLast.

Signature
Peter Jamieson
http://tips.pjmsn.me.uk
>I have my ADO connection working fine now. But can anyone help me
> figure out why .RecordCount is returning -1 here? MyValue is assigned
[quoted text clipped - 19 lines]
>> b. open a RecordSet by specifying a COnnection, a piece of SQL, and a
>> couple of other parameters
Rick Charnes - 04 Sep 2007 20:24 GMT
Yes!!!! Thank you, thank you, thank you. Thanks to these tips I now
have it working and objRecordSet.RecordCount returns the correct value.
I found that I *was* able to set objRecordSet.CursorType to
adOpenDynamic, and the provider didn't seem to change it back to
adOpenForwardOnly after the Open, but RecordCount still returned -1.
Setting CursorType to *adOpenStatic* did the trick and now I get my
RecordCount. (I DON'T need to update the recordset.)
I also see that:
objRecordset.Supports(adApproxPosition)
objRecordset.Supports(adBookmark)
now both return TRUE. Thank you VERY much for this help.
> Without going too deeply into areas that I'm not actualy that familiar with
> anyway, try using
[quoted text clipped - 17 lines]
> or adOpenKeyset, otherwise you may be able to use adOpenStatic. Then, if
> necessary, try using .MoveLast.
Ye
Peter Jamieson - 05 Sep 2007 14:19 GMT
Thanks for the feedback Rick - I wasn't at all sure that it could be made to
work:-)

Signature
Peter Jamieson
http://tips.pjmsn.me.uk
> Yes!!!! Thank you, thank you, thank you. Thanks to these tips I now
> have it working and objRecordSet.RecordCount returns the correct value.
[quoted text clipped - 39 lines]
>>
> Ye