Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Embedded SQL within VBA?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rick Charnes - 23 Aug 2007 17:29 GMT
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 - 27 Aug 2007 17:27 GMT
Use ADO?

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> 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.
Rick Charnes - 28 Aug 2007 14:43 GMT
Help!  I have no idea what ADO is.  Could someone point me in the right
direction with this?  Thanks much.

> Use ADO?
Peter Jamieson - 28 Aug 2007 16:32 GMT
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

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Help!  I have no idea what ADO is.  Could someone point me in the right
> direction with this?  Thanks much.
>
>> Use ADO?
Rick Charnes - 29 Aug 2007 16:57 GMT
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.

> ADO stands for "ActiveX Data Objects" - it's a COM interface that lets you
> connect to data sources that have an OLE DB driver.
[quoted text clipped - 4 lines]
>
> http://msdn2.microsoft.com/en-us/library/aa140082(office.10).aspx
Peter Jamieson - 29 Aug 2007 18:13 GMT
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.