Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Access VBA - DoCmd.RunCommand acCmdRefreshSharePointList
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
The code as follows:
Function RefreshSharePointLinkToTab
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
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 .
The code as follows:
Function RefreshSharePointLinkToTab
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
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 .
Who is Participating?
your vba code exist in Access ? as in Excel the DoCmd does not exist.
OOPS revewing my post.
If your using Excel just add a reference to the Microsoft Access Library in your project and that should be ok.
gowflow
OOPS revewing my post.
If your using Excel just add a reference to the Microsoft Access Library in your project and that should be ok.
gowflow
Hi,
In Excel 2003, MS Access 11.0 Object Library is already in the VBA>Tools>References and in Access 2003, MS Excel 11.0 Object Library as well.
R
In Excel 2003, MS Access 11.0 Object Library is already in the VBA>Tools>References and in Access 2003, MS Excel 11.0 Object Library as well.
R
so it is this
acCmdRefreshSharePointList
that is not declared do u know what should be its value ?
gowflow
acCmdRefreshSharePointList
that is not declared do u know what should be its value ?
gowflow
well don't hv all your variable when I run it the first line CurrentDB() is obviously set to nothing here so can't go further. Anything more you can supply to help troubleshoot this would be a plus.
gowflow
gowflow
in Excel 2010 which is working fine, it is 626. In Excel 2003 it is empty/null. Don't know what is the significance of this value and why there is a difference.
Hv you tried putting it this way ? replace this line
DoCmd.RunCommand acCmdRefreshSharePointList
by this line
DoCmd.RunCommand 626
gowflow
DoCmd.RunCommand acCmdRefreshSharePointList
by this line
DoCmd.RunCommand 626
gowflow
I recd an error message: The RunCommand action was cancelled. Err Number 2501.
The DoCmd.RunCommand 626 works only for Access 2010 and not for Access 2003. Do you have an equivalent code? Thanks.
The DoCmd.RunCommand 626 works only for Access 2010 and not for Access 2003. Do you have an equivalent code? Thanks.
Honestly I don't know what to say as never used share point either so I guess good luck !
What I found is that in Excel 2003 when you add the Access library although you get in the snipset list the property acCmdRefreshSharePointList
however what is intriguing is that when you lookup help and check all the commands that are listed you do not find this command. So maybe this leads to the conclusion that this command was not available in 2003 but unfortunately I cannot confirm this statement nor deny it as no pervious experience with share point.
gowflow
What I found is that in Excel 2003 when you add the Access library although you get in the snipset list the property acCmdRefreshSharePointList
however what is intriguing is that when you lookup help and check all the commands that are listed you do not find this command. So maybe this leads to the conclusion that this command was not available in 2003 but unfortunately I cannot confirm this statement nor deny it as no pervious experience with share point.
gowflow
Hi both,
<<Does your share point list appear like a linked table in your Access database?>> Yes (orange icon )
Thank you for the heads-up. I have a routine ( although that was not the original purpose ) that re-links to the Share Point list - as part of the error handling step. It is alright as a workaround since Access 2003 does not have a feature to refresh the link using VBA.
<<Does your share point list appear like a linked table in your Access database?>> Yes (orange icon )
Thank you for the heads-up. I have a routine ( although that was not the original purpose ) that re-links to the Share Point list - as part of the error handling step. It is alright as a workaround since Access 2003 does not have a feature to refresh the link using VBA.
So relinking is working okay for you?
To my understanding, that is the best you can do from Access 2003.
To my understanding, that is the best you can do from Access 2003.
Yes it works as an acceptable workaround and good to know that that is the best one can do from Access 2003. Thank you.
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.
-
Course of the Month4 days, 3 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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