Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 26th, 2010, 01:42 PM
Registered User
 
Join Date: Nov 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default working with sharepoint via vba

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!!
  #2 (permalink)  
Old February 18th, 2011, 05:35 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Help with using Access with SharePoint

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
  #3 (permalink)  
Old February 21st, 2011, 12:29 PM
Registered User
 
Join Date: Nov 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your reply!
I was able to make those functions work by disabling 2010 caching mode.
  #4 (permalink)  
Old February 21st, 2011, 12:32 PM
Registered User
 
Join Date: Nov 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'll look through chapter 19 again though. Originally I couldn't find any references about it... thanks again!
  #5 (permalink)  
Old February 21st, 2011, 01:01 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default working with sharepoint via vba

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
  #6 (permalink)  
Old August 24th, 2017, 04:47 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by gjgriffith View Post
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?


Similar Threads
Thread Thread Starter Forum Replies Last Post
Type mismatch error Excel (VBA) linked to SharePoint List yukorin Excel VBA 2 August 13th, 2010 11:03 AM
VBA Code not working in 2007 blue9244 Excel VBA 4 August 20th, 2009 09:11 AM
Sharepoint GetListItems() ViewFields not working forkhead .NET Web Services 1 August 12th, 2008 03:16 AM
Excel formula not working in vba yogeshyl Excel VBA 2 December 12th, 2007 02:27 AM
Excel formula not working in VBA yogeshyl Excel VBA 2 July 19th, 2007 03:57 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.