Solved

Access VBA - DoCmd.RunCommand acCmdRefreshSharePointList

Posted on 2013-01-13
13
2,110 Views
Last Modified: 2013-01-15
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 .
0
Comment
Question by:Russellbrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 38773426
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
0
 

Author Comment

by:Russellbrown
ID: 38773448
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
0
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 150 total points
ID: 38773479
so it is this
acCmdRefreshSharePointList
that is not declared do u know what should be its value ?
gowflow
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 38773507
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
0
 

Author Comment

by:Russellbrown
ID: 38773530
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.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38773723
Hv you tried putting it this way ? replace this line
DoCmd.RunCommand acCmdRefreshSharePointList        

by this line
DoCmd.RunCommand 626


gowflow
0
 

Author Comment

by:Russellbrown
ID: 38773931
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.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 38773984
<<
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
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38775398
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 listed and you can choose it,
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
0
 

Author Comment

by:Russellbrown
ID: 38777984
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.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 350 total points
ID: 38777996
So relinking is working okay for you?

To my understanding, that is the best you can do from Access 2003.
0
 

Author Comment

by:Russellbrown
ID: 38778096
Yes it works as an acceptable workaround and good to know that that is the  best one can do from Access 2003. Thank you.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38778109
OK
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question