Solved

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

Posted on 1998-11-25
3
242 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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…
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…
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…

773 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