• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Connecting Outlook to SQL server

I'm creating a form in Outlook that will connect to the SQL server to retreive a number in a table, then it update the table incrementally.  Is there a way to do this, like using some sort of store procedure?

Anyone that could provide a solution will receive 200+ points.

I have tried the following:

Function Item_Open()
 Dim dbEngine
 Dim winUser
 Dim dbs
 Dim myrs, ReturnValue
 Dim tmpstr, myRecipient, Sent_Flag

If (Item.Subject <> "") Then          'if Subject is filled
     Sent_Flag = True
Else
     Sent_Flag = False
End If

if Sent_Flag = False Then 'The message is being created
     Set dbEngine = Application.CreateObject("DAO.dbEngine.35")
      if err.Number <> 0 then
          msgbox Err.Description & Chr(13) & "Please check that DAO 3.5 is installed.",,"Database Lookup Failure"
          exit function
      end if
      Set dbs = dbEngine.workspaces(0).OpenDatabase("SMS", 0, 0, "ODBC;UID=sa;PWD=your_password;DATABASE=SMS;DSN=SMS")
      Set winUser = dbs.OpenRecordset("Select a.LogOn_Name0 from vIdentification a, vOperating_system b where a.dwMachineId = b.dwMachineId and b.Operating_System_Name0 = 'MS Windows 95' ", 1)
      if err.Number <> 0 then    
          msgbox Err.Description & Chr(13) & "Please check that DAO 3.5 is installed.",,"Database Lookup Failure"
          exit function
      end if
      If winUser.BOF And winUser.EOF Then
             'There are no records
             MsgBox "No records found"
      End If
     
      Do While Not winUser.EOF
          tmpstr=winUser.Fields(0)
          Set myRecipient = Item.Recipients.Add(tmpstr)
          winUser.MoveNext
      Loop
     item.subject = "Information related to SMS sent by "
     item.subject = item.subject & Application.GetNameSpace("MAPI").CurrentUser
End If
End Function

***Note: This is not my program, I only used this as a reference.  I have the problem at line: "set dbs = dbengine.workspace(0).opendatabase ..."

again, I just want to get one number from the table, and update that number incrementally.
0
nl4jy
Asked:
nl4jy
1 Solution
 
rjcpjcCommented:
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



0
 
nl4jyAuthor Commented:
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?
0
 
rjcpjcCommented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
nl4jyAuthor Commented:
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)
0
 
jessnjeffCommented:
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
0
 
Homer1779Commented:
mmmm You hit problem at workspaces?? You do have Access Reference library ticked dont you???

cheers

George
0
 
nl4jyAuthor Commented:
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.
0
 
nl4jyAuthor Commented:
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.
0
 
rjcpjcCommented:
Well, one suggestion I'd make is to change the following code:

Set objPage = Item.GetInspector.ModifiedFormPages("General")   <~~~~~~~~~~~~ userdefine properties
Set objControls = objPage.Controls                                  <~~~~~~~~~~~~ userdefine properties
Set txtOrderID = objControls("txtOrderID")              

You are getting the value from the control, not the field.  The field is where the value is actually stored.  So use something like this one line to replace those three:

Set txtORderID = Item.UserProperties.Find("txtOrderID")

Of course you need to make sure that your field is named txtOrderID.  That appears to be what your control is named, not sure what you named your field.

Other than that your code looks good to me.
0
 
nl4jyAuthor Commented:
That's a pretty good suggestion, and since no one else gave a comment, I guess you won.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now