ADO Connection Problem

[alexcraig]

I have some ADO connection code listed below which works fine in a Word macro
running on the same machine. However, in Outlook I keep getting a
"User-defined type not defined" compile error on the RS1 recordset
Declaration. The Help info suggests I need to ensure that the "Data Access
Object" is checked in the References dialog box. However, I don't see it
listed in the Object Library.

Assuming that is the problem, where can I obtain it?

Also, while I'm begging for help, because of the above mentioned problem I
haven't been able to test my last 4 lines of code for this Project. However,
I'm reasonably certain I'm missing something in that area to get the job done.

Basically, the goal is to extract a text block from Sql Server using a
couple of queries which are driven by an initial input number and then insert
it at the current cursor location in a new email composition window which is
already partly populated with text.

Any help with these two issues would be greatly appreciated.

Dim Source As Variant, Rs1 As New ADODB.Recordset
Dim JobOrderNo As String, Srcresult As String, Connect As String, CoID As
String, CoSizzle As String
Dim objMsg As MailItem, objInsp As Outlook.Inspector


Sub ICSIZ()
'
' Insert Company Sizzle Macro
' Macro created 12/31/2007 by Alex Craig
'
' Display Input Box and Get Job Order #
Message = "Enter Job Order #"
Title = "Job Order # Input Box"
JobOrderNo = InputBox(Message, Title)
' Get Job Order Data from Database
Source = Array("SELECT * FROM tblJobOrders WHERE JobOrderNo = ", " ")
Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=XXXXXX;
Database=XXX; UID=xxxxx; PWD=xxxxx;"
Source(1) = JobOrderNo
Srcresult = Source(0) & Source(1)
Rs1.Open Srcresult, Connect
Rs1.MoveFirst
CoID = Rs1!CompanyID
Rs1.Close
Set Rs1 = Nothing
' Get Company Data from Database
Source = Array("SELECT * FROM tblCompanyData WHERE CompanyID = ", "
")
Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=XXXXXXX;
Database=XXX; UID=xxxxxx; PWD=xxxxxx;"
Source(1) = CoID
Srcresult = Source(0) & Source(1)
Rs1.Open Srcresult, Connect
Rs1.MoveFirst
CoSizzle = Rs1!Directions
Rs1.Close
Set Rs1 = Nothing
' Insert Company Sizzle into the current composition window at current
cursor location
Set objInsp = objMsg.GetInspector
Set sInspector = CreateObject("Redemption.SafeInspector")
sInspector.Item = Application.ActiveInspector
sInspector.SelText = CoSizzle
End Sub

 

[alexcraig]

I should have mentioned I'm using Office 2007.

 

[Norman Yuan]

You need to set reference to Microsoft ActiveX Data Object library 2.x (2.8
is the latest).

BTW, do not use "New" in declaration, such as

Dim RS1 As New ADODB.RecordSet

use

Dim RS1 As ADODB.RecordSet

instead. And later instantiate it with "New" keyword, when needed.

 

[alexcraig]

The library you suggested did the trck. Also appreciate the Delcaration
direction.

Thank you very much.

The code now runs fine down to the point where it has successfully retrieved
the data I want from Sql Server. However, I'm stumped on how to get it into
current active new composition window at the current cursor position. Any
ideas?

 

[alexcraig]

Norman,

You can disregard my last reply as I managed to get it working. ;-)

For anyone perusing this thread in the future, here is some sample code
which successfully grabs some data from Sql/Server and inserts it at the
current cursor point in an active New Composition Mail Window.

Code:

Dim Source As Variant, Rs1 As ADODB.Recordset
Dim JobOrderNo As String, Srcresult As String, Connect As String, CoID As
String, CoSizzle As String
Dim objMsg As MailItem, objInsp As Outlook.Inspector, objDoc As Object
Dim sInspector As Object


Sub ICSIZ()
'
' Insert Company Sizzle Macro
' Macro created 12/31/2007 by Alex Craig
'
' Display Input Box and Get Job Order #
Message = "Enter Job Order #"
Title = "Job Order # Input Box"
JobOrderNo = InputBox(Message, Title)
' Get Job Order Data from Database
Set Rs1 = New ADODB.Recordset
Source = Array("SELECT * FROM tblJobOrders WHERE JobOrderNum = ", "
")
Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=XXXX;
Database=XXXX; UID=XXXX; PWD=XXXX;"
Source(1) = JobOrderNo
Srcresult = Source(0) & Source(1)
Rs1.Open Srcresult, Connect
Rs1.MoveFirst
CoID = Rs1!CompanyID
Rs1.Close
Set Rs1 = Nothing
' Get Company Data from Database
Set Rs1 = New ADODB.Recordset
Source = Array("SELECT * FROM tblCompanyData WHERE CompanyID = ", "
")
Connect = "Provider=MSDASQL; Driver={SQL Server}; Server=XXXX;
Database=XXXX; UID=XXXX; PWD=XXXX;"
Source(1) = CoID
Srcresult = Source(0) & Source(1)
Rs1.Open Srcresult, Connect
Rs1.MoveFirst
CoSizzle = Rs1!Directions
Rs1.Close
Set Rs1 = Nothing
' Insert Company Sizzle into the current composition window at current
cursor location
Set objMsg = Application.CreateItem(olMailItem)
Set sInspector = CreateObject("Redemption.SafeInspector")
sInspector.Item = Application.ActiveInspector
sInspector.SelText = vbCrLf & vbCrLf & CoSizzle
End Sub