Issues Integrating SharePoint List Data With Excel & Access

Experts, this is a long one, but I would appreciate someone helping me understand what is going on here...

About a year ago I developed a tool for my team to use to report quality opportunities.  The system uses MS Excel as a front end template to gather data about an issue and upload it to an Access DB, then I have a separate Excel tool that takes the newly added data from the database, and creates an HTML email and sends it to the other company locations that are owners of the reported issues.  The reason why my solution exclusively uses Excel and Access is because these are the only applications (MS Office) that are approved for use, and are standard on all of our companies PCs.  The excel tool used to gather the data not only collects text, but also allows the user to attach photo's and or PDF files to the issue.  These files are copied from the user's PC to a shared drive, and are available via hyperlink in the report that is sent out via email.

Recently I decided that I wanted to integrate this system into our companies SharePoint server, and host the reported items on a list that is accessible to all company users (we are a global company), as well as host the file attachments in a document repository.

The way I accomplished this was to link the SharePoint list to an Access Database, then use that database connection to perform SQL queries involving the SharePoint List.  This, however, is where I have began experiencing unexplained problems.

Due to the nature of my Excel data collection template, the data is primarily housed in a local Access Table, located in the same Access DB that the SharePoint List is linked to.  Each day, before the email is sent out, I run three SQL queries to update the SharePoint list with the goal of having it be an exact replica of the local access table.

The first query is designed to join the two tables using the item number (unique to each record) and delete any records from the SharePoint list that do not exist in the local access table.  I.E. Delete records from the SharePoint list that were deleted from the local table by a user.  (In the code below, the [FIELD_FEEDBACK] table is the local Access table, and the [Field Feedback Log] is the linked SharePoint List.)

    sqlString = "DELETE [Field Feedback Log].* " & _  
                "FROM [Field Feedback Log] LEFT JOIN [FIELD_FEEDBACK] " & _  
                    "ON [Field Feedback Log].[RECORD_ID] = [FIELD_FEEDBACK].[RECORD_ID] " & _  
                "WHERE [FIELD_FEEDBACK].[RESPONSIBLE_IATA] Is Null;"  
    CVGFFDB.Execute sqlString

Open in new window

The second query is designed to join the tables, and insert all records from the local table into the SharePoint List where the Record_ID doesn't already exist in the SharePoint List.  I.E. Add newly created records to the SharePoint list.

    sqlString = "INSERT INTO [Field Feedback Log] (RECORD_ID, ITEM_DATE, RESPONSIBLE_IATA, IATA_DESC, " & _  
                    "PHOTO5_LINK, CREATE_DTM, CREATE_USER, MOD_DTM, MOD_USER_V10) " & _  
                    "FIELD_FEEDBACK.MOD_DTM, FIELD_FEEDBACK.MOD_USER_V10 " & _  
                "FROM [Field Feedback Log] RIGHT JOIN FIELD_FEEDBACK ON [Field Feedback Log].RECORD_ID = FIELD_FEEDBACK.RECORD_ID " & _  
                "WHERE [Field Feedback Log].RECORD_ID Is Null;"  
    CVGFFDB.Execute sqlString

Open in new window

The third query again joins the table, and updates any records where the MOD Date is newer in the local table than it is in the SharePoint Site.  I.E. Update data that was modified.

    sqlString = "UPDATE [Field Feedback Log] INNER JOIN FIELD_FEEDBACK ON [Field Feedback Log].RECORD_ID = FIELD_FEEDBACK.RECORD_ID " & _  
                "SET [Field Feedback Log].[RESPONSIBLE_IATA] = [FIELD_FEEDBACK].[RESPONSIBLE_IATA], [Field Feedback Log].[IATA_DESC] = [FIELD_FEEDBACK].[IATA_DESC], " & _  
                    "[Field Feedback Log].[CTRY_REGION_NAME] = [FIELD_FEEDBACK].[CTRY_REGION_NAME], [Field Feedback Log].[CTRY_AREA_NAME] = [FIELD_FEEDBACK].[CTRY_AREA_NAME], " & _  
                    "[Field Feedback Log].[CTRY_NAME] = [FIELD_FEEDBACK].[CTRY_NAME], [Field Feedback Log].[REGION_NAME] = [FIELD_FEEDBACK].[REGION_NAME], " & _  
                    "[Field Feedback Log].[SUPER_REGION_NAME] = [FIELD_FEEDBACK].[SUPER_REGION_NAME], [Field Feedback Log].[REPORTED_BY] = [FIELD_FEEDBACK].[REPORTED_BY], " & _  
                    "[Field Feedback Log].[DTM_REPORTED] = [FIELD_FEEDBACK].[DTM_REPORTED], [Field Feedback Log].[ITEM_CATEGORY] = [FIELD_FEEDBACK].[ITEM_CATEGORY], " & _  
                    "[Field Feedback Log].[ITEM_DESC] = [FIELD_FEEDBACK].[ITEM_DESC], [Field Feedback Log].[PHOTO1_DESC] = [FIELD_FEEDBACK].[PHOTO1_DESC], " & _  
                    "[Field Feedback Log].[PHOTO1_PATH] = [FIELD_FEEDBACK].[PHOTO1_PATH], [Field Feedback Log].[PHOTO1_LINK] = [FIELD_FEEDBACK].[PHOTO1_LINK], " & _  
                    "[Field Feedback Log].[PHOTO2_DESC] = [FIELD_FEEDBACK].[PHOTO2_DESC], [Field Feedback Log].[PHOTO2_PATH] = [FIELD_FEEDBACK].[PHOTO2_PATH], " & _  
                    "[Field Feedback Log].[PHOTO2_LINK] = [FIELD_FEEDBACK].[PHOTO2_LINK], [Field Feedback Log].[PHOTO3_DESC] = [FIELD_FEEDBACK].[PHOTO3_DESC], " & _  
                    "[Field Feedback Log].[PHOTO3_PATH] = [FIELD_FEEDBACK].[PHOTO3_PATH], [Field Feedback Log].[PHOTO3_LINK] = [FIELD_FEEDBACK].[PHOTO3_LINK], " & _  
                    "[Field Feedback Log].[PHOTO4_DESC] = [FIELD_FEEDBACK].[PHOTO4_DESC], [Field Feedback Log].[PHOTO4_PATH] = [FIELD_FEEDBACK].[PHOTO4_PATH], " & _  
                    "[Field Feedback Log].[PHOTO4_LINK] = [FIELD_FEEDBACK].[PHOTO4_LINK], [Field Feedback Log].[PHOTO5_DESC] = [FIELD_FEEDBACK].[PHOTO5_DESC], " & _  
                    "[Field Feedback Log].[PHOTO5_PATH] = [FIELD_FEEDBACK].[PHOTO5_PATH], [Field Feedback Log].[PHOTO5_LINK] = [FIELD_FEEDBACK].[PHOTO5_LINK], " & _  
                    "[Field Feedback Log].[MOD_DTM] = [FIELD_FEEDBACK].[MOD_DTM], [Field Feedback Log].[MOD_USER_V10] = [FIELD_FEEDBACK].[MOD_USER_V10] " & _  
                "WHERE [Field Feedback Log].MOD_DTM<[FIELD_FEEDBACK].[MOD_DTM];"  
    CVGFFDB.Execute sqlString

Open in new window

Here's my problem:  When I run query #2, sometimes it works (it worked for two days or so as designed) but the last few days, it is inserting ALL records from the local DB into the SharePoint list, regardless if the RECORD_ID already exists in the table.  If I write a simple select query to simply select all records in the SharePoint list, the recordset has a recordcount of 0.

    sqlString = "SELECT [Field Feedback Log].* FROM [Field Feedback Log] ORDER BY RECORD_ID DESC , MOD_DTM DESC , ID;"  
    Set TempRS = CVGFFDB.OpenRecordset(sqlString, dbOpenDynaset)

Open in new window

When this is executed, the recordset "TempRS" shows a RecordCount of 0!  I have no idea why.  I can open the linked table in Access and see all 9000 records, so the table / list isn’t empty.  I believe without a doubt that this is why the #2 query above fails to only upload newly added records.

Does anyone know what I'm doing wrong here?  I really want this tool to work, as having everything on a SharePoint site is awesome and has promoted much better response from the folks we're asking to get involved.  However, there is clearly some issues that exist when using a SharePoint list in this manner.  What can I do to get it to work more consistently?
John ParkerService Quality ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Nick67Connect With a Mentor Commented:
I have an Excel toy for you.
It will pull a SharePoint list down to a new worksheet and an Access table down to another new worksheet.
Your Excel project requires a reference to MS DAO 3.6 Object Library to work.

You can perhaps now pull all the data down from the list and Access, alter it in Excel, kill the list, and push up the results as the list.
You might then be able to get away from using SQL at all, and do it all in Excel VBA which is your comfort zone.
It would then just all be range work

Option Explicit
'in an Excel VBA module

Sub ImportSharePointList()
'This baby will knock any SharePoint list, given a sitename and GUID for a list
'onto a new worksheet

Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
'I run SBS 2008, so my SharePoint site is simply 'http://ccompanyweb'
'Change to fit your scene
Const SERVER As String = "companyweb"
'this I got from linking a list to an Access DB
'Switching the resulting table to design view
'and looking at the properties
'change to suit your list
Const LISTNAME As String = "{8AECFB0B-D3E0-4E41-AD2C-FDFED0FC0B7C}"
Const VIEWNAME As String = ""

' The SharePoint server URL pointing to
' the SharePoint list to import into Excel.
strSPServer = "http://" & SERVER & "/_vti_bin"
' Add a new worksheet to the active workbook.
Set objWksheet = Worksheets.Add
' Add a list range to the newly created worksheet
' and populated it with the data from the SharePoint list.
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
    Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("a2"))

Set objMyList = Nothing
Set objWksheet = Nothing
End Sub

Sub GetSharePointRecordCount()
'this baby pulls a table from Access and throws it on a new sheet

Dim FieldFeedbackDB As DAO.Database
Dim TempRS As DAO.Recordset
Dim objWksheet As Worksheet
Dim sqlString As String
Dim x As Integer
Dim NumCols As Long
Dim NumFields As Long

'my DB is here, change to suit yours
Const DatabasePath As String = "m:\dev\TI_Prog"
Const MyPassword As String = "**********"

'Open Database
Set FieldFeedbackDB = OpenDatabase(DatabasePath) 'there are other switches, but my scene doesn't need them
'yours may

'tblInsDetails is my big table, change to suit your scene.
'Big as in 63 columns, 43000+ rows.
'Comes downhill in < 45 seconds

sqlString = "SELECT * FROM tblInsDetails;"
Set TempRS = FieldFeedbackDB.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If TempRS.RecordCount <> 0 Then
    Set objWksheet = Worksheets.Add
    'add, bold fieldnames in column 1
    For x = 0 To TempRS.Fields.Count - 1
        With objWksheet.Cells(1, x + 1)
            .Value = TempRS.Fields(x).Name
            .Font.Bold = True
        End With
    Next x
    'nail the table in row 2 and down
    With objWksheet.Range("a2") '.Resize(TempRS.Fields.Count, TempRS.RecordCount)
        .CopyFromRecordset TempRS
    End With

    'autofit everything
    With objWksheet.Range(Cells(1, 1), Cells(TempRS.RecordCount, TempRS.Fields.Count))
    End With

End If

'clean up
Set TempRS = Nothing

Set FieldFeedbackDB = Nothing
End Sub

Open in new window

I was in on the Access 2010 pre-beta discussion.  The maximum number of rows that an Access table connected to a SharePoint list was discussed at being 50,000.  I asked what the upgrade path was from SharePoint to something larger.  I was told there was none.

Those limits may have grown, but I'd be very wary if you have 9000 records already

Danger Will Robinson!
Beyond this, I have nothing else to offer.

John ParkerService Quality ManagerAuthor Commented:
Nick, thanks for the comment.  This table should only be getting about 100 new records per week, so the size limitation isn't a concern here (normal size right now should be 3200 records... its over 9k because the insert query above is duplicating all of the records.)  However, I was scoping out another integration project that would be dealing with a list that is well over the 50,000 limit, so you probably saved me a lot of wasted time and energy, and for that I thank you!
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

No Problem.

Where are you executing these queries from?
Access, Excel or SharePoint?

The first troubleshooting step in SQL syntax is to get a Select query working correctly
Take this part of Query2

                    "FIELD_FEEDBACK.MOD_DTM, FIELD_FEEDBACK.MOD_USER_V10 " & _  
                "FROM [Field Feedback Log] RIGHT JOIN FIELD_FEEDBACK ON [Field Feedback Log].RECORD_ID = FIELD_FEEDBACK.RECORD_ID " & _  
                "WHERE [Field Feedback Log].RECORD_ID Is Null;"

And get it working properly as a select query in Access.  That may point you in the direction of why you are getting too many records appended.

While you are playing in Access, you may want to try a subquery instead of a right join and is null.  For the sake of brevity I'll just say FIELD_FEEDBACK.* , but you get the idea

 FIELD_FEEDBACK.* from FIELD_FEEDBACK where FIELD_FEEDBACK.RECORD_ID not in (select  [Field Feedback Log].RECORD_ID from  [Field Feedback Log])

This gets away from the right join, may perform better, and pulls less data.

SharePoint isn't my thing -- but SQL syntax is SQL syntax
John ParkerService Quality ManagerAuthor Commented:
Thanks for the tip, Nick.  I will try it out.  I'm executing the queries from a VBA routine behind Excel.  I'm using the DAO reference library as the connection method to the Access DB.  Admittedly SQL isn't my strong point (I’m sure you could tell that from my queries above) and I'll try your suggested method and see if I can get it to dance for me.  The frustrating though is that I can just execute the simplest Select Query ("SELECT * FROM [FIELD FEEDBACK LOG];") and execute it, and the Record Set will return zero records.  That's what's driving me crazy...  To that end, I have doubts that your suggested method will have a different result, but it is probably the better way to go regardless.

I really need to figure out why the simple Select query above fails sometimes.  If I can figure that out, then I think I've figured out the whole thing.
I'm an Access guy.  You know the old saw about how a man who has a hammer sees every problem as a nail.

From Access, you'll be able to see your linked table -- or it will go bang! if something else has an exclusive lock on it.  From Excel, using a connection string, I'm not sure what will happen if the table/list isn't available.  Because that may be what is happening.

The Access query editor is also a thing of joy to use, and beats the daylights out of trying to hack together SQL as text.  When you get it working just right, switch your Access query to SQL view and copy-and-paste.  Much happier workflow :)
This was missing SELECT

FIELD_FEEDBACK.* from FIELD_FEEDBACK where FIELD_FEEDBACK.RECORD_ID not in (select  [Field Feedback Log].RECORD_ID from  [Field Feedback Log])

should be

SELECT FIELD_FEEDBACK.* from FIELD_FEEDBACK where FIELD_FEEDBACK.RECORD_ID not in (select  [Field Feedback Log].RECORD_ID from  [Field Feedback Log])

I've uploaded a stub Access database with queries.  You can either create linked tables ( to your local Access database table and SharePoint list) in it and test the queries, or import the queries to your local Access database and test them there.
John ParkerService Quality ManagerAuthor Commented:
Cool!  Thanks Nick.  I will test this out first chance I get.
I see you've posted another question, and I'll leave that one alone.
When your super simple select query fails from Excel VBA, does a similar query also fail in Access?

If they both fail (Excel/Access), then you've got so sort of SharePoint issue
If the Excel one fails, but the Access one succeeds, you've got some sort of ADO/DAO connection string issue.

Let me know
Looking at the other question, too, the code you posted shows that you are getting Excel to link to the SharePoint data through the Access db.
Is the failure dependent on the open/close/open exclusive state of the Access db?

Can you get Excel to query the SharePoint data *without* involving Access as a middle man?
John ParkerService Quality ManagerAuthor Commented:
Yes, I posted a separate question to try to focus on what I perceive to be the root cause of the issue.  I figured this question would probably have someones eyes glazed over by the time we get to the part I beleive to be the primary issue.

Regarding the connection from excel to access to sharepoint...  I don't know of a was to connect directly to the sharepoint and accomplish the same things I need to do with the way it's configured now...  I know there are ways to link the list into excel, but I'm really using excel only for it's ability to schedule tasks (using the OnTime method.)  The excel program really only sits there to execute on the schedule I want and run the code that pulls the report together and emails it out.  I'm a little embarrased to admit that I'm completely self taught here, so I'm sure there are better ways to do this, but I'm working within what I know right now, which is excel VBA, and some integration with access...
Not a problem.
The second posting was good--you've got to attract someone from the SharePoint side of things.
You use the tools you've got.  if Excel is it, then so be it :)
I am mostly self-taught too.

How much Access stuff have you done, then?
If you need help to import those queries posted and get started with the Access Query Editor, just to test where your grief lies, I can do that.
John ParkerService Quality ManagerAuthor Commented:
Thanks.  I know access relatively well as an application.  I've never done anything with the forms, etc, but I know the tables and queries pretty well.  I've coded in Access a little, but not a ton.  I can do most anything I need to do through the front end...

Regarding your suggestions so far, unfortunately (or fortunately), the queries are working correctly today, so I can’t trouble shoot the errors!  That's why it's frustrating... some days it works, others it doesn't.  Maybe tomorrow it will fail again, and I can try out some of your suggestions.

Thanks again for all of your help.
You may want to have a look at

In Access, if you throw your SharePoint list link table into design view and choose properties, you can get a look at the connection string.
See the attached image.
Creating a connection to the Shared Documents list on my SBS 2008 companyweb gives
WSS;HDR=NO;IMEX=2;DATABASE=http://companyweb;LIST={5A9D7734-3FFD-40AF-B009-766AF59EB474};VIEW={CB22589D-CC04-4B0F-89CC-BEB84FE6E0E2};RetrieveIds=Yes;TABLE=Shared Documents: All Documents

With some experimenting you should be able to get Excel VBA to see the list independent of Access--which would be another tool in the box to see where things are going south
John ParkerService Quality ManagerAuthor Commented:
Nick, thanks again as always for the help.  The only issue I would have with connecting directly to the SharePoint list outside of the access DB would be how to execute the synchronization queries I have between the access table and the SharePoint list?  Is there a way to perform queries on tables from two different data sources like that?  I've never really dug into it.
Data is data.

If you can make the connections, you can do whatever you'd need.
I am suggesting troubleshooting steps only, what you actually do with your app afterwards will be up to you.
If it were me, I'd move all the VBA into Access because that's my tool.
Excel --> rifle
Access --> M1A2 Abrams tank
but that's me.

The behavior of your second query, that it appends too may records, suggests that SOMETHING, who knows what, is making the app where you execute it think that the SharePoint table is empty.  You're still accessing it--because the append happens--but the WHERE clause isn't functioning correctly.

Now, you can open the linked list in Access and see all the records.
The question becomes "where is the point of failure?"

Poke at the guts of your WHERE
If the same SQL in Access and Excel returns different results, then something in the Excel connection setting might be hosed.
If the same SQL returns the same results, both bad, then maybe the SQL syntax is hosed
--not likely given that it works right some of the time--

If the SQL and the connection settings get ruled out, then it's time to look at co-incidences.
-->failure happens when multiple users have Access open
-->Failure happens when something Excel related is occuring
--> failure happens when x number of users have the SharePoint list open
--> failure happens when certain SharePoint workflows are underway
-->failure happens when a table gets over a certain size
--> failure happens when...who knows.

Troubleshooting always happens in a volume
Likelyhood of success on one axis
Ease of action on another
Cost of action on a third

You start at likely, easy and cheap and work outward to unlikely, difficult and expensive.
You change a single thing at a time and look for correlations.

Is everything working good today?
John ParkerService Quality ManagerAuthor Commented:

Sorry for not responding for almost two weeks!  My work priorities list got "blowed up" with a plateful of super hot projects.  I will check your Excel tool out and see how well it would work in my task here... sounds like it definately has potential!  Thanks again for all you help on this.

Not a problem.
Life happens :)
We are still working on it
John ParkerService Quality ManagerAuthor Commented:
@teylyn, yes, nick and I are still working on this... Unfortunately due to other demands, I've had to push this issue to the bottom of my priority list, and have not been able to test the ideas Nick provided.  What would be the most appropriate way to proceed?  Can I award Nick the points for being so helpful and patient?  Can we leave the question open for now?  I don't want to abuse the system, but I also don't want to ignore the fact that Nick has worked hard to help me find a solution.  Thanks,

TracyVBA DeveloperCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Please don't delete this question.
We've worked hard on it and possible resolution, but the author hasn't been able to test them.
I'd be unhappy if all the documentation disapperared
John ParkerService Quality ManagerAuthor Commented:

I will try to make time to test your solutions tonight... sorry for this getting pushed so far to the back burner.  I want you to get credit for the work you did.


Hey John,

How's it going.

Your once quarterly ping!
Hey John,

How's it going.

John ParkerService Quality ManagerAuthor Commented:
I wanted to close this question with some sincere apologies.  Nick, first thanks for all of your help and patience.  When I originally asked this question, I was hoping that someone had already dealt with this and would know the issue.  I wasn't prepared to spend additional time troubleshooting or recreating the solution.  My intention was never to abuse your goodness to help... I just wanted to understand what the problem might be and get a quick fix in place if one existed.  Since no one seemed to know what was causing the issue (certainly understandable as it was a very weird issue) I need to band-aid the process and move on.  However, I felt obligated to investigate the proposed solutions but time never was available to do so, and now I feel like a jerk for not having the time.  :-)

What I ended up doing to address the problem was to attempt the DAO connection to the SharePoint database, and if the initial query (to see how many records exist on the SharePoint site) failed, I stopped the attempt to update, and simply tried again in 15 minutes.  I continue this until the update succeeds, which is usually within a couple of hours.  Purely a work-around, and I still have no idea what causes the root problem, but it's been working for me for the last 18 months, so I'm writing the issue off as unsolvable and have moved on.

Nick, I want to award you the points for the question for all of your work on it.  Sorry I strung you along, but I honestly didn't know how to deal with the situation given my work-load.  I swear I'm not a jerk... :-)

Thanks for all of your help.

No Biggie!

My military inlaws call that OBE
Overtaken by Events.

If you ever get to a point where you can tackle the root issue, this question will still exist for reference--and to me that is the important thing.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.