Solved

Connecting Outlook to SQL server

Posted on 2001-07-02
10
317 Views
Last Modified: 2012-08-13
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
Comment
Question by:nl4jy
10 Comments
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6246168
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
 
LVL 1

Author Comment

by:nl4jy
ID: 6246342
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
 
LVL 9

Expert Comment

by:rjcpjc
ID: 6246361
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
 
LVL 1

Author Comment

by:nl4jy
ID: 6249052
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
 
LVL 8

Expert Comment

by:jessnjeff
ID: 6250303
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Expert Comment

by:Homer1779
ID: 6255032
mmmm You hit problem at workspaces?? You do have Access Reference library ticked dont you???

cheers

George
0
 
LVL 1

Author Comment

by:nl4jy
ID: 6255856
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
 
LVL 1

Author Comment

by:nl4jy
ID: 6255872
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
 
LVL 9

Accepted Solution

by:
rjcpjc earned 200 total points
ID: 6295763
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
 
LVL 1

Author Comment

by:nl4jy
ID: 6298251
That's a pretty good suggestion, and since no one else gave a comment, I guess you won.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Use email signature images to promote corporate certifications and industry awards.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now