Solved Three timer questions

[Marshall Brooks]

This code was derived from https://www.access-programmers.co.uk/forums/threads/f11-disable-or-not.328822/

The code works flawlessly if I step through it line by line.

I'm looking at the lines in red:

Private Sub btnACCDE_Click()
' https://www.access-programmers.co.uk/forums/threads/f11-disable-or-not.328822/
Dim CurrentDBPath, DummyPath As String, OutPath As String
Dim objFSO As Object
On Error GoTo ErrHandler
strResult = Dialog.Box(Prompt:="Did you do a Decompile and Compact and Repair?\n\nClicking No will cancel .ACCDE File creation." & "", Buttons:=(4 + 32))
If strResult = vbNo Then
'MsgBox "I'm Done"
    Exit Sub
End If
Screen.MousePointer = 11
CurrentDb.Properties("AllowSpecialKeys").value = False
'save and compile all modules - https://isladogs.co.uk/compile-modules/index.html
      Application.SysCmd 504, 16483
CurrentDBPath = CurrentProject.Path & "\" & CurrentProject.Name
DummyPath = CurrentProject.Path & "\" & "Dummy.accdb"
' We need to make a copy of the db for the accde vba code to work. Will not work on current DB
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile CurrentDBPath, DummyPath
OutPath = Replace(CurrentDBPath, "accdb", "accde")
Pause (40)
Do Until Not Dir(DummyPath) = ""
    DoEvents
Loop
Call MakeACCDE(DummyPath, OutPath)
objFSO.DeleteFile DummyPath
Screen.MousePointer = 1
If Dir(OutPath) = "" Then
    Box "Something went wrong! ACCDE file was not created."
Else
    Box "ACCDE created as " & OutPath & vbCrLf & "Check Date and Time"
End If
CurrentDb.Properties("AllowSpecialKeys").value = True
ExitSub:
Set objFSO = Nothing

Err_Exit:
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.DESCRIPTION
    Resume ExitSub
End Sub

The code works fine with the 40-second pause inserted (usually), but I hate to wait 40 seconds if a 1-second pause would sometimes be sufficient.

Q1 - The loop was supposed to work so that the script paused until the dummy.accdb file was created and then allowed the script to continue. It does not work and I'm not sure why.

Q2 - If the loop did work, in the CopyFile statement failed completely, the script would never exit, so I would like some method (timer on the form), to exit the loop after some interval (45 seconds, 60 seconds, etc.)

Q3 - Not related to this, but this is for a button on a form. If I had module code and wanted a similar timer function, how would I call that?

Thanks in advance!!!

 

[isladogs]

There are no lines in red!

 

[Marshall Brooks]

For some reason, the forum isn't showing either red text or bold text in the code blocks ...
These lines should be in red:
Pause (40)
Do Until Not Dir(DummyPath) = ""
DoEvents
Loop

 

[The_Doc_Man]

The code works fine with the 40-second pause inserted (usually), but I hate to wait 40 seconds if a 1-second pause would sometimes be sufficient.

Q1 - The loop was supposed to work so that the script paused until the dummy.accdb file was created and then allowed the script to continue. It does not work and I'm not sure why.

Q2 - If the loop did work, in the CopyFile statement failed completely, the script would never exit, so I would like some method (timer on the form), to exit the loop after some interval (45 seconds, 60 seconds, etc.)

Q3 - Not related to this, but this is for a button on a form. If I had module code and wanted a similar timer function, how would I call that?

Thanks in advance!!!

1. (Red lines) There are no lines in red because the code tags override the color tags. (Some quirk of the Xenforo software.)

2. (Q1) You should be able to test for the .ACCDE file's existence with FSO and, if it exists, test its properties including Creation Data and Last Modified date. If you have a pause loop, you shouldn't need to wait more than 5 seconds per iteration to check for file existence and properties. Further, if the file exists and the difference between Now() and the Last Modified Date is > 5 seconds, it is probably stable and ready to go.

3. (Q2) See comments for Q1 and testing for file operation via FSO and attention to the file's date properties.

4. (Q3) Timers are a "form" thing because you don't have the ability to do ANYTHING via code outside of an event context. MSACCESS.EXE is your MAIN and all you get to write are event-subs or general subs. But the MAIN won't call a general sub for you, it only supports events. So yes, you can make a subroutine that you could call, but the problem is, call it from what/where?

 

[Marshall Brooks]

More info ...

The script seems to be having trouble creating dummy.accdb - or more precisely, it creates it, but it can take up to 40 seconds to do so.

The doUntil loop was supposed to check for the existence of dummy.accdb. It doesn't seem to do anything and I'm not sure why. Should I use Fso.FileExists instead of Dir(). ?

The idea behind the Do Until loop was to eliminate the 40-second pause. It would just DoEvents until the file was created, whether that takes 5 seconds or 40 seconds - but the way it was written, it could take 2 hours and just sit there, so I wanted to add a timer inside the loop.

 

[The_Doc_Man]

Make the file in a folder that isn't supposed to have any persistent files. Then before the whole procedure starts creating things, delete everything in that working folder. The idea is to eliminate potential sources of false positives. When you see a completed file after erasing the folder, you know it was just now created. It is a matter of personal preference, but I would probably stick with FSO options for everything. Nothing against using Dir(), but simply a preference.

That 40-second delay in creating the dummy file is perhaps odd, but maybe not. Are you copying the file under which you are running? Because if so, you are running into file write locks against yourself.

The thing about "wait-for" loops is that you always build an "oh, shoot, it ain't gonna happen" exit somewhere, perhaps by counting iterations or testing elapsed time. Always expect the appearance of the unexpected and never mourn the absence of the expected. Just program for the possibilities.

 

[Marshall Brooks]

We keep going around the details without getting to my initial questions ...

Make the file in a folder that isn't supposed to have any persistent files. Then before the whole procedure starts creating things, delete everything in that working folder. The idea is to eliminate potential sources of false positives.

The problem isn't really with false positives - although the separate folder is workable and likely a good idea. For convenience, I like having the folder in the same folder as the database, but that isn't necessarily a requirement.

But I don't get false positives - the message at the end about whether or not the .accde file is created is always accurate - with the cavaet that if I had an earlier file with the same name, it will report that - which as you said, I could avoid with a creation date verification.

The main question was that my loop to check for existence of the dummy.accdb file was not working, but I probably have something incorrect in the syntax for that loop.

It is a matter of personal preference, but I would probably stick with FSO options for everything. Nothing against using Dir(), but simply a preference.

Oddly, I have another subroutine that used Dir() and suddenly stopped working and the fix was to go with FSO, so I'm not opposed to that.

That 40-second delay in creating the dummy file is perhaps odd, but maybe not. Are you copying the file under which you are running? Because if so, you are running into file write locks against yourself.

Yes, I'm making a copy of the active, open database. The 40 seconds is arbitrary and I'm trying to eliminate that. I've never had it take LONGER than 40 seconds. If it C&R and compile it USUALLY works with NO delay, but the idea of the loop was to allow it to take as long as needed until it finishes.

The thing about "wait-for" loops is that you always build an "oh, shoot, it ain't gonna happen" exit somewhere, perhaps by counting iterations or testing elapsed time. Always expect the appearance of the unexpected and never mourn the absence of the expected. Just program for the possibilities.

That was Q2 above - how do I test elapsed time inside the loop?

 

[The_Doc_Man]

how do I test elapsed time inside the loop?

Before entering the loop, take a timestamp... something simple like a date variable with TStamp = Now() just before the loop kick-off code. Then inside the loop, use an integer of some kind with IElapsed = DateDiff( "s", Tstamp, Now() ) to get elapsed seconds. I always get DateDiff wrong so if you get negative seconds, just flip the Tstamp and Now arguments.

 

[Marshall Brooks]

Timer code works - I didn't test the other suggestions yet, but here's what I have for the timer:

Dim TStamp As Date
Dim IElapsed As Integer
TStamp = Now()
Do Until 3 = 2
    IElapsed = DateDiff("s", TStamp, Now())
    If IElapsed > 15 Then
        MsgBox IElapsed
        Exit Do
    End If
Loop

 

[The_Doc_Man]

That's the idea for timed code. But now the next step is that inside the loop, repeat the test for completion of the thing you wanted to find. Use that as the other way to trigger an Exit Do so that the loop never takes more than one iteration after the desired event has happened. If you do that, you minimize the time involved and abort the loop at the earliest possible moment.

What you have there will work but, ... HOO BOY, will it ever be inefficient. Remember that Access VBA code is pseudo-compiled and interpreted or emulated, thus will be slow in execution. It is ALSO single-threaded. Which means that as long as YOU have code running in a loop, the next event that would normally occur in sequence CANNOT occur. Events are not interrupts, so they cannot interrupt each other. For example, if you put that sequence in the Form_Open event, you would delay the Form_Load event by 15 seconds, which would have the effect of delaying the visual painting of the form by that same 15 seconds.

 

[Marshall Brooks]

You mean as written, it would add 15 seconds, correct?

This was just a test to make sure the code works - 3 will never equal 2 so without the check, it wouldn't ever exit.

I haven't written the actual code yet, but it would be something like:

Do Until FSO.FileExists "Path and Dummy.accdb"
IElapsed = DateDiff("s", TStamp, Now())
If IElapsed > 60
Exit Do
End If
Loop

The intent is that if it takes 5 seconds to copy the database, the loop exits in 5 seconds, or it could take up to a minute. After a minute it will exit and fail.

It shouldn't take a full minute, but I'd rather give it a minute, than have it fail and have to step through the code to get it to work.

Am I misunderstanding anything?

 

[The_Doc_Man]

I would first separately test the "FSO.FileExists" part for what you are doing. That code should work... except for the missing "THEN" after the "If IElapsed" maybe.

 

[Marshall Brooks]

Can't do that - or there is really no point ... The (up to) 40-60 second pause is for CREATION of the dummy.accdb file. Nothing really is happening in the loop.

I suppose I could to something like:

If NOT FSO.FileExists "Path and Dummy.accdb"
Do Until FSO.FileExists "Path and Dummy.accdb"
IElapsed = DateDiff("s", TStamp, Now())
If IElapsed > 60
Exit Do
DoEvents
End If
Loop
End If

But all that would accomplish is cutting out one pass through the loop if somehow the copied dummy.accdb file was created between execution of the three lines below:
objFSO.CopyFile CurrentDBPath, DummyPath
OutPath = Replace(CurrentDBPath, "accdb", "accde")
<Pause or Check for file or initiate loop>

Also - I might be changing horses mid-stream, but I'll ask here first. I'm considering three options:

• The code I posted is a combination of @Gasman 's code and some of @isladogs 's code. I like that it all runs from a button on my switchboard. I don't like that it COULD take up to 40 seconds b/c it is initially making a copy of the open, active database to create the .accde file from.
• https://isladogs.co.uk/create-accde-code/index.html - This page (which I borrowed much of the above code from) is setup to run from a different database and covert the target database to an .accde. As written it will not disable the special keys (F11, etc.), which is something I want to do. I haven't tested it, but since the target database is closed, it should run quickly. One option would be to copy my button code (without the loop/pause) and the one sub to make the .accde file to a separate database and run that to create my .accde file. I wasn't crazy about having two database files to keep up with -but it isn't much different than what I do for backups where I have code that logs everyone out of the database and then use a different Compact-A-List database to do a C&R and backup of the backend. Let's say I call this _MakeACCDE.accdb
• What would it take to combine the above ideas? i.e. I would still have a "Make ACCDE" button on my active database. If clicked, it would:
  • Get the path and file name of the active database. (I know how to do this).
  • Open _MakeACCDE.accdb - I think I can figure this out.
  • Pass the current database name and path to _MakeADDCE.accdb (I don't know how to do this).
  • Close the target/source (the calling) database - not sure how to do this.
  • Create the .ACCDE File from the _MAKEACCDE.accdb database (not sure how to initiate this since it was called from the target database which is now closed).
  • Show a message on completion.
  • Close Access or at least the _MakeACCDE.accdb file.

How difficult is it to code the third option?

 

[The_Doc_Man]

Sorry for how complex this is, but you have a real rat's nest of complexity in what you are trying to do.

The step that really bothers me is what happens after you open the _MakeACCDE.ACCDB, for which step you have two choices of method. EITHER you make it just another element in the first DB's workspace OR you make it semi-autonomous as an application object. (See later for a third method, which is a bit wilder and somewhat hairy.)

In the first case (part of 1st DB's workspace) everything you do is a CALL from the 1st DB which has original locus of control. If everything is a CALL (implicit or explicit, doesn't matter) then you cannot close the 1st DB because it is the "base" of your call stack. Sort of like the cartoons where the guy sitting on a limb cuts it off and the tree falls but the limb doesn't. That is the implication of "locus of control." You can't get rid of that 1st DB because in the first case, you are riding it.

If you used the other method, i.e. creating an application object, the two DBs no longer share the same memory so the call stack situation no longer applies. However, this 2nd DB is still indirectly dependent on the input/output channels of your 1st DB, which is now using the 2nd DB as an application object. That object-oriented control mechanism creates a set of input/output channels via pseudo-devices (I think called pipes but Windows could have done it another way) between the 2nd DB and your original DB. The 2nd DB does NOT directly talk to the keyboard or mouse or display.

If you think about a pass-thru query where Access merely sends data through ODBC to have a query executed by an external SQL engine, this is analogous. OR if you think of Remote Desktop Protocol, you are using the local computer to pass mouse clicks & keyboard actions to, and get back screen data from, a remote computer. Anyway, you use the methods of programmatic object control (rather than direct control through mouse and keyboard) to control that 2nd DB. You can look up more on this topic by lookup of "IPC" or "interprocess control."

I believe that at the step where you would close the target/source DB, you would cut off those IPC channels, leaving whatever you had been running to "fend for itself" - which in Windows terms means a dangling or "detached" process. "Detached" specifically means that from that point forward, it is on its own for direction... no more control inputs/outputs. In this case, this would mean an uncontrolled task that is either self-directed or dead in the water. My concern is therefore that you would have a task you had to manually terminate because it would be out of touch. This is exactly what happens if you exit an Access DB that had opened Excel as an application object and you fail to pass the command "Application.Quit" through the object variable.

I'm not 100% sure you can get away with this using either of the two methods above, but there is another way to have that DB opened autonomously. I think the solution for this is that you would have to build a batch job to manage this process. You would trigger it to run your sequence of commands by using the /X:macroname command line option. The named macro would have to do all command staging which must include an Application:Quit action to keep the Access job from dangling - and thus blocking the batch job. The "calling DB" that you had mentioned earlier would have to become the "launching DB" to build the batch file and launch it via the SHELL command.

There are some complexities in terms of how you would pass your information. Perhaps you could build a batch job command line (using the verb SET) to define an environmental symbol holding your targeted file's name/path etc, then from Access use ENVIRON() to look up the value of that symbol. Doing this from a batch-oriented job WOULD make the copying and deleting and cleanup a LOT easier and would allow you to do some primitive file functions easily. The trick would be synchronizing with the task that launched the whole process. This is perhaps harder than it might look since your ability to create an environment variable is now trickier. The batch job and 1st DB would be in separate memory spaces again, so you might instead need to watch for the creation of your desired file to be completed. The method I always used for batch synching was to create everything using a false name and as a final step, the last step before the batch job exited, was to RENAME the file to its final name. Once that rename has occurred, you would be good to go.

 

[gemma-the-husky]

@Marshall Brooks

can you explain what you are trying to do please, as it's confusing me.

 

[Marshall Brooks]

Excellent explanation!!!

It's super complicated, but it seems similar to what I do with Bob Larson's code for updating the front end - i.e. the code:

• Checks for a batch file at startup and deletes it if found.
• Checks for a new version.
• If found, writes a batch file from the current database.
• Launches the batch file and closes the current database.
• The batch file then deletes the current database, copies the new one, opens the new one, and exits.

Seems like that could be adapted and modified ...

It also seems WAY beyond anything I really want to get involved with. I basically copied his code and was amazed at how it works.

 

[Marshall Brooks]

can you explain what you are trying to do please?

Gemma,

See the above thread linked in the first post.

Basically, I'm creating an .accde file from my .accdb file where the .accde file has the special keys (F11, etc.) disabled but the .accdb does not.

There are two ways to do that.

• My current method uses a button on the switchboard of my current database. The problem with that is that you can't make an .accde file if the source database is open so you have to make a copy of the current database and create the .accde from the copy. No problem, but creating the copy can take from 5 to 40 secconds b/c there are file locks in trying to copy an open file. Update: Forgot to mention - the initial method either failed or took 40 seconds, so the initial question was how to have the procedure take as long as it needs, but not longer. I think that is worked out.
• The other method would be to close the source database and open a second database and use that to created the .accde file. That avoids the file locks, but it means I need to close the open database, open the conversion database, probably have a prompt (filepicker) to select the source database, etc.

I was looking for a way to launch the conversion database from the source database and then close the source database and run the conversion, and that doesn't seem to be easy.

 

[gemma-the-husky]

I am trying to build the accde with this code.
I can only think it's hanging with this line.
Am I missing something?

objACC.SysCmd 603, strDBFile, strDEFile

Edit. No it's not hanging there, but that code isn't creating any file.

Sub makeaccde(dbfile As String, newfile As String)

    Dim strDBFile As String
    Dim strDEFile As String
    Dim objACC As Access.Application

    strDBFile = dbfile
    strDEFile = newfile

    Set objACC = New Access.Application

    On Error GoTo fail
    objACC.SysCmd 603, strDBFile, strDEFile
    objACC.Quit acQuitSaveNone

    Set objACC = Nothing


    While Dir(strDEFile) = ""
        DoEvents
    Wend

    MsgBox "Accde Built "
    Exit Sub


fail:
    MsgBox "Accde failed: " & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description
End Sub

 

[Marshall Brooks]

Yes, you are - it's not hanging on that line.

It works with the 40 second pause I added, or when I step through the code line-by-line.

I think where it is failing is b/c it tries to make the .accde file before the system has finished creating the dummy.accdb file.

 

[Marshall Brooks]

Two new questions ...

(I renamed objFSO to FSO).

First, this isn't working (or doesn't seem to be):

DummyPath = CurrentProject.Path & "\" & "Dummy.accdb"
' We need to make a copy of the db for the accde vba code to work. Will not work on current DB
Set FSO = CreateObject("Scripting.FileSystemObject")
'Pause (40)
FSO.CopyFile CurrentDBPath, DummyPath
TStamp = Now()
Do Until FSO.FileExists(DummyPath)
    IElapsed = DateDiff("s", TStamp, Now())
    If IElapsed > 60 Then
        MsgBox IElapsed
        Exit Do
    End If
    DoEvents
Loop

If I step through the code, it skips from Do Until to Loop - which implies the Dummy.accdb file exists - which would mean it was created in the time it took me to press F8 twice - and maybe it was, but that doesn't explain why it needs a 40 second pause to work some other times.

Secondly:

If Dir(OutPath) = "" Then
    Box "Something went wrong! ACCDE file was not created."
Else
    Box "ACCDE created as " & OutPath & vbCrLf & "Check Date and Time"
End If

The above just checks if an .accde file exists. Could be created two months ago and it will report good. How can I check if the file exists and was created in say the last two minutes. I'm guessing it is something like:
If FSO.FileExists(OutPath) and DateDiff("s", FSO.CreationDate(Outpath), Now()) < 120 Then
But that's probably not correct unless I guessed really well.