Somebody told me that Access has the capability to attach a table to Outlook, but they weren't sure how to do it. I was wondering if it has the same capability to attach a table to Lotus notes as the last function of a macro or even in a module. I am using Access 97. Does anybody have any ideas?

First, you need to downlaod:

Lotus NotesSQL 3.0.2e for Wintel32 US English
Windows 2000, Windows 95, Windows 98, Windows NT:

http://www.lotus.com/products/rnext....5?OpenDocument

This is the ODBC Driver needed to integrate Lotus Notes and Access. It may already be installed on your system. Check:

Start -> Administrative Tools -> Data Sources(ODBC)

The ODBC Data Source Administrator dialog should open. Click the "Drivers" tab and look for the "NotesSQL" driver.

Second, use the ODBC Data Source Administrator to Create a New Data Source (File DSN).

Third, go into Access:

File -> External Data -> Link Tables

In the Link dialog, navigate to C:\Program Files\Common Files\ODBC\Data Sources (the default folder in which ODBC Data Source files are stored)

In the Files of Type drop down list, select ODBC Databases()

The Select Data Source Dialog will appear listing your LotusNotes File Data Source. Select it.

A Link Tables dialog will appear listing the Notes tables you can link to.

I don't have Access to Lotus Notes at the moment, but I bet that'll get it for you.

HTH,

Bob

Here's somthing close to the code to do it (untested):


' Call CreateLinkedExternalTable("FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\LotusNotes.dsn","tblData")

Sub CreateLinkedExternalTable(ByVal strConnect As String, _
                              ByVal strSourceTbl As String, _
                              Optional ByVal strLinkTblName As String)


    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table

    Set catDB = New ADOX.Catalog
    catDB.ActiveConnection = CurrentProject.Connection

    If strLinkTblName = "" Then
        strLinkTblName = strSourceTbl
    End If

    Set tblLink = New ADOX.Table
    With tblLink
        .Name = strLinkTblName
        Set .ParentCatalog = catDB
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link DataSource") = strConnect
        .Properties("Jet OLEDB:Remote Table Name") = strSourceTbl
    End With

    With catDB.Tables
        On Error Resume Next
        .Append tblLink
        .Refresh
    End With
    Set catDB = Nothing
End Sub

A Full ODBC File DSN connection string that ADO can use looks like:

oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

HTH,

Bob