Help with Images on Continuous Form

[pattrickcolin]

Any luck yet, towards a solution.
Regards Colin

[orange]

Hi Colin,

I do have my test set up and able to generate a list of image files and their folder/subfolder.
I can do this with a DIR approach using a Bat file, and I have found a way to do this from within Access.
I have not adjusted this test set up to work with your directory structure. I'll look more at that later today.

I am trying to get something that will help you without overwhelming you.

I think the approach using just Access is most appropriate at the moment since it doesn't involve Batch files (*.bat)
or the Dir command and parameters.

I do not work with Access O365 so that's still an unknown.

My approach in the test set up is as follows:

-from an Access form, supply parameters for the Folder/subfolder you want to review
-click a button to generate a list of image(jpg) files complete with their path
-link the latest filelist as a table in your database
-using info in your Access table with the records and the path for an image file and the linked table
-run some existing queries to show various results
------images in your database table
------images on the folder/subfolder that was selected above
------images in the filesystem and not in the database
------image references in the database that don't exist on the filesystem
-using the form from the earlier posts, display database records with/without images

How familiar are you with queries?
What about vba?

Here is some code that gets executed when a button on the form is clicked:

Private Sub btnGenImageList_Click()
    Dim timestart As Date: timestart = Now
    Dim timeEnd As Date
    Me.txtMsg.Visible = False
    TestShellDir
    Me.txtMsg.Visible = True
    Me.txtMsg = Now & " -Image File List created for Folder(s) and File(s) using DIR"
    timeEnd = Now
    MsgBox "Elapsed time " & DateDiff("s", timestart, timeEnd)
End Sub

Can you see what it is doing?
Issues/questions?

Here is design view of the form. The code goes with the upper button.



/orange

[pattrickcolin]

Yes, comfortable with Queries and setting up New Queries.
I now have a library book to try and learn VBA. However at 71 years I find I'm slower to learn.

I've looked at your VBA code realize its attached to a button Named [btnGenImageList] on my form.
This button is linked via Properties-Events-OnClick and is placed as an [Event Procedure] where I put the VBA text. It is here that the code can be tweaked and edited.

Looking at the code, its like a foreign language, where one tries to read a foreign Language book with a phrase book. So I understand some of it but couldn't write it down.
But here goes, What I think it means:-

Dim Statements set Start and Finish times
Next line switches off messages to the screen.
TestShellDir must be reading a Directory, But I have no idea how. (Looks like this is the part you are working on?)
Next line switches messages back on
Next line sets up a screen message telling me todays date that this procedure ran, looking up the folder and files.
Next line sets End Time
Final line produces a message in seconds on how long the event procedure took.

Not overwhelmed and still feeling comfortable.

Here in Southend on Sea, on a sunny day, with the Daffodils out, for the past 3 weeks, looks like Spring is finally here.
Thankyou for what you are doing.
Regards Colin Pattrick

[orange]

Colin,

Good stuff. Glad to hear Spring has arrived.

TestShellDir must be reading a Directory, But I have no idea how. (Looks like this is the part you are working on?)

That's correct, but I think the better approach is to use Access/vba and look through the Folders and Subfolders. That is the purpose of the other button shown on the form.

Here is the code behind the Click event of that button.

'---------------------------------------------------------------------------------------
' Procedure : btnRegVBA_Click
' Author    : mellon
' Date      : 13-Mar-2017
' Purpose   : This code executes when the button btnRegVBA is clicked
' General logic is:
'-Create a time stamp
'-Call the GetAllFiles routine to recursively get all files from starting folder and
'  all of its subFolders
'-Inform user of completeion of the filelist
'-Relink thetext file as a liked table for use in the database
'-Coomplete timestamp and provide Message to user
'---------------------------------------------------------------------------------------
'
Private Sub btnRegVBA_Click()


10  On Error GoTo btnRegVBA_Click_Error

20  Dim timestart As Date: timestart = Now  'get the time when this process starts
      Dim timeEnd As Date
30  Me.txtMsg.Visible = False   'don't show the text box where the message goes

    '   call the routine to get all files from this directory and all sub directories
    '   then write the file to a known location
40  GetAllFiles

50  Me.txtMsg.Visible = True   'show the txtMsg message area

60  Me.txtMsg = Now & " -Image File List created for Folder(s) and File(s) using VBA"  'fill the txtMsg box with info

    'relink the filelist file as a table in the database using a file specification
70  Call RelinkText("C:\users\mellon\documents\TestImages\FileList.txt", "MyLatestJpgs", "MyLatestJpgs Link Specification")

80  timeEnd = Now       'get the tiime when the file acquistion and table relink have finished
    ' then provide a message to user of the elapsed time.
90  MsgBox "Elapsed time " & DateDiff("s", timestart, timeEnd)

100 On Error GoTo 0
110 Exit Sub

btnRegVBA_Click_Error:

120 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure btnRegVBA_Click of VBA Document Form_frmRegenImageFileList"
End Sub

And here is the code that is executed when Call RelinkText is processed (line 70 in the procedure above)

'---------------------------------------------------------------------------------------
' Procedure : RelinkText
' Author    : mellon
' Date      : 13-Mar-2017
' Purpose   :
'Set up the relnk routine with parameters
' sTextFilename As String ---the fullname of the text file contain the ImageFile full path info
' sTableName As String-------the name of the linked table within the database
' sSpecName As String--------the name of the specification to be used for the relink
'---------------------------------------------------------------------------------------
Sub RelinkText(sTextFilename As String, sTableName As String, sSpecName As String)


    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
10  On Error GoTo RelinkText_Error

20  Set db = CurrentDb
30  For Each tbd In db.TableDefs
40      If tbd.Name = sTableName Then
50          db.TableDefs.Delete sTableName
60          Debug.Print Now & " -Link to Table (" & sTableName & ") was deleted  "
70          Exit For
80      Else
90      End If
100 Next tbd
110 DoEvents
120 DoCmd.TransferText acLinkFixed, sSpecName, sTableName, sTextFilename, True
130 Debug.Print Now & " -Table(" & sTableName & ") was relinked to file (" & sTextFilename & ")"
140 Debug.Print Now & " -Using spec : " & sSpecName
150 On Error GoTo 0
160 Exit Sub

RelinkText_Error:

170 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure RelinkText of Module ModuJED"

End Sub

Note that none of this is meant to overwhelm, but to give you some vba to look at and try to work through since its part of the process of getting the image file names from the file system.

I am working on a few other things but will try to get something that will simplify some set up.

Colin, the code here is for you to review / understand what is being done. It is not meant for you to install/use.
I'll get to that.

[pattrickcolin]

Thank you very much.
This will be my bed time reading ha ha.
Have a good night. Colin

[orange]

Colin,
Did you get the email I sent?

[pattrickcolin]

Just searched for it and found it amongst heaps of others. Sorry, but it didn't have your username 'Orange' just your real name, so I missed it.
Its late evening now, so its bedtime reading.
Will seriously look at it Friday evening.
A big Thankyou.
The reason for the delay is, I a doing a presentation to 50 Veterans about my Dads ship that sank in WW11.

[orange]

Good luck with your presentation.
/orange

[pattrickcolin]

So what I understand:-
In the same folder, as my database resides, I place a file called “FileSysImageName”
When this file is run, it generates a text listing of all my image files as one big listing called ‘FileList’, this is a text list

Within my database I create a new table called ‘tblImage’. This table contains two fields, autonumberID and [ImagePath]
I create another table called ‘MyLatestJpegs’ (Note all my images are jpegs). This table is contains a field that is linked to ‘FileSysImageName’. So when ‘FileSysImageName’ is run then it will contain a listing of all my image files.

On a Form within my database I will place Two buttons (as discussed Previously). This will automate the process of gathering the Image Files, de-linking from previous lists and re-linking to the latest listing.

The relinking is handled by a procedure called MSysIMEXSpecs and MSysIMEXColumns. This is well out of my knowledge Zone, and I don’t think my Library book will help. However the meanings behind the procedures and what they do is within my comfort zone.

Points to note.
My image files and Program Files are all held within a Folder called ‘Sync’
So the [PATH]=”C:\Users\User\Documents\Sync”
Inside the ‘Sync’ Folder resides folders called:-
‘Colins Master Files & Programs’,
‘Colins BackEnd Data File’
And ‘Colins Stamp Images’
The first two are self explanatory.
‘Colins Stamp Images’ Folder contains a list of ID numbers which represents [CountryID]
Inside each [CountryID] are all the Image file names, These file names made up of [Prefix]& [Number]& [Suffix].
So my full [IMAGEPATH] is made [PATH] & [CountryID]& [Prefix]& [Number]& [Suffix].
This is how the image is called for on Continuous Forms, (or wherever I need the image.)
There are NO further Subdirectories within ‘Colins Stamp Images’

Comments
I am in my comfort Zone to produce a list of all records on file, over 600,000 records, that I should contain an image.
The reality is that Image files held outside the database is only half this amount.
So with the procedures outlined above it will compare the two lists.

A question will it trap File Names Held as [MyLatestJpegs] that Do not appear in my database [ImagePath]

“ A big thank you for work done to date and Yes my talk to Pensioners from our local Supermarket Group went down really well.
I talked about “The 1,300 Days of the Strathallan” a ship my Dad served on during the war.
Built for 1,500 people, lifeboats for 2,500.
She sailed with 5,100 people aboard. Torpedoed at 2-30 in the morning December 1942, when approx. 2,000 people manned lifeboats and 3,000 people remained aboard with no means of salvation. Over 24 hours, destroyers came alongside and rescued the 3,000 remaining, including Dad. The ship took 26 hours to sink. The Commonwealth War Graves lists 16 Deaths. Its been called ‘The Greatest Story, Never Told’ but its not newsworthy like the Titanic, because they all survived. Strange old world. “