close and reopen db? in vb

[Dan2kx]

hello,

i want to use the various startup code such as

AllowByPassKey
etc

as you no doubt know this only takes affect after a db restart, i want to force this in code... any ideas how?

my plan is to use command switches to parse a "lock"/"unlock" variable and then restat immediately (if unlock (default would be locked)) and open exclusively.

tnx
dan

[MMcCarthy]

Hi Dan

This is a property setting and can be controlled as follows:

' disable bypass key
SetProperties "AllowBypassKey", dbBoolean, False 
' enable bypass key
SetProperties "AllowBypassKey", dbBoolean, True

[MMcCarthy]

For more advanced information on controlling startup properties check out this msdn article.

Chapter 2: Protecting Your Database with Startup Options

[Dan2kx]

im sorry, i already know how to do that bit.

i need to know how to force a restart of currentdb

[MMcCarthy]

You could try running a compact and repair which will compact the database and then close and reopen it.

'------------------------------------
'- Compact the database.  This only -
'- works if it is the only code in  -
'- the function, and if the         -
'- function is called from the last -
'- line of another VB function      -
'------------------------------------
Function CompactandRepairDB()
On Error GoTo Err_CompactDB

    CommandBars("Menu Bar"). _
    Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Compact and repair database..."). _
    accDoDefaultAction
    
Exit_Compactdb:

    Exit Function
    
Err_CompactDB:

    If Err.Number = 3356 Then
        Resume Exit_Compactdb
    Else
        MsgBox Err.Description
        Resume Exit_Compactdb
    End If

End Function

[Dan2kx]

i used a shell command as such

(simplified)

call shell(strFilena me, 1)
docmd.quit

this works but i cannot open the db in exclusive mode because it is still open
any ideas similar?

[NeoPa]

You will never be able to call for an exclusive open on a database from within the code of the very same database. That is simply illogical.

You can try to be clever and set something up with a Cmd or Bat file. That won't be too straightforward though, depending on the complexity you want to use.

[Dan2kx]

Would you consider it sloppy to open a blank database first and then the original with exclusive rights? would that work?

[MMcCarthy]

Would you consider it sloppy to open a blank database first and then the original with exclusive rights? would that work?

No because as NeoPa says you cannot open "another" database exclusively while you are still in the original database and therefore it is still open.

Remember you are dealing with instances of the database. It doesn't matter if the database is empty or not. It's the application that counts.

[Dan2kx]

I meant to open a blank database, close the original and then open the original again with /excl and then close the blank as such;

call shell(blank,1)
docmd.quit
call shell(original /excl)
docmd.quit

therefore the original would not be open.
would that work?

[NeoPa]

If the original is not open then the code must have stopped running. ==> no opening of any other database.

Control must be passed to some other process, which in turn, and only after the calling process (original Access database code) has been determined to have completed, may open the original database in exclusive mode.

Does that make sense?

[Dan2kx]

mayb...

if i open a blank database with new code to open the first on open that would work!?

or else a batch file then?
if i was to use a batch file how would i get it to wait until access was closed before reopening?

[Dan2kx]

yes! works!

so from main db, i have a command switch, if /cmd = "unlock" turn startups back on, open next (blank) database and close main.

autoexec macro in blank db runs code which opens original /excl and closes blank. to slow down the automated process and allow /excl time for first db to close, don't use digital signature on blank db, or add in a msgbox

shell command used as prviously sated.

unless anyone can suggest a better method thaks for the help guys!

Dan out (for now)

[NeoPa]

In a CMD file (far better than BAT - although either would work for your requirements) I usually do a simple rename of a file to itself to determine if it is safe to continue.

If that works (NOT ERRORLEVEL = 1) then the file is free to be opened. If not, then loop and try again, optionally prompting the operator to determine if they're still interested.

The beauty of a CMD (or BAT) file is that the actual file can be created on the fly by the database code, precisely defined for the job in hand and without depending on anything external to the database to be left lying around.

[Dan2kx]

im afraid i have no real experience with bat/cmd files other than simple tasks, could you point me in a useful direction for this particular requirement? i am attracted to the simplicity rather than my more complicated fix.