Solved

Connecting Outlook to SQL server

Posted on 2001-07-02
10
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Changing a few Outlook Options can help keep you organized!
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 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