Updating a Sharepoint List via Access 2007

Topic

New Reply

Hi. While I have a lot of experience developing various types of Office solutions, I have one that is very new to me. I will be using SharePoint, which is a relatively new development environment for me. I’m using Office 2007 and (at least for now) working in Windows 7. The reason I mention Windows 7 is that i seem to be prompted to log in to the SP site frequently, while I’m rarely prompted to on my XP workstation.

First let me outline the steps:

[*]I have a SharePoint list that represents a cumulative list of Help Desk calls. (Technically, it’s a subset of ALL calls, based on category, but that’s probably irrelevant.)[*]I will periodically receive a CSV file containing up-to-date records. Many of these records may already be in the SharePoint list; I can’t assume they’ll all be new.[*]I need to add only the NEW records to the SharePoint list. Conceptually, this shouldn’t be a problem, because they each have a unique case ID.

What I expected to be able to do was to create a link to the SP list called “Regulatory” in Access, import the new CSV file into the TmpRegulatory table each month (replacing the previous table), and create an append query to add the new records. But for me, everything after the import of the CSV is frustratingly difficult to pull off!

For starters, while I can make a Select query that selects only new records, it doesn’t like it when I “convert” it to an append query. Honestly, I’m not even sure how the Select query works: I used the wizard, which created one that specified “IsNull” as the “WHERE”, because all the records have a value… but here it is anyway:
[indent]SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action]
FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
WHERE (((Regulatory.[Ref No]) Is Null));
[/indent]When I convert the SELECT query to an APPEND query, I get the error “Duplicate Output Destination ‘[RefNo]’. Here is the SQL for the APPEND:
[indent]INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action], [Ref No] )
SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action], Regulatory.[Ref No]
FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
WHERE (((Regulatory.[Ref No]) Is Null));
[/indent]

Any ideas where I’m going wrong? Can someone explain how IsNull collects only the new records in the SELECT in the first place? Any ideas on a better way to update my SP list with the new data from the CSV?

I really appreciate the help!

Reply | Quote

Replies

When I convert the SELECT query to an APPEND query, I get the error “Duplicate Output Destination ‘[RefNo]’. Here is the SQL for the APPEND:
[indent]INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action], [Ref No] )
SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action], Regulatory.[Ref No]
FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No]
WHERE (((Regulatory.[Ref No]) Is Null));
[/indent]

Any ideas where I’m going wrong? Can someone explain how IsNull collects only the new records in the SELECT in the first place? Any ideas on a better way to update my SP list with the new data from the CSV?

Duplicate Output Destination ‘[RefNo] If you read the field list in the Insert Into section you will see [ref no] is listed twice. The first and the last item. Delete the 2nd one, and the corresponding entry in the select area.

Code:

[font="Courier New"]INSERT INTO Regulatory ( [Ref No], [Date/Time Logged], [Event Type], [Affected User Name], [Item Name], Building, Category, Severity, [SVD Assigned], [Assigned User Name], [Last Action Taken], [Date Of Last Action])[/font]
[font="Courier New"]SELECT TmpRegulatory.[Ref No], TmpRegulatory.[Date/Time Logged], TmpRegulatory.[Event Type], TmpRegulatory.[Affected User Name], TmpRegulatory.[Item Name], TmpRegulatory.Building, TmpRegulatory.Category, TmpRegulatory.Severity, TmpRegulatory.[SVD Assigned], TmpRegulatory.[Assigned User Name], TmpRegulatory.[Last Action Taken], TmpRegulatory.[Date Of Last Action][/font]
[font="Courier New"]FROM TmpRegulatory LEFT JOIN Regulatory ON TmpRegulatory.[Ref No] = Regulatory.[Ref No][/font]
[font="Courier New"]WHERE (((Regulatory.[Ref No]) Is Null));[/font]

The original select query joins the two tables with a Left Join, so it includes all records from tmpRegularatory, whether or not they have a matching entry in Regulatory. Where they don’t have a matching entry in Regulatory, the[Ref no] field that should have come from Regulatory will be empty – Null.

Reply | Quote

Thank you so much for that insight! I feel ashamed that I didn’t pour over the actual SQL in detail; I assumed (hah!) that things would work exactly as described in the MS reference.

Reply | Quote