Results 1 to 13 of 13
  1. #1
    cbjroms is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    4

    Outlook To SQL Via Access

    I have a email folder in Outlook that contains 100s of emails which record my discussions with a developer of some bespoke software. I want to import these into SQL to create a knowledge base of information that can be searched upon to extract all the decisions that we have made during the course of the 2 year project.



    Having sreached the net, I found that it is very easy to dump the contents of an email folder into Access using the import data functionality. In fact I have linked the table and so believe (never used Access before!!) that I now have an Access table that is connected in 'real-time' to the Outlook folder. This is eactly what I want BUT in SLQ as this is something that I am very familiar with using.

    So I have tried to import the Access database into SQL (which also appears to be relatively easy) but keep getting the message that 'The source database ...contains no visible tables or views'. Checking SQL pemissions, I am owner of this new databse.

    Two questions please. First, cant believe that going through Access is the simplest way to do this and presume that I will loose the 'real-time' link - am I right? Second, given that I can see my Access database has a visible table, why am I getting the error?

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    To answer you question a linked table doesn't reside "in" the database it is linked in.
    It actually is a table in a exchange (or similar) server somewhere.

    Do you run on on-premise Exchange server?
    If you do then you can connect your SQL server directly to it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    cbjroms is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    4
    Thanks Minty,

    We do not run an on-premises Exchange server, we use Office365. Can I connect SQL to that?

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You probably can, I suspect using an ODBC link (possibly something you have to pay for).

    https://automate.io/integration/office-365/sql-server

    CData also make an ODBC connector, both are pay for options though.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    cbjroms is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    4
    I am a litlle confused. If Access can connect to Outlook, cant SQL do the same? I have seen mention of an ODBC connector for sql - would this work?

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I suspect Access is only connecting to your local Outlook instance which wouldn't be available to the server.

    There are literally hundreds of ODBC connectors for SQL server, some are inbuilt or freely available, others cost 4 arms and 5 legs, depending on the perceived "value" of the data you are trying to get to.
    Each connector is specific to the source.

    As an example - Quickbooks which holds accounting information for your business, only let you get to your data through a couple of third party ODBC connector that is big $$$ because they know you'll want it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    cbjroms is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    4
    Thanks Minty,

    Yes, Access is only connecting to my local Outlook instance.

    But my local Outlook instance is on a PC which sits on the same network as the server which hosts SQL.

    If I were to create an Outlook instance on my server, would that be easly for SQL to connect-to?

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by cbjroms View Post
    Thanks Minty,

    Yes, Access is only connecting to my local Outlook instance.

    But my local Outlook instance is on a PC which sits on the same network as the server which hosts SQL.

    If I were to create an Outlook instance on my server, would that be easly for SQL to connect-to?
    That is a very interesting question, that I don't know the answer to I'm afraid.
    Hopefully someone else can provide more insight.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I don't know the prices but here you can find/buy SSIS components for outlook https://www.cdata.com/kb/tech/exchan...mport-2008.rst .

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035

  11. #11
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you setup your Access database as a linked server in SSMS?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Access as linked server? Even if it were possible with an ODBC driver I surely would not allow it on the SQL parc I'm responsable of. To much of a security risk.

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I'm not sure what the OP's security requirements are but here is some info on this:
    https://sqljana.wordpress.com/2016/1...at-the-errors/
    And some more from CData:
    https://www.cdata.com/kb/tech/access...ked-server.rst
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-12-2018, 05:38 AM
  2. Replies: 4
    Last Post: 02-19-2016, 03:13 PM
  3. Replies: 0
    Last Post: 12-15-2014, 08:18 AM
  4. Replies: 11
    Last Post: 05-20-2014, 11:32 AM
  5. Assigning Outlook task from shared Outlook mailbox
    By Remster in forum Programming
    Replies: 2
    Last Post: 11-16-2011, 04:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums