Connection to SQL Server from Outlook?

[tstanbur]

Hi,

I need to be able to update a SQL Server DB when a message arrrives from a specified recipient. The message subject will have a reference code which I need to update the DB with.

I have set up a rule based on this and now need to create some sort of VBA script to:

1. Get their reference code from the subject
2. Connect to the SQL Server DB
3. Update the DB

I have found some example code for connecting to SQL Server but can't get it to connect.

This is the code I've been using:[VBA]Dim conn As adodb.connection
Dim rs As adodb.recordset
Dim SQL As String
Set conn = New adodb.connection
con.Open "Provider=SQLOLEDB; Data Source = xxxsource; Initial Catalog = xxxic; User Id = xxxid; Password= xxxpw"

SQL = "Select * from tbl_xxx"
Set rs = conn.Execute(SQL)[/VBA]I get a "user-defined type not defined" error with the "adodb.connection" highlighted.


I'm sure it is possible to do what I want....any pointers much appreciated!

If it is not possible, I suppose the next best thing would be to output to a text file where I could use ASP to read that and insert to the DB.

Thanks in advance....Tom

[Killian]

Hi Tom and welcome to VBAX

The error is telling you that the compiler doesn't know what an "adodb" is.
The data object library you need to use has to be added to your project first - then you can access it and use it's object model

In the VBE, go to Tools>References and select the library you want to use, in this case "Microsoft ActiveX Data Object 2.x Library"
You should then be able to run the code and make the connection