working with sharepoint via vba

is49460 · November 26th, 2010, 01:42 PM

Hello,

I'm trying execute some basic commands working with web database, such as:

Application.RunCommand acCmdToggleCacheListData

or

Application.RunCommand acCmdToggleOffline

I get the following error message:
run-time error 2046
the command or action 'ToggleCacheListData' isn't available now.
or
the command or action 'ToggleOffline' isn't available now, respectively.

Any suggestions how to make it work?
Thanks!!

gjgriffith · February 18th, 2011, 05:35 AM

Hello is49460,

I saw your note here from a while back now and I'm sorry I had not responded earlier! Actually, I think the problem you are seeing is because you are trying to run that code from the current instance of Access, which requires the database to be closed and reopened to toggle online/offline mode, which would cause the code in the current database to stop executing (I know is seems kinda of strange).

So, to make this work, you need to create a new instance of Access and open the database in the new instance and then call the RunCommand to toggle online/offline...and of course, the database cannot be opened in any other instance of Access when you do that.

Anyway, I hope this makes sense. We actually discuss these topics (and much more about SharePoint) in:
1. Access 2010 Programmer's Reference - Chapter 19 - Working with SharePoint
2. Access 2007 VBA Programmer's Reference - Chapter 17 - Working with SharePoint

So, hopefully, that helps you out, but if you have more questions, please let me know and I'll do what I can to help!

Good Luck!

Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

is49460 · February 21st, 2011, 12:29 PM

Thank you for your reply!
I was able to make those functions work by disabling 2010 caching mode.

is49460 · February 21st, 2011, 12:32 PM

I'll look through chapter 19 again though. Originally I couldn't find any references about it... thanks again!

gjgriffith · February 21st, 2011, 01:01 PM

Glad to hear that you resolved the issue. Access 2010 provides some really nice features for working with SharePoint, hopefully Chapter 19 in the Access 2010 Programmer's Reference will be helpful! And if I can do anything to help, just let me know.

Thanks,

Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

Nauticalgent · August 24th, 2017, 04:47 AM

Quote:

Originally Posted by gjgriffith

So, to make this work, you need to create a new instance of Access and open the database in the new instance and then call the RunCommand to toggle online/offline...and of course, the database cannot be opened in any other instance of Access when you do that.

I realize I am a little late to the party and hopefully some of the party-goers are still awake...

When I attempted to do this procedure some curious things happened. I am using Access 2010 and SP 2007.

Here is my code:

Code:

Option Compare Database
Option Explicit

Dim appAccess As Access.Application

Sub ToggleSharePoint()
On Error GoTo err_handler

    Dim strDB As String
    Const strConPath = "C:\Test_DB\"
    strDB = strConPath & "myDB.accdb"
    
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strDB
    
    Access.RunCommand (Access.AcCommand.acCmdToggleOffline)
    
    MsgBox "Done"
    
exit_handler:
    Exit Sub
    
err_handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume exit_handler
    

End Sub

I am able to run this sub from the immediate window and it works flawlessly. I can run Access.RunCommand (Access.AcCommand.acCmdToggleOffline) from the IW as well and get the same results.

However, when I attempt to do this from a Form's Command Button, I get the same error as the OP, which was addressed already.

I changed to procedure from and Sub to a Function and tried to run it at startup as part of the AutoExec Macro...and that is when the funky stuff started...

My application started opening multiple times in an infinite loop - about 100 instances until I was able to stop it. What is more, not once did I get my MsgBox "Done" to assure me the code had worked...AND...no errors were thrown.

I am at a loss on
1. Why the code isn't working
2. How to toggle SP on and off with VBA

I purchased the A2010 Programmer's Reference and read and re-read chapter 19 and could not find any help there.

Is what I am trying to do just not possible?