[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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

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.
0
ashkal
Asked:
ashkal
  • 2
1 Solution
 
ashkalAuthor Commented:
Thankx in advance ! ! !
0
 
chewhoungCommented:
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.
'******************************************************************************
'WorkspaceTypeEnum
Const dbUseODBC=1
'DriverPromptEnum
Const dbDriverComplete = 0
Const dbDriverNoPrompt = 1
Const dbDriverPrompt = 2
Const dbDriverCompleteRequired = 3
'RecordsetTypeEnum
Const dbOpenTable = 1
Const dbOpenDynaset = 2
Const dbOpenSnapshot = 4
Const dbOpenForwardOnly = 8
Const dbOpenDynamic = 16
'RecordsetOptionEnum
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
RS.MoveLast
RS.MoveFirst
If RS.RecordCount Then
      txtManager = RS(0)
      RS.Close
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


0
 
ashkalAuthor Commented:
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.
Regards,
Ashwin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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