Solved Three timer questions

[gemma-the-husky]

What code are you using to create the accde file?

 

[Marshall Brooks]

The code in the initial post in this thread and:

Public Function MakeACCDE(InPath As String, OutPath As String)
Dim App As Access.Application
Set App = New Access.Application
App.AutomationSecurity = 1 'msoAutomationSecurityLow
App.SysCmd 603, InPath, OutPath
Set App = Nothing
End Function

 

[The_Doc_Man]

Your report of "falling through the loopcode" because the file exists already COULD be because the file really WAS created quickly. BUT this is why earlier I suggested that you "groom" the working folder by deleting everything that you were going to create later. Then you can't get a false positive based on an old file.

 

[gemma-the-husky]

That's the code I have. I've never tried to build an accde in code before. The database I was using actually opens a connection form to select and connect to a choice of back ends. The syscmd line ran, but didn't create the .accde in the correct folder. I don't know if my quit line might have deleted the saved .accde. I'll comment that out to see.

 

[Marshall Brooks]

It isn't a false positive - the database was renamed this morning. Also, it wouldn't be a false positive b/c here we are checking for "Dummy.accdb" and that file gets created and deleted each time the subroutine runs (successfully).

It also isn't an error in my code.

To verify I tried this:

...
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)
If FSO.FileExists(DummyPath) Then
    MsgBox "Exists"
Else
    MsgBox "Does Not Exist"
End If
Exit Sub
FSO.CopyFile CurrentDBPath, DummyPath
...

Not the If statement is after DummyPath is defined and before the copyFile command that creates the Dummy.accdb.

I ran the code without Dummy.accdb in the folder and got "Does Not Exist" and ran it after I manually copied the database and re-named it s Dummy.accdb and got "Exists".

So now I'm back to "Why does the code work with a 40-second (or sometimes less) pause between the line to create the Dummy.accdb and the line to call the MakeACCDE subroutine and not otherwise if the code isn't hung up b/c the Dummy.accdb was not created, and how can I work around this.

Also - could you reply to the end of R20 about checking the .accde file creation date.

 

[Marshall Brooks]

That's the code I have. I've never tried to build an accde in code before. The database I was using actually opens a connection form to select and connect to a choice of back ends. The syscmd line ran, but didn't create the .accde in the correct folder. I don't know if my quit line might have deleted the saved .accde. I'll comment that out to see.

Gemma - here is what I am experiencing - maybe it will help both of us!!!

If I run my code line-by-line (F8-Debug), it works flawlessly. If I run the code with a 40-second pause before I call MakeACCDE it runs flawlessly (at least usually).

If I run it without the pause, it usually fails to create the .accde file. There is no error message, the file just is not created.

The 40-second pause is somewhat arbitrary. Initially, I tested it and it needed a 20 second pause (I think I did 10-before the dummy.accdb creation originally and 10 between that and the .accde call and later I figured out that before the .accde call was all I needed.) I found that USUALLY (but not always), it would work with NO pause if I did a C&R, compile, C&R twice on the source database before I ran it. It also seemed to work with a shorter pause if I stepped through the code and created the .accde file via F8 and then ran it again.

Final tip: My File transfer is VERY slow when working over VPN (although these files are local, but that doesn't seem to matter somehow). However, I was surprised that the pause didn't seem to need to be any longer when running over VPN, or shorter when connected via hard wire.

 

[The_Doc_Man]

Also - could you reply to the end of R20 about checking the .accde file creation date.

File object

Office VBA reference topic

learn.microsoft.com

Basically, you assign (instantiate) a file object variable using FSO methods and then look at file-object.DateCreated or .DateLastModified - which will be a date/time response.

Usually, if a file is quickly created, then .DateCreated and .DateLastModified will be the same since most of these file properties are only reported to the second, not to fractions thereof. Most files - particularly if created by simply copying them, will report the same date for both properties. However, if there is a bit of "polishing" to be done for the file's creator, then the last modified date will be slightly later than the created date. For example, the file that gets created when you do a Compact & Repair operation will create an empty DB file first (.DateCreated) and then will fill in various tables in sequence, then take care of the rest of the infrastructure... so will have a later DateLastModified.

 

[Marshall Brooks]

Thanks - I'm not that worried about comparing creation and modifed date - but it will be useful to know if the file is the one I just created or was the one I might have created 5 minutes ago ...

 

[Marshall Brooks]

Also - as usual, I was overthinking things in Reply #20. I already have a timestamp in the subroutine (although since my loop doesn't really work, I'm not using it anymore - but for the created .accde file, all I have to do is check that the creation date is newer than the timestamp and I know that the file is valid/current.

 

[gemma-the-husky]

I've tried again.

The file copy is complete, but I just can't get the syscmd statement to generate an .accde
I wouldn't create a database this way, so I'm not going to continue to try.
I imagine I'm missing something.

 

[Marshall Brooks]

@gemma-the-husky - Odd, it does work for me at long as the pause is present ...

 

[gemma-the-husky]

I added a msgbox after the copy, and opened the dummy database to ensure it had been completely copied.
I can create the .accde manually without any errors, but not via the code. I am not trying to suppress any properties in the database which might prevent an .accde being built. Maybe that's the issue. It's a new database I'm working on, and there's no fancy stuff in there.

 

[Marshall Brooks]

Source database needs to be compiled and needs to be in a trusted location. I'm not sure if it would work if you didn't have any VBA code in the db - not that you said that.

Also - @isladogs code was written to copy specified modules to a new blank database. I omitted that and just made an .accde from a copy of the source database. Maybe that is the issue for you.

 

[gemma-the-husky]

I don't think this folder is a trusted location, so I'll try that.

 

[Marshall Brooks]

Almost there ...

Dim FSOFile as Object
If FSO.FileExists(OutPath) Then
FSOFile = FSO.GetFile(OutPath)
If FSOFile.DateCreated > TStamp Then
Box "ACCDE created as " & OutPath
Else
Box "Something went wrong! ACCDE file was not created."
End If
Else
Box "Something went wrong! ACCDE file was not created."
End If

I'm getting Error 091 Object Variable or With Block Variable Not Set on the red line above ...

 

[The_Doc_Man]

Syntax for the red line is SET FSOFile = FSO.GetFile(OutPath) - because object variables are actually pointers to an implied data structure. Your original syntax would have tried to store a value (like a string or number or other scalar data type.) But it is a tossup as to whether it disliked setting a value to an object on the left of the equals sign or whether it disliked trying to GET a value from the object on the right of the equals sign. Doesn't matter.

Scalar variables, you use "X =" to assign a value to variable. OBJECT variables, you use "SET X =" to assign an object address to object X. The .GetFile returns a file OBJECT.

 

[Marshall Brooks]

Thank you - I see that on the example page now. Will post updated code later - might not be until Monday.

 

[Marshall Brooks]

Status:

• Code works. It generates the .accde file and correctly reports whether or not it was created.
• I added a progress bar. It was useful when there was a 40-second pause and it was "It seems like it should be done by now."
• Today the code ran in 3-seconds. I'm not sure if the Progress bar kept the db occupied so that it didn't cough making the file or what? The plan is to occasionally test it - i.e. I'll leave it at 3 seconds for now. If that fails, I'll increase until it works and leave it there.

I'm going to mark the thread as solved since the original question of how to exit the loop after a certain period of time was resolved.

However, if anyone figures out the bigger question of how to pause the code for long enough, but not more than long enough, I'd be interested.

Here's the code:

Private Sub btnACCDE_Click()
' https://www.access-programmers.co.uk/forums/threads/f11-disable-or-not.328822/
' https://www.access-programmers.co.uk/forums/threads/three-timer-questions.329391/
Dim CurrentDBPath, DummyPath As String, OutPath As String
Dim FSO As Object
Dim FSOFile As Object
Dim TStamp As Date
Dim IElapsed As Integer
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
DoCmd.OpenForm "frmTimerProgressBar"
With Forms![frmTimerProgressBar]
     .LabelCaption.caption = "Creating .ACCDE File"
     '.CycleDuration sets how long it takes the Progess Bar to complete (in 1/10-seconds). Default of 600 takes 60 seconds. 50 would take 5 seconds.
     .CycleDuration = 30
     .cmdStart_Click
End With
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 FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile CurrentDBPath, DummyPath
TStamp = Now()
' Loop below is not required.
'Do Until FSO.FileExists(DummyPath)
'    IElapsed = DateDiff("s", TStamp, Now())
'    If IElapsed > 60 Then
'        MsgBox IElapsed
'        Exit Do
'    End If
'    DoEvents
'Loop
OutPath = Replace(CurrentDBPath, "accdb", "accde")
' 40 does not give errors.  3 Works sometimes. 2 Fails Sometimes. MB
Pause (3)
Call MakeACCDE(DummyPath, OutPath)
FSO.DeleteFile DummyPath
DoCmd.Close acForm, "frmTimerProgressBar", acSaveNo
Screen.MousePointer = 1
If FSO.FileExists(OutPath) Then
     Set FSOFile = FSO.GetFile(OutPath)
     If FSOFile.DateCreated > TStamp Then
        Box "ACCDE created as " & OutPath
    Else
        Box "Something went wrong! ACCDE file was not created."
    End If
Else
    Box "Something went wrong! ACCDE file was not created."
End If
CurrentDb.Properties("AllowSpecialKeys").value = True
ExitSub:
Set FSOFile = Nothing
Set FSO = Nothing

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

 

[Marshall Brooks]

Forgot to click reply, but I also spoke too soon.

Basically, the code works if no file exists in the folder initially - it reports success, or if an old file exists in the folder and it fails, it reports failure.
If a previous file exists, it reports failure, even though the new file is there.

Figured it out - I think I need .DateLastModified instead of .DateCreated.

 

[Marshall Brooks]

That didn't work either. The other option is to check if a previous file exists and then delete it before the procedure starts.