From Newsgroup: comp.databases.ms-sqlserv

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 ] ...........
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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 ] ...........
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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 ] ...........
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

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 ] ...........
--- Synchronet 3.21d-Linux NewsLink 1.2