Code to Work Offline

[vbaInet]

Three questions, did you put that DoCmd code in the Load event of the form? The code should run unaided, did you close and open the db and did the form load by itself?

 

[crann]

yes to all three questions.

its in the onload event

when db is launched form with that onload event startups up first and then immediately get the runtime error.

all happens unaided

jon

 

[vbaInet]

Do you know how to create an AutoExec macro?

 

[crann]

I know if I create a macro and save it autoexec that should run that particular macro on the db startup.

I did look through the macro list in the hope there was a simple toggle offline option.

sadly not lol

jon

 

[vbaInet]

Ok, on second thoughts we won't be needing the AutoExec macro. Give the following a go.

1. In the new db, put the following function in a module:

Public Function ToggleOfflineMain(AccAppToToggle As Access.Application, _
                                  AccAppPath As String)

    With AccAppToToggle
        .CloseCurrentDatabase
        .OpenCurrentDatabase AccAppPath
        .RunCommand acCmdToggleOffline
    End With
    
    Set AccAppToToggle = Nothing
    
    Application.Quit
End Function

... you can delete the form.
2. Debug > Compile, Save and Close the db
3. In your main db, place this code behind a button:

    Dim accApp      As Access.Application
    Dim strPathToDb As String
    
    Const PATH_TOGGLE_DB As String = "[COLOR="Blue"]Path to the new db here[/COLOR]"
    
    strPathToDb = Application.CurrentProject.FullName
    
    Set accApp = New Access.Application
    
    With accApp
        .OpenCurrentDatabase PATH_TOGGLE_DB
        .Run "ToggleOfflineMain", Application, strPathToDb
    End With
    
    Set accApp = Nothing

... put the path to the new db in blue.
4. Debug > Compile and Save.

Now click the button and see what happens.

 

[crann]

Ok thats done

when I click button error message 7867 Database already open

when I hit debug, highlights. OpenCurrentDatabase AccAppPath in yellow???

 

[vbaInet]

Did the main database close before the error?

 

[crann]

No basicallywhen I click the cmd button on my Nav Page it immediately loads that error message that the database is already open

 

[vbaInet]

Add this line, Debug > Compile, save and close the new db, re-run the code and tell me which db it is:

    With AccAppToToggle
        .CloseCurrentDatabase
[COLOR="Blue"]        MsgBox .CurrentDb.Name[/COLOR]

 

[crann]

the message returns saying its the path of my main database

 

[vbaInet]

Replace the code in the new db with the below, Debug > Compile, save, close and re-run:

Public Function ToggleOfflineMain(AccAppToToggle As Access.Application, _
                                  AccAppPath As String)
    
    AccAppToToggle.Quit
    
    Set AccAppToToggle = New Access.Application
    
    With AccAppToToggle
        .OpenCurrentDatabase AccAppPath
        .RunCommand acCmdToggleOffline
        .Visible = True
    End With
    
    Set AccAppToToggle = Nothing
    
    Application.Quit
End Function

 

[crann]

So

It appeared at a quick flash as if the database was closed and reopened then a pop up message saying:

runtime 31652

you do not have exclusive access to the database. Your sharepoint lists can not be taken offline or cached at this time

no other databases open restarted my pc to check nothing running in background

jon

 

[vbaInet]

Do the usual with the following code:

    With AccAppToToggle
        .OpenCurrentDatabase AccAppPath
        DoEvents
        .Visible = True
        .RunCommand acCmdToggleOffline
    End With

 

[crann]

same error message on debug highlights following:

.RunCommand acCmdToggleOffline

 

[vbaInet]

Save all your work and close all Access applications, go to Task Manager, click the Details tab (not the Processes tab), look for MSACCESS.EXE and kill all instances.

Open your main db and re-run the code.

 

[crann]

Cleared everything

still same message and I notice that the new db seems to have a second icon appear with a padlock on it on the database which just seems to remain on desktop.

I really appreciate your help, sorry my knowledge is so basic.

 

[vbaInet]

Is your database split, front end and back end?

 

[crann]

oh dear

not actually sure cant remember what i did when i set this up is that easy to check.

All my tables are linked to my sharepoint site so i can save and change data from several locations.

When i make alterations to the database like forms etc i simply upload the db to my sharepoint space then pull it back down to any machine i like and save it local.

It works very well just ive recently bought a tablet to go mobile so was trying to integrate a simple button to switch me offline quick when no internet available.

 

[vbaInet]

I should have looked at the interface in the first instance. Do you get the button in the attached image? I believe that's equivalent to what we're trying to do. I have an updated code, but try this button first.

 

[crann]

Yes lol thats the button i am trying to get onto my Nav form as my db loads full screen with any of the access commands or toolbars. This is because other people use my database so don't want any back office MS Access on show.

So at the moment I have to keep minimizing everything down to get to that button.

I just assumed it was a simple button to add into the db.

Sorry maybe I haven't been very clear.