By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

close and reopen db? in vb

100+
P: 365
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
Dec 17 '08 #1
Share this Question
Share on Google+
26 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Dan

This is a property setting and can be controlled as follows:
Expand|Select|Wrap|Line Numbers
  1. ' disable bypass key
  2. SetProperties "AllowBypassKey", dbBoolean, False 
  3. ' enable bypass key
  4. SetProperties "AllowBypassKey", dbBoolean, True 
  5.  
Dec 18 '08 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
For more advanced information on controlling startup properties check out this msdn article.

Chapter 2: Protecting Your Database with Startup Options
Dec 18 '08 #3

100+
P: 365
im sorry, i already know how to do that bit.

i need to know how to force a restart of currentdb
Dec 18 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You could try running a compact and repair which will compact the database and then close and reopen it.

Expand|Select|Wrap|Line Numbers
  1. '------------------------------------
  2. '- Compact the database.  This only -
  3. '- works if it is the only code in  -
  4. '- the function, and if the         -
  5. '- function is called from the last -
  6. '- line of another VB function      -
  7. '------------------------------------
  8. Function CompactandRepairDB()
  9. On Error GoTo Err_CompactDB
  10.  
  11.     CommandBars("Menu Bar"). _
  12.     Controls("Tools"). _
  13.     Controls("Database utilities"). _
  14.     Controls("Compact and repair database..."). _
  15.     accDoDefaultAction
  16.  
  17. Exit_Compactdb:
  18.  
  19.     Exit Function
  20.  
  21. Err_CompactDB:
  22.  
  23.     If Err.Number = 3356 Then
  24.         Resume Exit_Compactdb
  25.     Else
  26.         MsgBox Err.Description
  27.         Resume Exit_Compactdb
  28.     End If
  29.  
  30. End Function
  31.  
Dec 18 '08 #5

100+
P: 365
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?
Dec 18 '08 #6

NeoPa
Expert Mod 15k+
P: 31,418
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.
Dec 18 '08 #7

100+
P: 365
Would you consider it sloppy to open a blank database first and then the original with exclusive rights? would that work?
Dec 18 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
@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.
Dec 18 '08 #9

100+
P: 365
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?
Dec 18 '08 #10

NeoPa
Expert Mod 15k+
P: 31,418
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?
Dec 18 '08 #11

100+
P: 365
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?
Dec 18 '08 #12

100+
P: 365
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)
Dec 19 '08 #13

NeoPa
Expert Mod 15k+
P: 31,418
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.
Dec 19 '08 #14

100+
P: 365
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.
Dec 19 '08 #15

NeoPa
Expert Mod 15k+
P: 31,418
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
Expand|Select|Wrap|Line Numbers
  1. CD /D %HomePath%
  2.  
  3. :CheckLoop
  4. Ren MyDB.Mdb MyDB.Mdb
  5. If ErrorLevel 1 GoTo CheckLoop
  6.  
  7. MyDB.Mdb
Dec 20 '08 #16

NeoPa
Expert Mod 15k+
P: 31,418
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.
Dec 20 '08 #17

100+
P: 365
I am using a UNC (\\DataDrive\Folder) path to locate my DB and this throws a spanner in the works for CMD file?
Dec 23 '08 #18

NeoPa
Expert Mod 15k+
P: 31,418
It shouldn't be that much of a problem. The example I used was simply an example. Try instead something similar to :
TestMDB.Cmd
Expand|Select|Wrap|Line Numbers
  1. :CheckLoop
  2. Ren \\DataDrive\Folder\MyDB.Mdb MyDB.Mdb
  3. If ErrorLevel 1 GoTo CheckLoop
  4.  
  5. \\DataDrive\Folder\MyDB.Mdb
Dec 23 '08 #19

100+
P: 365
I dont mean to keep throwing up new obsticles... but i am using a hidden folder and it doesnt seem to work..
Dec 23 '08 #20

NeoPa
Expert Mod 15k+
P: 31,418
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?
Dec 23 '08 #21

100+
P: 365
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
Dec 24 '08 #22

NeoPa
Expert Mod 15k+
P: 31,418
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) ?
Expand|Select|Wrap|Line Numbers
  1. DIR \\nlg-plwardv1\holiday$
Dec 24 '08 #23

100+
P: 365
ok the file is not visible (that is hidden too) could that be the problem then?
Dec 24 '08 #24

NeoPa
Expert Mod 15k+
P: 31,418
Oh yes ;)
Dec 24 '08 #25

100+
P: 365
im having some trouble with a command file (that is written from access vb module)

Expand|Select|Wrap|Line Numbers
  1. CheckLoop1:
  2. Copy \\nlg-plwardv1\Holiday$\ShiftInfo.txt \\nlg-plwardv1\holiday$\ShiftInfo.txt /y
  3. If ErrorLevel 1 GoTo CheckLoop1
  4. Checkloop2:
  5. \\nlg-plwardv1\holiday$\ShiftInfo.txt
  6. If ErrorLevel 1 GoTo CheckLoop2
  7. Checkloop3:
  8. Del \\nlg-plwardv1\holiday$\Update.cmd /f
  9. If ErrorLevel 1 GoTo CheckLoop3
  10.  
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
Jan 30 '09 #26

100+
P: 365
figured it out had colon in wrong place

Expand|Select|Wrap|Line Numbers
  1. :CheckLoop
Feb 1 '09 #27

Post your reply

Sign in to post your reply or Sign up for a free account.