Link Access to specific Outlook folder using VBA

Your requirement is not very clear, could you please elaborate what do you need?

Do you have some code that you have already written and its not working?

Thanks,
Sudeep

No I have no code. I would like to be able to link to, for example, my Inbox in Outlook via Access. Although I have it working by using "External Data" I would like to be able to do this using VBA code. As I imagine, it will be similar to linking to external tables, such as SQL Server. I don't wish to have a copy of it, I require a dynamic link.

You mean just create the link or do you want to work directly with the folders from VBA?

 For creating a link, you would create a tabledef object with VBA.   For the other (controlling Outlook via Automation), you would need to buy a 3rd party tool, such as vbMAPI:

https://www.everythingaccess.com/vbmapi.asp

 or Outlook redemption:

http://www.dimastr.com/redemption/home.htm

 To work with the object model and avoid the security prompts.  I use vbMAPI myself for interacting with Outlook.

Jim.

Jim
Thanks - what I am looking to do is to be able to link to Outlook contacts within an Access database. So when a Contact is added in Outlook, it will appear in my linked Access table. I am doing this currently using "External Data" / "New Data Source" / "From Other Sources" / "Outlook Folder" in Access. But to install this on a few PCs, I would rather do this via VBA, otherwise I'll need to install the link on each PC individually.

Is this possible?

<<Is this possible?>>

 Yes.   You create a linked table by creating a tabledef object.  Each entry in the "tables" section of the database container is a tabledef object.

  Access can tell the type of table by that objects connect property.   For internal tables, the property is blank.  For external ones, the connect  property tells the database engine how to reach the data.

 Since you already have a linked table, do this in the Debug Window (Ctrl/G in the VBA editor):

 Debug.Print CurrentDB().tabledefs("mytablename").Connect

 and hit return.   This is the connect string you will need to use to reach the contacts.

 To create a tabledef(), you would do something along the lines of this:

Public Function CreateLinkTable(strLinkedTableName As String, strRemoteTable As String, strConnect As String) As integer

    Dim db as DAO.Database
    Dim tdf As New DAO.TableDef

    Set db = CurrentDb()

    db.TableDefs.Refresh

    Set tdf = db.CreateTableDef(strLinkedTableName)
    tdf.Connect = strConnect 
    tdf.SourceTableName = strRemoteTable 
    db.TableDefs.Append tdf
    db.TableDefs.Refresh

    Set tdf = Nothing

    Set db = Nothing

End Function

Select allOpen in new window

Jim
OK - I mostly understand that thanks. But the code you listed needs 3 parameters passed:
strLinkedTableName As String
strRemoteTable As String
strConnect As String

Which values do I put into these?

Jim
Excellent advice as usual. Many thanks, and I did notice the rogue "End With" before I used it!