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
Solved

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Find out what you should include to make the best professional email signature for your organization.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
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…

839 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