views:

58

answers:

2

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?

A: 
'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.

pghcpa
+1  A: 

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)
HansUp
$app.DoCmd.SelectObject(0,"sharepoint_table",1) was needed as well.
MattUebel