Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Connecting Outlook to SQL server

Posted on 2001-07-02
10
Medium Priority
?
340 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

783 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