I have a routine that when executed in Excel 2010/Access 2010 works fine. But when I run the same routine on Excel 2003/Access 2003, I have a compile error at line DoCmd.RunCommand acCmdRefreshSharePointList: Variable not defined
The code as follows:
Function RefreshSharePointLinkToTable()
Dim dbs As Database
Set dbs = CurrentDb()
Dim exWB As Excel.Workbook
Dim exSheet As Excel.Worksheet
Dim AccountChoice As String
Dim excelark As Object
Dim sql As String
Dim rst As Object
Set excelark = GetObject(, "Excel.Application")
Set exWB = excelark.ActiveWorkbook
Set exSheet = exWB.Worksheets("Sheet1")
AccountChoice = exSheet.Cells(1,1).Value
sql = "SELECT * FROM [" & AccountChoice & "];"
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
If rst.Updatable Then
DoCmd.SelectObject acTable, AccountChoice, True
DoCmd.RunCommand acCmdRefreshSharePointList <<<< variable not defined
rst.Close
End If
Set dbs = Nothing
Set exSheet = Nothing
Set excelark = Nothing
Set exWB = Nothing
Set exSheet = Nothing
End Function
I step over the codes and the variable (.e.g. AccountChoice ) was correctly sourced. Would appreciate your help to debug the error.
Please note rst.Updatable returns True and Accountchoice exists as a list in SP ( say ListABC) as well as a linked table, ListABC in Access .
Don't know what is the significance of this value and why there is a difference.
>>
These acCmdWhatevers are constants that make it easier/more intuitive for you to program your runcommand statements. As you noted, that difference simply means that the functionality is not there in Access 2003. As I recall Access 2007 introduced features that made it a lot easier for developers to work with Share Point, and a lot more was added in Access 2010.
I personally do not work with SharePoint lists, so I am not real familiar with them. Does your share point list appear like a linked table in your Access database?
If so, an alternative might be to re-link your SharePoint list. This article has code for linking to SharePoint lists, specifically from Access 2003:
http://msdn.microsoft.com/en-us/library/office/aa662942(v=office.11).aspx