Solved

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

Posted on 1998-11-25
3
236 Views
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.
0
Comment
Question by:ashkal
  • 2
3 Comments
 

Author Comment

by:ashkal
ID: 1622018
Thankx in advance ! ! !
0
 
LVL 5

Accepted Solution

by:
chewhoung earned 100 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.
'******************************************************************************
'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
 

Author Comment

by:ashkal
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.
Regards,
Ashwin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
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…
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…

910 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

21 Experts available now in Live!

Get 1:1 Help Now