Hi there

I run a piece of VBA code that builds a temp MDB file and at the end
compacts it into a new file, with a different name; the same temp file is
again compacted into another file, with a slightly different name

The below code builds the target filename into DBfilename, check whether already exists, in case deletes it, compacts the temp into the target using
a different folder and at the end moves the target in it's proper location

The above is repeated with another target name


Dim DBfilename As String

DBfilename = NN & Ty & CO & MA & "DATI_REP.Mdb"

' 1

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename

' 2

DBfilename = NN & Ty & CO & MA & Format(Me.RIFYEAR, "0000") & Format(Me.RIFMONTH, "00") & "DATI_REP.Mdb"

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename


Now, where is the problem? In a different subsequent procedure I show the
list of available MDB files, with the CREATION DATE aside, using the
following code

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile(Me.APP_PATH + MyName)
CreationDate = oF.DateCreated
etc

What happens? the creation date of the a.m. newly built mdb files is the
same of the previously existing file, the one I have DELETED

The strange thing is that if I execute the code step-by-step (F8) checking
the file/folders after every instruction, I can see exactly what I expect: files are deleted, built in temp, moved back, and the final creation date
is the date I want, now.

If I run the code with no stop or debug, files are deleted, built and moved properly, but the creation date of the new files is the same of the old
deleted ones

https://i.imgur.com/QBITIrA.png

The picture shows the "green" files with correct creation date obtained
running step-by-step
The "yellow" files are the result of the code execution with no debug (the related log is shown on the left)

Any help and suggestion is welcome.

Just a couple of ideas:
- the CreationDate = oF.DateCreated instruction is a mistake
- NTFS has a bug

Access version 2010, running on Windows 7


Thank you


--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Ammammata explained on 7/23/2021 :

Hi there

I run a piece of VBA code that builds a temp MDB file and at the end
compacts it into a new file, with a different name; the same temp file is again compacted into another file, with a slightly different name

The below code builds the target filename into DBfilename, check whether already exists, in case deletes it, compacts the temp into the target using
a different folder and at the end moves the target in it's proper location

The above is repeated with another target name

Dim DBfilename As String

DBfilename = NN & Ty & CO & MA & "DATI_REP.Mdb"

' 1

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename

' 2

DBfilename = NN & Ty & CO & MA & Format(Me.RIFYEAR, "0000") & Format(Me.RIFMONTH, "00") & "DATI_REP.Mdb"

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename

Now, where is the problem? In a different subsequent procedure I show the list of available MDB files, with the CREATION DATE aside, using the following code

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile(Me.APP_PATH + MyName)
CreationDate = oF.DateCreated
etc

What happens? the creation date of the a.m. newly built mdb files is the
same of the previously existing file, the one I have DELETED

The strange thing is that if I execute the code step-by-step (F8) checking the file/folders after every instruction, I can see exactly what I expect: files are deleted, built in temp, moved back, and the final creation date
is the date I want, now.

If I run the code with no stop or debug, files are deleted, built and moved properly, but the creation date of the new files is the same of the old deleted ones

https://i.imgur.com/QBITIrA.png

The picture shows the "green" files with correct creation date obtained running step-by-step
The "yellow" files are the result of the code execution with no debug (the related log is shown on the left)

Any help and suggestion is welcome.

Just a couple of ideas:
- the CreationDate = oF.DateCreated instruction is a mistake
- NTFS has a bug

Access version 2010, running on Windows 7

Thank you

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

I wonder if Filemanager isn't cacheing the old fileinfo for your
filename next time it looks in that folder. What happens if you open a
command window and do a Dir.

Rdub

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 7/23/2021 9:38 AM, Ammammata wrote:

Hi there

I run a piece of VBA code that builds a temp MDB file and at the end
compacts it into a new file, with a different name; the same temp file is again compacted into another file, with a slightly different name

The below code builds the target filename into DBfilename, check whether already exists, in case deletes it, compacts the temp into the target using
a different folder and at the end moves the target in it's proper location

The above is repeated with another target name

Dim DBfilename As String

DBfilename = NN & Ty & CO & MA & "DATI_REP.Mdb"

' 1

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename

' 2

DBfilename = NN & Ty & CO & MA & Format(Me.RIFYEAR, "0000") & Format(Me.RIFMONTH, "00") & "DATI_REP.Mdb"

If Dir_Exist(Me.APP_PATH & DBfilename) <> "" Then
Kill Me.APP_PATH & DBfilename
End If

DBEngine.CompactDatabase Me.APP_PATH & "DATI_REP.MDB", Me.APP_PATH
& "temp\" & DBfilename

Name Me.APP_PATH & "temp\" & DBfilename As Me.APP_PATH & DBfilename

Now, where is the problem? In a different subsequent procedure I show the list of available MDB files, with the CREATION DATE aside, using the following code

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oF = oFSO.GetFile(Me.APP_PATH + MyName)
CreationDate = oF.DateCreated
etc

What happens? the creation date of the a.m. newly built mdb files is the
same of the previously existing file, the one I have DELETED

The strange thing is that if I execute the code step-by-step (F8) checking the file/folders after every instruction, I can see exactly what I expect: files are deleted, built in temp, moved back, and the final creation date
is the date I want, now.

If I run the code with no stop or debug, files are deleted, built and moved properly, but the creation date of the new files is the same of the old deleted ones

https://i.imgur.com/QBITIrA.png

The picture shows the "green" files with correct creation date obtained running step-by-step
The "yellow" files are the result of the code execution with no debug (the related log is shown on the left)

Any help and suggestion is welcome.

Just a couple of ideas:
- the CreationDate = oF.DateCreated instruction is a mistake
- NTFS has a bug

Access version 2010, running on Windows 7

Thank you

I don't know why there are different results when going step-by-step vs. runtime, but it seems that the script is doing what you are asking it to do:

CreationDate = oF.DateCreated

How about:
CreationDate = DateTime(now)

--
best regards,

Neil

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il giorno Fri 23 Jul 2021 04:16:25p, *Neil* ha inviato su comp.databases.ms-access il messaggio news:sdeivq$9j4$1@dont-email.me.
Vediamo cosa ha scritto:

How about:
CreationDate = DateTime(now)

no, as I replied to Ron, I need the true creation date when I fill the MDB
list to select

https://i.imgur.com/6fhgbgd.png

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il giorno Fri 23 Jul 2021 04:25:52p, *Ron Weiner* ha inviato su comp.databases.ms-access il messaggio news:sdejhk$dgh$1@dont-email.me.
Vediamo cosa ha scritto:

I wonder if Filemanager isn't cacheing the old fileinfo for your
filename next time it looks in that folder. What happens if you open a command window and do a Dir.

I thought about cacheing, so I already changed the code to compact into a different temp folder and then move the target file

the original code was to compact directly in the proper folder (after the deletion of the previous file with the same name)

but as you can see it doesn't work

so, right now, 16:45, I just run the procedure twice:

https://i.imgur.com/UdOy0Ci.png

the "standard" file date is ok, 16:37 and 16:42, the creation date is the
"old" one, yesterday and 4 days ago

DIR command, with /T:C parameter, gives the same wrong dates

C:\Sviluppo\MaresRep>dir chn*.mdb /t:c

Directory of C:\Sviluppo\MaresRep

19/07/2021 14:39 27,996,160 CHNANALL202107DATI_REP.Mdb
22/07/2021 10:09 27,996,160 CHNANALLDATI_REP.Mdb
22/07/2021 10:19 27,897,856 CHNAYALL202107DATI_REP.Mdb
22/07/2021 10:17 27,897,856 CHNAYALLDATI_REP.Mdb

while normal DIR gives

C:\Sviluppo\MaresRep>dir chn*.mdb

Directory of C:\Sviluppo\MaresRep

23/07/2021 16:42 27,996,160 CHNANALL202107DATI_REP.Mdb
23/07/2021 16:42 27,996,160 CHNANALLDATI_REP.Mdb
23/07/2021 16:36 27,897,856 CHNAYALL202107DATI_REP.Mdb
23/07/2021 16:36 27,897,856 CHNAYALLDATI_REP.Mdb


Now, running the "second part", the form that shows the MDB list

https://i.imgur.com/6fhgbgd.png

displays the wrong creation dates, and this confuses the user because
he/she thinks that the "first part" did't work properly :(


--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Back in the "olden days", I was responsible for archiving sets of Video
files where the customer was VERY particular about the Created, Last
Accessed, and Modified date-times. It has been quite some time, and I
do not recall specifically what our issue was, but I wound up having to completely take over all of these dates and times from my code.

I wrote a pair of functions that were placed in a separate code module.
One function reads the 3 values from a file, and the other writes
them.

As far as I know that app is still in use today. The app was written
in VB6, but should be a direct port to Access. To use it in Access
create a new Code Module then copy and paste all of the code from
below. Watch for word wrap. Here's the code.

' -=-=-=-=-=-=-=-=-=-=-=-=-= Code Starts -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Option Explicit

Private Type FILETIME
LowDateTime As Long
HighDateTime As Long
End Type

Private Type SYSTEMTIME
Year As Integer
Month As Integer
DayOfWeek As Integer
Day As Integer
Hour As Integer
Minute As Integer
Second As Integer
Milliseconds As Integer
End Type

Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const OPEN_EXISTING = 3
Private Const FILE_SHARE_READ = &H1
Private Const FILE_SHARE_WRITE = &H2
Private Const FILE_ATTRIBUTE_ARCHIVE = &H20

Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA"
(ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal
dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long,
ByVal hTemplateFile As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As
Long) As Long
Private Declare Function GetFileTime Lib "kernel32" (ByVal hFile As
Long, lpCreationTime As FILETIME, lpLastAccessTime As FILETIME,
lpLastWriteTime As FILETIME) As Long
Private Declare Function SetFileTime Lib "kernel32" (ByVal hFile As
Long, lpCreationTime As Any, lpLastAccessTime As Any, lpLastWriteTime
As Any) As Long
Private Declare Function SystemTimeToFileTime Lib "kernel32"
(lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long
Private Declare Function FileTimeToSystemTime Lib "kernel32.dll"
(lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long
Private Declare Function LocalFileTimeToFileTime Lib "kernel32" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long
Private Declare Function FileTimeToLocalFileTime Lib "kernel32"
(lpFileTime As FILETIME, lpLocalFileTime As FILETIME) As Long
Private Declare Function SystemTimeToVariantTime Lib "OLEAUT32"
(lpSystemTime As SYSTEMTIME, vtime As Date) As Long
Private Declare Function VariantTimeToSystemTime Lib "OLEAUT32" (ByVal
vtime As Double, ByRef lpSystemTime As SYSTEMTIME) As Long

Public Sub FileSetDates(strFilename As String, dteCreate As Date,
dteAccessed As Date, dteModified As Date)
' Purpose Set Date/Time of all three file File Date/Times
' Pass the fullsilespec to the file you want to set the
Date/Times
' Pass the DateTime for each of the 3 File Date/Time values

Dim hFile As Long, RetVal As Long
Dim SysTimeCreate As SYSTEMTIME, SysTimeAccessed As SYSTEMTIME, SysTimeModified As SYSTEMTIME
Dim ftCreateTimeLocal As FILETIME, ftAccessedTimeLocal As FILETIME, ftModifiedTimeLocal As FILETIME
Dim ftCreateTime As FILETIME, ftAccessedTime As FILETIME,
ftModifiedTime As FILETIME

' Convert From a VB Date Type to a SYSTEMTIME Type
RetVal = VariantTimeToSystemTime(CDbl(dteCreate), SysTimeCreate)
RetVal = VariantTimeToSystemTime(CDbl(dteAccessed),
SysTimeAccessed)
RetVal = VariantTimeToSystemTime(CDbl(dteModified),
SysTimeModified)

' Convert SYSTEMTIME to Local FILETIME
SystemTimeToFileTime SysTimeCreate, ftCreateTimeLocal
SystemTimeToFileTime SysTimeAccessed, ftAccessedTimeLocal
SystemTimeToFileTime SysTimeModified, ftModifiedTimeLocal

' Convert Local FILETIME to GMT
LocalFileTimeToFileTime ftCreateTimeLocal, ftCreateTime
LocalFileTimeToFileTime ftAccessedTimeLocal, ftAccessedTime
LocalFileTimeToFileTime ftModifiedTimeLocal, ftModifiedTime

' Open the file to get the filehandle
hFile = CreateFile(strFilename, GENERIC_WRITE, FILE_SHARE_READ Or FILE_SHARE_WRITE, ByVal 0&, OPEN_EXISTING, 0, 0)
If hFile Then
' File opened - Set all of the File Times
RetVal = SetFileTime(hFile, ftCreateTime, ftAccessedTime, ftModifiedTime)
' Close file
RetVal = CloseHandle(hFile)
Else
' Poopies
MsgBox "CreatFile failed in FileSetDates()"
End If

End Sub

Public Sub FileGetDates(ByVal strFilename As String, ByRef
dteCreateTime As Date, _
ByRef dteLastAccessTime As Date, ByRef dteLastModifiedTime As Date)
' Purpose Get all of the date and times associated with the filename specified
' Pass the fullsilespec to the file you want to get the
Date/Times from
' Sub will place the Create, Last Accessed, and Last Modified
dates of the file in the Parameters passed by ref

Dim hFile As Long, RetVal As Long

Dim cTime As FILETIME ' DateTime as written on the media
Dim aTime As FILETIME
Dim mTime As FILETIME

Dim cTimeLocal As FILETIME ' DateTime converted to users time zone
Dim aTimeLocal As FILETIME
Dim mTimeLocal As FILETIME

Dim CreateTime As SYSTEMTIME ' Used as interim step for
conversion to a VB Date Type
Dim LastAccessTime As SYSTEMTIME
Dim LastModifiedTime As SYSTEMTIME

' Open the file
hFile = CreateFile(strFilename, GENERIC_READ, FILE_SHARE_READ,
ByVal CLng(0), OPEN_EXISTING, FILE_ATTRIBUTE_ARCHIVE, 0)
If hFile Then
' Well at least that worked
' Get the Dates
RetVal = GetFileTime(hFile, cTime, aTime, mTime)
' Close the file
RetVal = CloseHandle(hFile)

' Convert FILETIME as written on the media to the users Local
FILETIME (was written as GMT)
RetVal = FileTimeToLocalFileTime(cTime, cTimeLocal)
RetVal = FileTimeToLocalFileTime(aTime, aTimeLocal)
RetVal = FileTimeToLocalFileTime(mTime, mTimeLocal)

' Convert Local FILETIME Type to SYSTEMTIME Type
RetVal = FileTimeToSystemTime(cTimeLocal, CreateTime)
RetVal = FileTimeToSystemTime(aTimeLocal, LastAccessTime)
RetVal = FileTimeToSystemTime(mTimeLocal, LastModifiedTime)

' Convert SYSTEMTIME to a VB Date type
RetVal = SystemTimeToVariantTime(CreateTime, dteCreateTime)
RetVal = SystemTimeToVariantTime(LastAccessTime,
dteLastAccessTime)
RetVal = SystemTimeToVariantTime(LastModifiedTime,
dteLastModifiedTime)
Else
' Poopies
MsgBox "CreatFile failed in FileGetDates()"
End If

End Sub

' -=-=-=-=-=-=-=-=-=-=-=-=-= Code Ends -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il giorno Fri 23 Jul 2021 10:19:53p, *Ron Weiner* ha inviato su comp.databases.ms-access il messaggio news:sdf89d$5nm$1@dont-email.me.
Vediamo cosa ha scritto:

Here's the code

thank you Ron, I'll give it a try

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)