By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

automatically update an Access table by a files listing

P: 2
Hello,
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
Share this Question
Share on Google+
2 Replies


PhilOfWalton
Expert 100+
P: 1,430
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)
  5.  
  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
  16.  
  17.     Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
  18.  
  19.     'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
  20.  
  21.     For Each VarItem In colDirList
  22.         Debug.Print VarItem & " " & GetFileDate(CStr(VarItem))
  23.     Next
  24.  
  25. Exit_Handler:
  26.     Exit Function
  27.  
  28. Err_Handler:
  29.     MsgBox "Error " & Err.Number & ": " & Err.Description
  30.     Resume Exit_Handler
  31.  
  32. End Function
  33.  
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
  5.  
  6.     Dim strTemp As String
  7.     Dim colFolders As New Collection
  8.     Dim vFolderName As Variant
  9.  
  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
  17.  
  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
  34.  
  35. End Function
  36.  
Phil
Aug 1 '17 #2

P: 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.