Embedded SQL within VBA?

[Rick Charnes]

Can I embed a SQL cursor inside my VBA script? I need to grab some
values from a SQL table and have those available to me as VBA variables.
Thanks.

 

[Peter Jamieson]

Use ADO?

 

[Rick Charnes]

Help! I have no idea what ADO is. Could someone point me in the right
direction with this? Thanks much.

 

[Peter Jamieson]

ADO stands for "ActiveX Data Objects" - it's a COM interface that lets you
connect to data sources that have an OLE DB driver.

I don't know the /best/ place to point you, partly because MS's MSDN site is
now very much focussed on .NET technologies, but for a Word-oriented example
that gives you some flavour and how-tos, try

http://msdn2.microsoft.com/en-us/library/aa140082(office.10).aspx

 

[Rick Charnes]

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.

 

[Peter Jamieson]

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

 

[Rick Charnes]

Works perfectly. THANK YOU VERY MUCH.

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

 

[Rick Charnes]

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

 

[Peter Jamieson]

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.

 

[Rick Charnes]

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

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.

Ye

 

[Peter Jamieson]

Thanks for the feedback Rick - I wasn't at all sure that it could be made to
work