Solved: Can I run a SQL query from Outlook '03 VBA?

[jamescol]

Here's another challenge I must tackle in my OL2K3 app. :dunno I need to obtain a result set from a SQL Server 2000 DB and use it to populate some of the custom form fields.

Is it possible to do this using VBA? :confused: I have searched Microsoft's support site, technet, MSDN, etc. but have not located a definitive answer. :mad:

If such a query is possible, here is the select statement I need to run, along with the datasource and sql driver name. It is not necessary to include authentication in the call, since the SQL server uses pass-through authentication from Windows. Can someone show me the code construct to use?


'DataSource = "Warehouse"
'Driver "SQL SERVER"

'This is the query:
SELECT
vwContract.ContractNumber,
vwContract.ContractStartDate,
vwContract.ContractExpireDate,
vwContract.ContractTitle,
vwEmployee.EmployeeFirstLastName

FROM vwContract, vwEmployee

Where
[OwnerHRPersonId]= <username> ' username is supplied by another procedure
AND
vwEmployee.EmployeeId = vwContract.AdministratorEmployeeId

Order by
[ContractTitle]



I then need to place the ContractTitle results in a combo-box control with the first row as the default and populate the other form fields with the data corresponding to the first row. When the user selects a different ContractTitle form teh combo-box, I will need to update the other form fields from the corresponding data in the result set.

What would be the best way to store the result set and populate/update the fields as the user changes selections from the combo box?

Thanks,
James

[mark007]

Add a reference to ActiveX Data Objects and use:


Dim con as adodb.connection
dim rec as adodb.recordset
dim MySQL as string
MySQL="SELECT vwContract.ContractNumber, vwContract.ContractStartDate, vwContract.ContractExpireDate, vwContract.ContractTitle, vwEmployee.EmployeeFirstLastName FROM vwContract, vwEmployee Where [OwnerHRPersonId]= ' username is supplied by another procedure AND vwEmployee.EmployeeId = vwContract.AdministratorEmployeeId Order by [ContractTitle]"
set con=new adodb.connection
con.open Provider=sqloledb;Data Source=WareHouse;Initial Catalog=myDatabaseName"
set rec=con.execute(MySQL)
'rec now contains the results set
'you can loop through it using
while not rec.eof
'access your fields here using
TheValue=rec.fields(x).value
'where x is the column to return
rec.movenext
wend


Hope that helps.

For more info on connection strings see:

http://www.able-consulting.com./MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

:)

[jamescol]

Mark,
Works like a charm! Thank you very much for the code segment. I can't tell you how many hours I searched for that information.

One more question - I see several ActiveX Data Object references to choose. Does it matter which one I use? Obviously the one I used for testing works, but I don't want to run into any trouble later.

Thanks again!

James

[mark007]

Which references do you have?

:)

[jamescol]

Mark,
These are the references that appear in my list: :dunno

MS ActiveX Data Objects 2.0 Library
MS ActiveX Data Objects 2.1 Library
MS ActiveX Data Objects 2.6 Library
MS ActiveX Data Objects 2.7 Library
MS ActiveX Data Objects 2.8 Library
MS ActiveX Data Objects Recordset 2.7 Library
*MS ActiveX Data Objects (Multi-Dimensional) 2.8 Library

* The one I used to test

Thanks,
James

[mark007]

I just wrote a long answer but due to the internet at work being **** here is a slightly abbreviated version.

MS ActiveX Data Objects 2.x Library is the standard library where the 2.x depends on the version. If you are using the code just on your PC or are confident that it will only be used on PC's with the same MDAC installed then use the highest one.

MS ActiveX Data Objects Recordset 2.7 Library contains only the recordset object, not the command or connection objects so shouldn't be used in your case.

MS ActiveX Data Objects (Multi-Dimensional) 2.8 Library is for use with multi-dimensional data sets so is rarely required.

In summary therefore the one you are most likely to want to use is:

MS ActiveX Data Objects 2.8 Library

:)

[JensonKitty]

Wooopss... posted too soon. Found how to add the activex in vba.