Accessing Ms-Access vide Outlook'97 Client !!!

Posted on 1998-11-25
Medium Priority
Last Modified: 2010-04-08
I have just set up the Ms-Exchange(ver 5.0) environment.How do i access MS Access or SQL Server from Outlook'97 client.
I have tried doing this from Exchange Client using Exchange Forms Designer.But when i accessed the MS Access Database from "Visual Basic for Exchange Server",it gave me an compilation error saying "User Defined type not identified".
So i am now trying this from Outlook '97.
Question by:ashkal
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
  • 2

Author Comment

ID: 1622018
Thankx in advance ! ! !

Accepted Solution

chewhoung earned 400 total points
ID: 1622019
To connect to database you need to use ODBC. In WIN 95 control panel ODBC icon, under User DSN, create a User Data Source base on access mdb. In my case I, I created a User DSN name Leave. You can see this LEAVE in my code.

The following is the code I use:

'Force explicit variable declaration
Option Explicit

'Script level declarations
'Remember that in VBScript the AS keyword and typed variables
'are not allowed. All variables are typed as variant by default.
Dim dbe
Dim wrkODBC
Dim conDB
Dim Rs
Dim gstrAppName
Dim IsLoading
Dim MyArray
Dim MyArrayPO
Dim myOlApp
Dim myNameSpace
Dim myFolder
Dim myDeletedFolder
Dim txtManager

'DAO Constants
'Remember that DAO constants must be declared with Const statement.
'The Const statement is only available in VBScript 2.0 or above.
'They cannot be called directly as you would in VB or VBA.
Const dbUseODBC=1
Const dbDriverComplete = 0
Const dbDriverNoPrompt = 1
Const dbDriverPrompt = 2
Const dbDriverCompleteRequired = 3
Const dbOpenTable = 1
Const dbOpenDynaset = 2
Const dbOpenSnapshot = 4
Const dbOpenForwardOnly = 8
Const dbOpenDynamic = 16
Const dbAppendOnly = 8
Const dbConsistent = 32
Const dbDenyRead = 2
Const dbDenyWrite = 1
Const dbExecDirect = 2048
Const dbFailOnError = 128
Const dbForwardOnly = 256
Const dbInconsistent = 16
Const dbReadOnly = 4
Const dbRunAsync = 1024
Const dbSeeChanges = 512
Const dbSQLPassThrough = 64
Const olFolderInbox = 6
Const olFolderSentMail = 5
Const olFolderDeletedItems = 3

Sub Item_Open()
'Dim procedure variables.
Dim curExtension
Dim lngOrderID
Dim i
Dim strSQL

'Use the GetODBCConnection function to establish an ODBC Connection
If Not (GetODBCConnection("LEAVE","ODBC;DSN=LEAVE", dbDriverCompleteRequired)) Then
      Exit Sub
End If

strSQL = "Select Name "
strSQL = strSQL & " from tEmployees where EmployeeID = 'AAAA';"
Set RS = conDB.OpenRecordset(strSQL, dbOpenSnapshot)
'Must move last to find RecordCount-Bug in Access ODBC
'This technique not required on SQL Server connection
If RS.RecordCount Then
      txtManager = RS(0)
End If

End Sub

'* Procedure:        GetODBCConnection (ByVal MyDSN, ByVal MyConn, ByVal MyPrompt)
'* Description:       This procedure establishes an ODBCDirect connection object
'*                  which can be used to open recordsets and execute SQL code.
'* Arguments:      MyDSN = A valid User DSN.
'*                  MyConn = A valid ODBC connect property string.
'*                  Example: "ODBC;DSN=Nwind" or "ODBC;DSN=Pubs"                  
'*                  MyPrompt = A valid option Value specifying ODBC driver
'*                  prompt options.
'*                  Note: The options argument determines if and when to prompt
'*                  the user to establish the connection.
'* Returns:            TRUE if successful; FALSE if connection fails.

Function GetODBCConnection (ByVal MyDSN, ByVal MyConn, ByVal MyPrompt)
Dim strUser
Dim strPass
'Turn on error trappping
On Error Resume Next
'Set to defaults-change these Values if required
strUser = "chew"
strPass = ""
'Create a DAO object. You must use DAO.dbEngine.35 or you will cause
'a page fault on machines with both DAO 3 and DAO 3.5 installed.
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
If Err.Number <> 0 Then
          Msgbox "Error#: " & err.number & chr(13) & err.description & chr(13) _
       & "Warning -- Could not create DAO 3.5 Object!" & Chr(13) _
      & "Please make sure that DAO 3.5 is installed on this machine!", _
      vbCritical, gstrAppName
      GetODBCConnection = False
          Exit Function
End If

'Create an ODBCDirect Workspace
Set wrkODBC = dbe.CreateWorkspace("ODBCWorkspace", strUser , strPass , dbUseODBC)
'Set wrkODBC = dbe.CreateWorkspace(olNameSpace.CurrentUser.Name, strUser , strPass , dbUseODBC)

If Err.Number <> 0 Then
          Msgbox "Error#: " & err.number & chr(13) & err.description & chr(13) _
      & "Warning -- Could not create ODBC workspace!" & chr(13) _
      & "Please make sure that user name and password are correct.", _
      vbCritical, gstrAppName
      GetODBCConnection = False
          Exit Function
End If
dbe.Workspaces.Append wrkODBC
'Establish the connection to DSN

Set conDB = wrkODBC.OpenConnection("Connection1", MyPrompt, , MyConn)
'Set conDB = wrkODBC.OpenConnection(olNameSpace.CurrentUser.Name, MyPrompt, , MyConn)
If Err.Number <> 0 Then
          Msgbox "Error#: " & err.number & chr(13) & err.description & chr(13) _
      & "Warning -- Could not create connection to " & MyDSN & "!" & chr(13) _
      & "Please make sure that " & MyDSN & " is a valid DSN!", _
      vbCritical, gstrAppName
      GetODBCConnection = False
          Exit Function
End If

GetODBCConnection = True
End Function


Author Comment

ID: 1622020
Hi chewhoung's
Thanks a ton for your prompt reply.I will be posting more queries as i tend to learn more of
MS Exchange.Looking forward to your timely help.

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

762 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