Connecting to a Database using Outlook

[robboll]

Hello Developers!

I am trying to capture some field values from a SQL Server 2008
database in some Outlook VBA. When I step through the code with the
debugger it seems to connect fine, and the strSQL variable displays
the select statement correctly. The problem is the variables mtid and
mpath come out null. I need to capture mtid and mpath from the select
statement. Do you have any suggestions on how I should do this? I've
had a lot of variations of this code, and this one seem to perform the
best. Thanks for any help with this!

RBollinger


Function test4()
Set strConnection = CreateObject("ADODB.Connection")
strConnection.Open ("Provider=SQLOLEDB;Data
Source=ULDSV02;Database=WeeklyReport;Trusted_Connection=Yes;")
Set rs = CreateObject("ADODB.Recordset")

strSQL = "SELECT [TopicID] as mtid,[Path] as mpath FROM [WeeklyReport].
[dbo].[uvw_TIDPath] WHERE rtrim([TopicID]) ='520';"

rs.Open strSQL, strConnection
strConnection.Close
Set rs = Nothing
Set strConnection = Nothing

End Function

 

[Norman Yuan]

What code is between

rs.Open strSQL, strConnection

and

strConnection.Close?

After rs.Open ...., you are supposed to examine if RecordSet.EOF is true. If
EOF is true, that means the SQL statement does not find a matching record
from the data source. If EOF is not true, then the recordset does contain
record, in this case, if rs!mtid is still null, then the field (TopicID) of
the record in the database is null.