How to make Access run ExifTool to retrieve metadata/tiff tags? (1 Viewer)

JamesGreg

New member
Local time
Tomorrow, 00:31
Joined
Feb 17, 2021
Messages
15
Okay I've looked into why it wasn't working. When I do the import wizard, I have set the options as so:

1613628109019.png


1613628010194.png


Then when I finish the wizard, it successfully imports the data into the table.

Does this mean anything significant to you, at least when coding it? Like default for TransferText is acImportDelim so that should be fine right? Then what about the delimiter being a comma and text qualifier being a "?
 

bastanu

AWF VIP
Local time
Today, 06:31
Joined
Apr 13, 2010
Messages
1,401
There you go, save all that in a named specification and use it in your TransferText :)

Cheers,
 

JamesGreg

New member
Local time
Tomorrow, 00:31
Joined
Feb 17, 2021
Messages
15
There you go, save all that in a named specification and use it in your TransferText :)

Cheers,
Okay, it seems like Access needs to wait for the file to be created first before it can import. Because when I run the sub now, it gives me the same error. But when I end the process and find the CSV in the specified location and re-run the sub, the data successfully imports into the table. So how would I tackle this now? I guess this is where ShellAndWait would come into the eqaution, but I didn't really understand how to incorporate it here. Is that the way to go, and if so how do I do that? Or at least make Access wait for the CSV to be created.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:31
Joined
Oct 29, 2018
Messages
21,357
Okay, it seems like Access needs to wait for the file to be created first before it can import.
That's what I've been suspecting all along. There are many ways to insert a time delay in your code. ShellAndWait could be one of them. As well as the Sleep API. Another is to use a Loop. Yet another is to use the Dir() function to check for the file.
 

bastanu

AWF VIP
Local time
Today, 06:31
Joined
Apr 13, 2010
Messages
1,401
Here is a sub to wait for a certain number of seconds:
Code:
Public Sub Pause(NumberOfSeconds As Double)
On Error GoTo error_goto

Dim PauseInterval As Variant   'Pause interval is the wait time
Dim StartTime As Variant       'wait start time
Dim ElapsedInterval As Variant  'time elapsed from start time to now
Dim preMidnightInterval As Variant   'time interval from start time to midnight
Dim endTime As Variant

'initializing variables
PauseInterval = NumberOfSeconds
StartTime = Timer
ElapsedInerval = 0
preMidnightInterval = 0
endTime = StartTime + PauseInterval

Do While ElapsedInterval < PauseInterval
ElapsedInterval = Timer - StartTime + preMidnightInterval
'During the day premidnightInterval =0
'shortly after midnight is passed timer is almost 0 and preMidnightInterval becomes non zero
'detecting midnight switch
'the instant midnight is passed ElapsedInterval = 0 - starttime + 0
    If ElapsedInterval < 0 Then
    preMidnightInterval = 86400 - StartTime 'interval segment before midnight
    StartTime = 0       'reset start time to midnight
    End If
DoEvents

Loop
'Debug.Print "starttime " & StartTime & "elapsed interval " & ElapsedInterval & " timer:" & Timer & "endtime:" & endTime
Exit_GoTo:
'On Error GoTo 0
Exit Sub

error_goto:
Debug.Print Err.Number, Err.Description, er1
GoTo Exit_GoTo

End Sub

but as theDBGuy said a simple loop might work as well (but might get stuck if the csv never gets created):

Code:
Do Until Dir(FullPathAndNameofYourFile.CSV)<>""
    DoEvents
Loop

Cheers,
Vlad
 

Isaac

Lifelong Learner
Local time
Today, 06:31
Joined
Mar 14, 2017
Messages
8,738
I use a few lines of code for waiting:

Code:
Sub MakecodeWait(strSeconds As String)
Dim dtNow As Date
dtNow = Now()
Do Until Now > dtNow + TimeValue("00:00:0" & strSeconds)
    'do nothing
Loop
End Sub

Call it:

do until dir(path)<>""
makecodewait "3" 'wait 3 more seconds
loop

Except you might want to consider putting something in to where if it's been longer than a certain amount of time, consider something went wrong and abort
 

JamesGreg

New member
Local time
Tomorrow, 00:31
Joined
Feb 17, 2021
Messages
15
Here is a sub to wait for a certain number of seconds:
Code:
Public Sub Pause(NumberOfSeconds As Double)
On Error GoTo error_goto

Dim PauseInterval As Variant   'Pause interval is the wait time
Dim StartTime As Variant       'wait start time
Dim ElapsedInterval As Variant  'time elapsed from start time to now
Dim preMidnightInterval As Variant   'time interval from start time to midnight
Dim endTime As Variant

'initializing variables
PauseInterval = NumberOfSeconds
StartTime = Timer
ElapsedInerval = 0
preMidnightInterval = 0
endTime = StartTime + PauseInterval

Do While ElapsedInterval < PauseInterval
ElapsedInterval = Timer - StartTime + preMidnightInterval
'During the day premidnightInterval =0
'shortly after midnight is passed timer is almost 0 and preMidnightInterval becomes non zero
'detecting midnight switch
'the instant midnight is passed ElapsedInterval = 0 - starttime + 0
    If ElapsedInterval < 0 Then
    preMidnightInterval = 86400 - StartTime 'interval segment before midnight
    StartTime = 0       'reset start time to midnight
    End If
DoEvents

Loop
'Debug.Print "starttime " & StartTime & "elapsed interval " & ElapsedInterval & " timer:" & Timer & "endtime:" & endTime
Exit_GoTo:
'On Error GoTo 0
Exit Sub

error_goto:
Debug.Print Err.Number, Err.Description, er1
GoTo Exit_GoTo

End Sub

but as theDBGuy said a simple loop might work as well (but might get stuck if the csv never gets created):

Code:
Do Until Dir(FullPathAndNameofYourFile.CSV)<>""
    DoEvents
Loop

Cheers,
Vlad
When I used the second code, I get this message:

1613692018835.png
 

JamesGreg

New member
Local time
Tomorrow, 00:31
Joined
Feb 17, 2021
Messages
15
Just an update, I looked back at Isaac's idea on running a .bat file to do this and it works a lot better than trying to run exiftool with Shell.

All I did was use wsh to help me for the Wait.

Code:
'code to run the bat file here
wsh.Run(strCommand, _
                           WindowStyle:=0, _
                           WaitOnReturn:=True)
'code to run the import transfertext here

Thanks for that @Isaac

Now, my only question is, say if I add a date/time column in my table (tblData) in Access. Because the TransferText will append data into the table each time it runs the .bat, is there a way to log the date/time of the data in the same table? So that I can compare data from created from one date to another? Or is there a better idea?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:31
Joined
Oct 29, 2018
Messages
21,357
Just an update, I looked back at Isaac's idea on running a .bat file to do this and it works a lot better than trying to run exiftool with Shell.

All I did was use wsh to help me for the Wait.

Code:
'code to run the bat file here
wsh.Run(strCommand, _
                           WindowStyle:=0, _
                           WaitOnReturn:=True)
'code to run the import transfertext here

Thanks for that @Isaac

Now, my only question is, say if I add a date/time column in my table (tblData) in Access. Because the TransferText will append data into the table each time it runs the .bat, is there a way to log the date/time of the data in the same table? So that I can compare data from created from one date to another? Or is there a better idea?
You can add a date/time field to your table and set the default value to Now().
 

Isaac

Lifelong Learner
Local time
Today, 06:31
Joined
Mar 14, 2017
Messages
8,738
Just an update, I looked back at Isaac's idea on running a .bat file to do this and it works a lot better than trying to run exiftool with Shell.

All I did was use wsh to help me for the Wait.

Code:
'code to run the bat file here
wsh.Run(strCommand, _
                           WindowStyle:=0, _
                           WaitOnReturn:=True)
'code to run the import transfertext here

Thanks for that @Isaac

Now, my only question is, say if I add a date/time column in my table (tblData) in Access. Because the TransferText will append data into the table each time it runs the .bat, is there a way to log the date/time of the data in the same table? So that I can compare data from created from one date to another? Or is there a better idea?
Glad that you got something working better. You can use something like:

Currentdb.execute "insert into sometable (columnname) values(#" & now() & "#",dbfailonerror
 

Users who are viewing this thread

Top Bottom