472,126 Members | 1,594 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,126 software developers and data experts.

automatically update an Access table by a files listing

I looked everywhere on the net but I can't to find an answer, so I come to solicit your help .
I have a directory "D: \ Books", all books are in pdf (book1.pdf, book2.pdf ... book500.pdf).
I have a very very simple database for these books (I will try to complexify this database later (with several tables, relations, etc ...)) but for now I prefer to take things in order which suits my neuron; It is in Access 2016 and contains a table "tBooks" with just 2 fields: "Num_Book (Key) and" Book_name ", the names of the records correspond perfectly to the names of the files (book1.pdf, book2.pdf ... book500 .pdf); There is a form "fBooks" based on the table "tBooks"; If I add or remove books in the "D: \ Books" directory how can I automatically update my "tLivres" table just by clicking on an "Update" command button in the associated "fBooks" form ; I just want to update the table: if there is no new book added to the directory then no new record is created in the table; If the added book does not exist in the table, then a new record is added to the table, if a book is removed from the directory then the corresponding record is removed from the table.
Thanks in advance, I have been rowing for weeks ...
Aug 1 '17 #1
2 1325
1,430 Expert 1GB
I'm not going to give you the full answer, but this is the tricky bit. With a bit of modification, that will create your table.

Remember to have BookName as No Duplicates

If you get stuck, come back

Expand|Select|Wrap|Line Numbers
  1. Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
  2.     Optional bIncludeSubfolders As Boolean)
  3.     'Debug.Print ListFiles("G:\SBEAM", "*.csv", True)
  4.     '? ListFiles("E:\Phil Data\Access\Mdb 2010", "*.PDF", True)
  6.     On Error GoTo Err_Handler
  7.     'Purpose:   List the files in the path.
  8.     'Arguments: strPath = the path to search.
  9.     '           strFileSpec = "*.*" unless you specify differently.
  10.     '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
  11.     '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
  12.     '               The list box must have its Row Source Type property set to Value List.
  13.     'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
  14.     Dim colDirList As New Collection
  15.     Dim VarItem As Variant
  17.     Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
  19.     'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
  21.     For Each VarItem In colDirList
  22.         Debug.Print VarItem & " " & GetFileDate(CStr(VarItem))
  23.     Next
  25. Exit_Handler:
  26.     Exit Function
  28. Err_Handler:
  29.     MsgBox "Error " & Err.Number & ": " & Err.Description
  30.     Resume Exit_Handler
  32. End Function
And the FillDir Routine is
Expand|Select|Wrap|Line Numbers
  1. Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
  2.     bIncludeSubfolders As Boolean)
  3.     'Debug.Print ListFiles("H:\SBEAM", "*.Csv", True)
  4.     'Build up a list of files, and then add add to this list, any additional folders
  6.     Dim strTemp As String
  7.     Dim colFolders As New Collection
  8.     Dim vFolderName As Variant
  10.     'Add the files to the folder.
  11.     strFolder = TrailingSlash(strFolder)
  12.     strTemp = Dir(strFolder & strFileSpec)
  13.     Do While strTemp <> vbNullString
  14.         colDirList.Add strFolder & strTemp
  15.         strTemp = Dir
  16.     Loop
  18.     If bIncludeSubfolders Then
  19.         'Build collection of additional subfolders.
  20.         strTemp = Dir(strFolder, vbDirectory)
  21.         Do While strTemp <> vbNullString
  22.             If (strTemp <> ".") And (strTemp <> "..") Then
  23.                 If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
  24.                     colFolders.Add strTemp
  25.                 End If
  26.             End If
  27.             strTemp = Dir
  28.         Loop
  29.         'Call function recursively for each subfolder.
  30.         For Each vFolderName In colFolders
  31.             Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
  32.         Next vFolderName
  33.     End If
  35. End Function
Aug 1 '17 #2
Many thanks for your very quick response .

I try today or tomorrow (because job...) and I send an S.O.S. if I'm flooded .
Aug 2 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.