Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: Auto-update Table From Outlook Contacts Folder

    I know it's possible to create a linked table in Access 2007, but does anyone know how to approach this problem with SQL Server 2005? I need the table to always have an updated table and only insert new records if it doesn't exist in the table.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to provide the MS-Access "linked table" functionality within SQL Server 2005. Because SQL Server offers so much more functionality, there are really many things that you need to evaluate based on your actual needs before you can make a decision.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2011
    Posts
    63
    I need a table to be in SQL Server and have that table 'linked' to that outlook folder. Since the outlook folder contains some bad data, I have a small stored procedure that will 'clean it up' and export it into another table.

  4. #4
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    There are many ways to provide the MS-Access "linked table" functionality within SQL Server 2005. Because SQL Server offers so much more functionality, there are really many things that you need to evaluate based on your actual needs before you can make a decision.

    -PatP
    You mind telling me a few of the 'many ways' SQL Server can do this? Thanks.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many options available, but I still don't clearly understand what you want.

    It seems like the source of your data is Microsoft Outlook. Do you want an Exchange Container, a public folder, a user folder, or something different to be the source of the data?

    You mentioned "bad data", but didn't explain what that meant to you. Is the data intact (in other words can you successfully view that data with any tool), or is it corrupt and currently unreadable? How does your stored procedure "clean it up" and export to another table?

    Does the data flow one way (into SQL Server), or does it flow back to the source? If it goes both ways, does it always do that or only under some conditions? How frequently does the data need to flow (real time, lagging by minutes, daily, or other)?

    I'm sorry that I'm brimming with questions, but what seems simple to you has many potential options that could add a lot to the complexity.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    There are many options available, but I still don't clearly understand what you want.

    It seems like the source of your data is Microsoft Outlook. Do you want an Exchange Container, a public folder, a user folder, or something different to be the source of the data?
    The source of the data is a public folder that everybody in the company shares. Below is the connection string for the linked table in Access

    Code:
    Outlook 9.0;MAPILEVEL=All Address Lists\;PROFILE=Outlook;TABLETYPE=1;TABLENAME=Salem Recipients;COLSETVERSION=12.0;DATABASE=C:\DOCUME~1\phamt\LOCALS~1\Temp\;TABLE=Salem Recipients
    Quote Originally Posted by Pat Phelan View Post
    You mentioned "bad data", but didn't explain what that meant to you. Is the data intact (in other words can you successfully view that data with any tool), or is it corrupt and currently unreadable? How does your stored procedure "clean it up" and export to another table?
    For now, I import certain columns from a spreadsheet into a temp table, remove some rows and then insert into the Employee table.

    Code:
    IF OBJECT_ID('tempdb..#XlsImport') IS NOT NULL 
    DROP TABLE #XlsImport 
    GO 
    
    CREATE TABLE #XlsImport 
    (
    First_Nm varchar(50),
    Last_Nm varchar (50),
    Phone varchar(50),
    Account varchar (50),
    Full_Name as (First_Nm+' '+ Last_Nm),
    Email as (First_Nm+'.'+ Last_Nm+'@company.com')
    )
    GO 
    
    select * from #XlsImport
    INSERT INTO #XlsImport  ( [First_Nm ], [Last_Nm], [Phone], [Account] )
    SELECT [First], [Last], [Phone],[Account]
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\Documents and Settings\phamt\My Documents\SalemRecipients.xls',
    'SELECT * FROM [Salem_Recipients]')
    
    --DELETE RECORDS WITHOUT A FIRST NAME OR LAST NAME
    DELETE FROM #XlsImport
    WHERE First_Nm is null or Last_Nm is null or Last_Nm LIKE '***%'
    
    INSERT INTO tbl_Employee (First_Nm, Last_Nm,Full_Name, OfficePhone, UserName, Email)
    SELECT First_Nm, Last_Nm,Full_Name,Phone,Account,Email FROM #XlsImport
    --SELECT*FROM tbl_Employee
    DROP TABLE #XlsImport
    GO
    Quote Originally Posted by Pat Phelan View Post
    Does the data flow one way (into SQL Server), or does it flow back to the source? If it goes both ways, does it always do that or only under some conditions? How frequently does the data need to flow (real time, lagging by minutes, daily, or other)?
    The data only needs to flow into SQL Server. I am assigning an employee ID to all employees so if an employees was added or removed, all the other employee IDs must remain the same.

    I hope I provided enough information. This seems to much more complicated than I hope.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •