Results 1 to 3 of 3
  1. #1
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78

    Linked Outlook Table Slow/Crashes - Need a better option


    Issue: I have a database linked to a Shared Mailbox Outlook Sent Folder. The Sent Folder is small but the data in the entire mailbox is enormous. When I open the table linked to the folder, it will freeze, act funny, crash.... etc. So instead of open the table I would like to create a table from its contents and then work from it rather than the actual linked table.

    Note: the test shared sent items folder now only has 3 email items in it and it is perpetually crashing. When in production it will have 1000's of emails...

    I am currently using a CREATE TABLE statement as a work around. Can someone think of any better options?

    Code:
    SELECT * INTO NEWTABLE FROM tblDeliverableTracking;
    The purpose of the database is to track reports distributed from 9 employees throughout the month. These 9 employees could produce close to 1000 reports a month in total. I imagine I can use a SQL date filter to create a monthly table or append records to the table monthly......etc.

    Any other options are there? Right now the append monthly sounds the most logical....

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    can you make a query for the databale so it only opens a small set of the data instead of the entire data set?

  3. #3
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    I ended up using some code to export the Outlook Email Data to Excel. It was the fastest processing option.

    It beat out several things I considered:

    1. Make Table from Scratch (3 to 4 minutes)
    Code:
    SELECT * INTO NEWTABLE FROM tblDeliverableTracking;
    2. Link 9 Users Sent Items Folders.... (To Messy)
    3. Create a new Table using the wizard (3 to 4 minutes)

    Final Option.....

    Took 66 Seconds to process into excel using this Excel VBA Code ----> Then a simple append:

    Code:
    Sub ExportMailByFolder()
    
    'Note: Reference to Outlook Object Library is needed.   Outlook should also be open.
        Dim ns As Outlook.Namespace
        Dim objFolder As Outlook.MAPIFolder
        Dim intCounter As Integer
        Dim itmCounter As Integer
        Dim myTime As Double
        
        Set ns = GetNamespace("MAPI")
        Set objFolder = ns.PickFolder
        
        myTime = Timer
        
        Application.ScreenUpdating = False
        
        On Error Resume Next
        For itmCounter = objFolder.Items.Count To 1 Step -1
            
            With objFolder.Items(itmCounter)
        
                If .Class = olMail Then
                    
                    intCounter = intCounter + 1 'Increment Data Input Row
            
                    Cells(intCounter, 1) = .Subject
                    Cells(intCounter, 2) = .Body
                    Cells(intCounter, 3) = .SenderName
                    Cells(intCounter, 4) = .To
                    Cells(intCounter, 5) = .SenderEmailAddress
                    Cells(intCounter, 6) = .SenderEmailType
                    Cells(intCounter, 7) = .CC
                    Cells(intCounter, 8) = .BCC
                    Cells(intCounter, 9) = .Importance
                    Cells(intCounter, 10) = .Sensitivity
                    Cells(intCounter, 11) = .Sender.GetExchangeUser.PrimarySmtpAddress
                    
                    Rows(intCounter).WrapText = False
                    
                End If
        
            End With
        
        Next itmCounter
        On Error GoTo 0
        Application.ScreenUpdating = True
        
        MsgBox "This code ran successfully in " & Round(Timer - myTime, 2) & " seconds", vbInformation
        
        Set ns = Nothing
        Set objFolder = Nothing
    
    End Sub

    Didn't like the link to the database... it didn't seem stable. I feel like it would lead to database corruption.

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

Similar Threads

  1. Linked Outlook Table
    By Homegrownandy in forum Access
    Replies: 0
    Last Post: 03-08-2016, 09:31 AM
  2. Insert INTO MS SQL Linked table too slow
    By green78 in forum SQL Server
    Replies: 5
    Last Post: 05-28-2015, 09:33 AM
  3. Slow INSERT on a Linked SQL Table
    By altemir in forum SQL Server
    Replies: 5
    Last Post: 07-22-2013, 04:57 PM
  4. Replies: 3
    Last Post: 02-24-2012, 12:38 PM
  5. Slow linked table
    By Okidoo in forum Programming
    Replies: 0
    Last Post: 05-19-2011, 03:02 PM

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