How to select from sql 2003 in vba for Outlook?

[JensonKitty]

Trying to figure out how to execute a sql statement from within vba for Outlook. The developers have a database with names, addresses, and most importantly, pictures. So, with this in mind, I want to execute a sql statement from within vba for outlook to get the photo names. I see how to run sql from excel, but can't figure out how to do in vba for Access. Any suggestions?

[JP2112]

I do it like this, so it doesn't matter where I am doing it from.


Function GetMDBData(sqlQuery As String) As Variant
' adapted from http://www.ozgrid.com/forum/showthread.php?t=18289
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stSQL As String
Dim vaData() As String
Dim values As Variant
Dim headers() As String
Dim i As Long, j As Long
' Instantiate the new ADO-objects.
Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' Get the path to the database
stDB = ' path to your MDB file
' Create the SQL-statement
stSQL = sqlQuery
' Connect to the databasesource.
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
' Open the recordset.
rst.Open stSQL, cnt, 3, 3
' Transfer the recordset to the array of variant-type.
If rst.RecordCount > 0 Then
' size arrays
ReDim headers(1 To 1, 1 To rst.fields.Count)
ReDim vaData(1 To rst.RecordCount + 1, 1 To rst.fields.Count)
' put headers into first array
For i = 1 To rst.fields.Count
headers(1, i) = rst.fields(i - 1).Name
Next i
' put values into second array
values = rst.getrows

' put header into first row of final array
For i = 1 To UBound(headers, 2)
vaData(1, i) = headers(1, i)
Next i
' put values into final array, starting from row 2
If rst.RecordCount > 1 Then
For i = 2 To UBound(vaData)
For j = 1 To UBound(vaData, 2)
vaData(i, j) = Trim$(values(i - 1, j))
Next j
Next i
Else ' only one record, must loop slightly different
For i = 2 To UBound(vaData, 2) + 1
vaData(2, i - 1) = Trim$(values(i - 2, 0))
Next i
End If
GetMDBData = vaData
End If
End Function


Then I would simply call the function and assign the result to a Variant:


Dim results As Variant
results = GetMDBData("SELECT [PictureNames] FROM [DeveloperDatabase];")