tstanbur
03-22-2006, 10:02 AM
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: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)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
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: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)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