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(strFilename, 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]

@Dan2kx
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.

[NeoPa]

As an working example, and to illustrate all the points I think you'll need, I knocked up a working version below. You may well have to make changes, and you may want to incorporate some flexibility into it (Name of MDB; Folder path; etc), but fundamentally this is what you need.
TestMDB.Cmd

CD /D %HomePath%
 
:CheckLoop
Ren MyDB.Mdb MyDB.Mdb
If ErrorLevel 1 GoTo CheckLoop
 
MyDB.Mdb

[NeoPa]

As a sort of PS I googled "cmd tricks" and found Coding Horror: Stupid Command Prompt Tricks. Typing HELP on the command line is one of the basic ways of learning what you can do with it. It has a couple of other tips too though.

Everything else I found seemed to be cracking related so I won't send you in that direction.

[Dan2kx]

I am using a UNC (\\DataDrive\Folder) path to locate my DB and this throws a spanner in the works for CMD file?

[NeoPa]

It shouldn't be that much of a problem. The example I used was simply an example. Try instead something similar to :
TestMDB.Cmd

:CheckLoop
Ren \\DataDrive\Folder\MyDB.Mdb MyDB.Mdb
If ErrorLevel 1 GoTo CheckLoop
 
\\DataDrive\Folder\MyDB.Mdb

[Dan2kx]

I dont mean to keep throwing up new obsticles... but i am using a hidden folder and it doesnt seem to work..

[NeoPa]

I think you need to give more information.

Hidden folders don't automatically stop access. They're simply not displayed by default (when browsing). What is your specific problem here?

[Dan2kx]

my file path is
\\nlg-plwardv1\holiday$\holidays.mdb
i tried your .cmd solution but got a "file not found error"
i dont really wantto map the drive

cheers
and sorry for being difficult

Dan

[NeoPa]

This doesn't look like an issue with hidden shares to me. I've just run a test on viewing the contents of a hidden share and it worked perfectly as expected.

What response do you get when you run (from a command prompt) ?

DIR \\nlg-plwardv1\holiday$

[Dan2kx]

ok the file is not visible (that is hidden too) could that be the problem then?

[NeoPa]

Oh yes ;)

[Dan2kx]

im having some trouble with a command file (that is written from access vb module)

CheckLoop1:
Copy \\nlg-plwardv1\Holiday$\ShiftInfo.txt \\nlg-plwardv1\holiday$\ShiftInfo.txt /y
If ErrorLevel 1 GoTo CheckLoop1
Checkloop2:
 \\nlg-plwardv1\holiday$\ShiftInfo.txt
If ErrorLevel 1 GoTo CheckLoop2
Checkloop3:
Del \\nlg-plwardv1\holiday$\Update.cmd /f
If ErrorLevel 1 GoTo CheckLoop3
 

can anyone see any errors with this? doesnt do anything as far as i can tell
the last line is for deleting itself

this is just a tester ATM, i plan to use the code to pull the current version of an MDE if the one that is running is out of date (on open).
Cheers

[Dan2kx]

figured it out had colon in wrong place

:CheckLoop