Refresh Sharepoint-linked Table in Access?

1
votes

I have an access table in 2007 that is linked to a sharepoint list. When a new record is added to the sharepoint list, the change does not get automatically reflected in the access table. If you right click on the linked table, there is an option to "refresh list" which does exactly as you would expect.

My question is how to perform this option programatically with powershell or something similar? (.netish)

ADDITION:

I have found that acCmdRefreshSharePointList exists as a way to execute the "refresh list" option. I am attempting to use it with powershell:

$app = New-Object -ComObject access.application
$app.OpenCurrentDatabase("C:\foo.accdb")

$acCmd = "acCmdRefreshSharePointList" 
$app.DoCmd.RunCommand($accCmd)

I would hope that that last line would cause the sharepoint-linked table to refresh. However, I receive the following error:

Exception calling "RunCommand" with "1" argument(s): "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))" At line:1 char:22 + $app.DoCmd.RunCommand <<<< ($acCmd) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

Can anybody shed light on what I could be doing wrong, or alternative methods of refreshing this list?

2
sharepointms-accesspowershellms-access-2007
That's odd that new records don't automatically show up in the linked table; in fact, it's sort of the point of linked tables in the first place. The only time I've had to refresh the link is when changes to the list structure (schema) have been made, like adding a new column. – CBono
Can you explain the role of Powershell and Access here? Are you programming in Access, or in Powershell? If the former, what does Access have to do with it? – David-W-Fenton
@CBono: in Access, it's not uncommon to need to Requery a form to show all the records, but that's not the case if you used the form to add the record. The question is very unclear on what platform is being used and how the record is being added and where the new records fail to appear. – David-W-Fenton
I have an access db that draws on sharepoint as its datasource for reporting and analysis. Powershell is serving as a method of automating the generation of these reports(to pdf), and then attaches them to an email and sends that email off. ftw – MattUebel
Problems? I call them good fodder for questions on SO :D – MattUebel

2 Answers

2
votes

You're loading the Powershell variable with a text string which is the name of an Access constant it doesn't know about. Try it with the constant's value instead:

$acCmd = 626
$app.DoCmd.RunCommand($accCmd)
0
votes
'Refresh Sharepoint table.
'Open recordset, requery and close recordset
On Error Resume Next
Debug.Print Now(), "Refreshing Sharepoint Main Menu"
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * from
    yourtablename")
DoCmd.Requery
DoEvents
rs.Close

Tried and true method I have had in production for over a year.