update a table from files of directory and sub directories

[bip54]

Hello,
First excuse me for my bad english language
I'm unable to solve this problem despite searches in my head and on the net .

I have an Access 2013 Database with only one table "tBooks", this table has 4 Fields "N°", "Titles", "Keywords" ans "Personal", there is a Form "fBooks" based on a request "rBooks" to filter my Books by part of the Tittle or a Keyword but the problem is not here, it works well at this level .
The Field "Titles" is filled out with the complete name of the *.pdf files contained in a directory "K:\Books" with 2 subdirectories "\Actual" and "\Ancient" ; I have filled out my table by hand (500 books : 200 in "K\Books" , 150 in each sub Directory ...) and I search a way to automatically update my Field "Titles" just by clicking a button on the Form : If there is a new file in the Directory "K\Books" or in a sub Directory, a new entry is added in the Field "Titles" of the Table "tBooks", if the book allready exists in the Table, no entry is added and, if it is possible a message appears with the name of the files that are not added .
I have not the necessary skills for this job .
Many thanks for your Help

[orange]

Can you give us descriptions for each of the fields in your table?
Is Title the filename of the .pdf?
Where do Keywords come from?

You can write a description of your issue/opportunity in you native language, then go to Google translate and get the English equivalent and post it. Your English seems more than adequate for communication (to me), but the post lacks some details( again in my view).

Can you provide a few records from your table to show readers some "real" data?

Good luk.

[bip54]

Can you give us descriptions for each of the fields in your table?
Is Title the filename of the .pdf?
Where do Keywords come from?

You can write a description of your issue/opportunity in you native language, then go to Google translate and get the English equivalent and post it. Your English seems more than adequate for communication (to me), but the post lacks some details( again in my view).

Can you provide a few records from your table to show readers some "real" data?

Good luk.

OK Here is the Table tBooks in 2 modes :

Files.pdf are in french

Creation


Worksheet


On the Worksheet you can see the Field "Title" who is occupied by the exact name of the books\files with their .pdf extension , the corresponding files being placed in a directory "K\Books" and 2 sub directories "K:\Books\Actual" and "K:\Books\Ancient" . The two other Fields ("Key Words" and "Resume" ) are only comments stocked in Access and are not part of a file .

Thanks in advance

[orange]

OK,
What exactly will you do with this database? That is, what is the purpose of the database?
Who will use it?

It seems Anne Wilson is the author, correct? Is author important to your "business"?
Can a book have more than 1 Author?

How do you determine/identify Keywords? What is the ultimate use of Keywords?

[bip54]

OK,
What exactly will you do with this database? That is, what is the purpose of the database?
Who will use it?

It seems Anne Wilson is the author, correct? Is author important to your "business"?
Can a book have more than 1 Author?

How do you determine/identify Keywords? What is the ultimate use of Keywords?

I have built this Database by adapting and combining free samples on french sites but I'm blocked...

1 - I simply want to rank the e-cookbooks of me and my Wife and question the base to find books by Tittle or by Keyword
2 - Anne Wilson is the author of some books but we have books from differents authors and Editors
3 - Author or Editor is usefull because some books have the same Title but have differents authors or Editors
4 - I determine Keywords by Title and flipping the books
5 - The ultimate use of keywords It is to question the database more finely than by the only titles
6 - On the Form where I question the Database, I have put a button that open the book.pdf at the page I want . It works and it is why I must keep the same name for the file in the directory and the record in the Database .

here is the Form :

[orange]

A few things to consider:

If you have the names of these e-cookbooks (*.pdf) in folders ,
you can get a current list of those names/cookbooks using the command window
and a command along this line

DIR /s/b K:\Books\*.pdf >C:\Documents\MyPdfList.txt

which will place all of the File names with Path into a file MyPdfList.txt

You could import the data in that file directly into Access.

You could create a function to parse the filenames into pieces and (depending on your needs) build keywords from each filename from pieces 5 or more characters in length. In addition you could add additional keywords during a review of your holdings. You could have a list of "possible keywords" that you could assign to titles during a review(s).

It depends on the volume of titles, the frequency and volume of searches etc.

You might consider another table tblBookHasKeyword that consists of

BookID (your No)
KeywordID ( a unique id for each keyword)

which would allow you to have multiple keywords per title and use multiple keywords to identify a small set of Books (focused search) using multiple keywords.

You may get some ideas from this thread on keywords/fragments.

You can restrict the entry of duplicates by use of a unique composite index.


Bonne chance.

[bip54]

A few things to consider:

If you have the names of these e-cookbooks (*.pdf) in folders ,
you can get a current list of those names/cookbooks using the command window
and a command along this line

DIR /s/b K:\Books\*.pdf >C:\Documents\MyPdfList.txt

which will place all of the File names with Path into a file MyPdfList.txt

You could import the data in that file directly into Access.

You could create a function to parse the filenames into pieces and (depending on your needs) build keywords from each filename from pieces 5 or more characters in length. In addition you could add additional keywords during a review of your holdings. You could have a list of "possible keywords" that you could assign to titles during a review(s).

It depends on the volume of titles, the frequency and volume of searches etc.

You might consider another table tblBookHasKeyword that consists of

BookID (your No)
KeywordID ( a unique id for each keyword)

which would allow you to have multiple keywords per title and use multiple keywords to identify a small set of Books (focused search) using multiple keywords.

You may get some ideas from this thread on keywords/fragments.

You can restrict the entry of duplicates by use of a unique composite index.


Bonne chance.

Thanks for your response but my purpose is lightly differnt ;

In fact my database works correctly, I am only looking for the code to automatically add in the "Title" field (by clicking on a button) the name of the new .pdf files that I add to the K: \ Books folder or in one of the K \ Books \ Actual or k: \ Books \ Ancient subfolders.

For example :
There are 500 different books distributed in my folder K: \ Books and subfolders K: \ Books \ Actual and K: \ Books \ Ancient : 1.pdf, 2.pdf, ....... 500.pdf so there are 500 entries with the same names in my "Title" field: 1. Pdf, 2.pdf, ....... 500.pdf
Today I add 5 files: 501.pdf, 502.pdf in K: \ Books, and 503.pdf, 504.pdf, 505.pdf inK: \ Books \ Actual
By clicking a button, the program reads the directory K: \ Books and the subdirectories K: \ Books \ Actual and K: \ Books \ Ancient and then compares the file names of these directories and sub directories with the names of the records in the field "Title" of the database, if the name already exists, it does not add a record to the 'Title' field, if the name does not exist (here 501.pdf, 502.pdf, 503.pdf, 504.pdf, 505.pdf), it adds the corresponding records to the "Title" field (here 501.pdf, 502.pdf, 503.pdf, 504.pdf, 505.pdf), which makes it possible to update the database adding only the new books by clicking on a single button, avoiding entering new books by hand

And I'm really unable to produce such a code, it is too much complicated for me, I try since days ...
Thanks in advance

[orange]

bip54,

Thanks for the clarifications.
If you load a pdf file called 502.pdf, where do you get the "Title" from??
Some details about your process would help readers.

My understanding from your posts was that the filename was the title.

[bip54]

for the moment I simply and manually copy the full name of the files (picked in the directory) and create a new record for each file to the Field "Tittle" ; I made this 500 times...pfff

In example in the directory K\Book, I copy the name of the file "502.pdf" anf I paste it in a new record in the field "Title" of the table "tBook", that's why I want to make the process automatic .

[aytee111]

This will list your files. Copy this into your code and get it working. The file name is in "varitem", here it prints the name in the immediate window, you can change it to make a temporary table and then use that to update your list of books, queries showing missing titles, etc.

http://allenbrowne.com/ser-59.html

[bip54]

This will list your files. Copy this into your code and get it working. The file name is in "varitem", here it prints the name in the immediate window, you can change it to make a temporary table and then use that to update your list of books, queries showing missing titles, etc.

http://allenbrowne.com/ser-59.html

Many many many thanks !!!

I just tried it and it's good .

Simply could you help me for the code to print the files in a temporary table and not in a message box ; for the rest (queries) there is no problem .

[aytee111]

Dim rstOut as Recordset
Set rstOut=Currentdb.Openrecordset("tablename",dbOpenDynaset)
DoCmd.RunSQL "DELETE * FROM tablename;"
....
For Each varItem In colDirList
  rstOut.AddNew
  rstOut!fieldname=varitem
  rstOut.Update
Next
....
Set rstOut=Nothing

[bip54]

Dim rstOut as Recordset
Set rstOut=Currentdb.Openrecordset("tablename",dbOpenDynaset)
DoCmd.RunSQL "DELETE * FROM tablename;"
....
For Each varItem In colDirList
  rstOut.AddNew
  rstOut!fieldname=varitem
  rstOut.Update
Next
....
Set rstOut=Nothing

Ok
I think I understood:

1 - a loop reads the files in the directory
2 - she writes their names in a temporary table
3 - the program compares the records of the two tables (the temporary and the "Title" table)
4 - a request tells him to write only new files in the "Title" table

Thanks again, I'm saved (I think... )

[aytee111]

Yes, that would be the process. Well done!

[bip54]

Hello,

I'm trying since more than one month to write a good code, but i'm really unable to do that , i have the steps :

1 - a loop reads the files in the directory
2 - she writes their names in a temporary table
3 - the program compares the records of the two tables (the temporary and the "Title" table)
4 - a request tells him to write only new files in the "Title" table

but I can't translate that in code ... I'm so sorry ; That exceeds my weak skills ; I only need this to complete my Database .

Please could you help me by giving me the code .

Thanks in advance