Connecting Outlook to SQL server

You might want to try ADO instead.  I find it easier to work with.  

Here is a link for some examples:

http://www.slipstick.com/dev/database.htm

It doesn't give detail information on how to use ADO as connection, if possible, could anyone give a sample on how to make the connection?

There are links off of that page which will explain that, but  they do require some digging.  Here is some sample code that works with an Access database.  I don't have any for SQL databases, but I'll try to dig some up.

     Set db = CreateObject("ADODB.Connection")
               db.Mode = 3
               db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\folder\databasename.mdb;"
               strSQL = "SELECT * FROM tblCustomers;"
               Set Rs = CreateObject("ADODB.Recordset")
               Rs.Open strSQL, db5,2,3
                Item.UserProperties.Find("txtSalesRep").Value = Rs.Fields("salesrep")

rs.Close
db.close

Set rs = Nothing
Set db = Nothing

I believe all you would have to do for SQL database is to change the provider type.  

--PatriciaC

It's close, but those code only requires to link the file, but what I need is use the ODBC source instead of OLE source.  I was able to connect with ODBC, but I can't seem to display the data on the form.  The following is what I have:
Option Explicit
 'Script level declarations
 Dim dbe
 Dim wrkODBC
 Dim conDB
 Dim Rs
 Dim gstrAppName
 Dim IsLoading
 Dim MyArray
 Dim MyArrayPO
 'Dim page objects and control collections
 Dim objPage
 Dim objControls
 Dim objPagePO
 Dim objControlsPO
 'DAO constants dim
 Dim dbUseODBC
 Dim dbDriverComplete
 Dim dbDriverNoPrompt
 Dim dbDriverPrompt
 Dim dbDriverCompleteRequired
 Dim dbOpenSnapshot
 Dim dbOpenForwardOnly
 Dim dbOpenDynamic
 dbUseODBC=1
 dbDriverComplete=0
 dbDriverNoPrompt=1
 dbDriverPrompt=2
 dbDriverCompleteRequired=3
 dbOpenSnapshot=4
 dbOpenForwardOnly=8
 dbOpenDynamic=16
 dim MyNum

Sub Item_Open()
 Dim curExtension
 Dim lngOrderID
 Dim i
 Dim strSQL
 If Not (GetODBCConnection("autonum","ODBC;DSN=SMS",dbDriverCompleteRequired)) Then
     Exit Sub
 End If
 strSQL = "Select ID from autonum"
 set Rs = conDB.openrecordset(strsql,dbopensnapshot)
 'rs.movelast
 rs.movefirst
 mynum = rs(0)
     
' item.textbox1.text = mynum  <~~~~~~~~Stuck
End Sub

Function GetODBCConnection (ByVal MyDSN, ByVal MyConn, ByVal MyPrompt)
 Dim strUser
 Dim strPass
 Dim dbUseODBC
 'Must assign values to constants in VBScript
 dbUseODBC=1
 strUser=""
 strPass=""
 Set dbe= Item.Application.CreateObject("DAO.dbEngine.35")
 Set wrkODBC = dbe.CreateWorkspace("ODBCworkspace", strUser, strPass, dbUseODBC)
 dbe.Workspaces.Append wrkODBC
 Set conDB = wrkODBC.OpenConnection("Connection1", MyPrompt, , MyConn)
 GetODBCConnection = True
End Function

Function Item_Close()
 conDB.Close
 wrkODBC.Close


This is partial of my code, I was able to use ODBC connection, and when I display it on a messagebox, it works, but I can't seem to display it on the textbox.  Also, as request before, how do I update the database, (i.e. retreive ID number 1123, update the ID to become 1124 everytime this form is loaded)

Try using userproperties.  I had the same problem updating textboxes.  Eventually, I called Microsoft and was advised to use userproperties.  You have to create your own fields when adding controls to forms and this will update the field, which in turn will display in the textbox.

item.userproperties.find("client1").Value = rstGen.fields("Client1").value

mmmm You hit problem at workspaces?? You do have Access Reference library ticked dont you???

cheers

George

Oh, I figure out some problems out.  Now I am able to communicate with SQL server.  I created a ODBC With the name SMS, link it to my autonum table, and it'll accept updates command from outlook.  The reason why I can't display it was because of a similar incedent with jessnjeff.  Thanks.  After looking at your comment, I researched a little further, and came up with the following in my code:

Option Explicit
 'Script level declarations
 Dim dbe
 Dim wrkODBC
 Dim conDB
 Dim Rs
 Dim gstrAppName
 'Dim page objects and control collections
 Dim objPage
 Dim objControls
 Dim objPagePO
 Dim objControlsPO
 'DAO constants dim
 Dim dbUseODBC
 Dim dbDriverComplete
 Dim dbDriverNoPrompt
 Dim dbDriverPrompt
 Dim dbDriverCompleteRequired
 Dim dbOpenSnapshot
 Dim dbOpenForwardOnly
 Dim dbOpenDynamic
 dbUseODBC=1
 dbDriverComplete=0
 dbDriverNoPrompt=1
 dbDriverPrompt=2
 dbDriverCompleteRequired=3
 dbOpenSnapshot=4
 dbOpenForwardOnly=8
 dbOpenDynamic=16
 dim MyNum

 Dim txtOrderID

Sub Item_open()
 Dim curExtension
 Dim lngOrderID
 Dim i
 Dim strSQL
 If Not (GetODBCConnection("autonum","ODBC;DSN=SMS",dbDriverCompleteRequired)) Then
     Exit Sub
 End If
 strSQL = "Select ID from autonum"
 set Rs = conDB.openrecordset(strsql,dbopensnapshot)
 'rs.movelast
 rs.movefirst
 mynum = rs(0)
 Set objPage = Item.GetInspector.ModifiedFormPages("General")   <~~~~~~~~~~~~ userdefine properties
 Set objControls = objPage.Controls                                  <~~~~~~~~~~~~ userdefine properties
 Set txtOrderID = objControls("txtOrderID")               <~~~~~~~~~~~~ userdefine properties
 if txtorderid.text = "0" then
   strSQL = "update autonum set id = id + 1"
   conDB.execute strSQL
   txtOrderID.text = mynum
 end if
End Sub

Function GetODBCConnection (ByVal MyDSN, ByVal MyConn, ByVal MyPrompt)
 Dim strUser
 Dim strPass
 Dim dbUseODBC
 'Must assign values to constants in VBScript
 dbUseODBC=1
 strUser=""
 strPass=""
 Set dbe= Item.Application.CreateObject("DAO.dbEngine.35")
 Set wrkODBC = dbe.CreateWorkspace("ODBCworkspace", strUser, strPass, dbUseODBC)
 dbe.Workspaces.Append wrkODBC
 Set conDB = wrkODBC.OpenConnection("Connection1", MyPrompt, , MyConn)
 GetODBCConnection = True
End Function

Function Item_Close()
 conDB.Close
 wrkODBC.Close
End Function

Then I was able to set my textbox to whatever value my autonum was.  Thank you all for your help.

Although All these comments and suggestions didn't answer what I needed, anyone out there that could improve my code will get the points.  If I don't receive any more comments, then I'll have to pick one of the above.

That's a pretty good suggestion, and since no one else gave a comment, I guess you won.