473,715 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

automatically update an Access table by a files listing

2 New Member
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
2 1479
PhilOfWalton
1,430 Recognized Expert Top Contributor
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
bip54
2 New Member
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

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

Similar topics

1
1872
by: Jimmy | last post by:
I have a table that is shown below in ORIGINAL TABLE. It is common for the VEHICLE # field to have repeating vehicles (ID 3,4,5 & 8,9). Usually the Vehicle will have SHIPDATE, DATE_RECEIVED, and DATE_ORDERED for the first entry, after that is could be null (ID 4 & 5). What I would like to do is for a query or VB code to look into the ORIGINAL TABLE, find the duplicating VEHICLES. Where ever DATE_RECEIVED or DATE_ORDERED is empty,...
1
4477
by: Pierre Maricq | last post by:
Hi, I am using Win2000 and Access2000. I need to build build a macro or write a VBA in Access that would screen all files contained in a directory on my C drive (files are structrured DAT files ) and update an Access table with the following information: (1) Filename (variable lenght) (2) Modified File Date/Time
1
2351
by: john_liu | last post by:
How can I update an Access table based on a sheet in Excel by VBA in excel. Thanks
1
3836
by: Esmi | last post by:
I am developing an application in Ms Excel 2003 that has a feature to update records in Ms Access. The problem is the table name I am trying to update contains a space and whenever I run the below code I got the following Error Message. Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT, OR ‘UPDATE’ However, I can’t rename the table because it has many relationships and connections with other tables. Private Sub...
3
2611
by: pizzaface | last post by:
Hello: I'm wanting to update a Microsoft Access Table automatically with the network computer name and user name as soon as a user logs onto his/her computer. I would like this to happen with minimal disruption to the user. I have read about possibly using the Windows Scheduler or autoexec.bat to open an access database, run an autoexec macro, etc...I already know how to get the network computer name and user name. I'm looking for assistance...
4
5178
by: Kelly Warden | last post by:
I want to create a form in Access to update my table automatically with the information selected from combo boxes, lists, comments, etc. Also, I want to automate this w/ some VBA or SQL or something in Access. I want to be able to automatically fill out parts of the form based on some other tables containing different data. Basically in the form, if reason 1 is automatically filled out then its done and no one needs to look at it and it...
2
5191
by: simon penny | last post by:
I have a table in which I record referral records. I have a second table in which I record service records. One referral can have many service records attached. The tables are linked by referral ID - the service table incudes the referal ID field. A representation of the service table follows: Col1 Col2 Col3 Col4 1122 160 01/01/2001 1 1256 160 01/10/2001 2 1899 160 01/06/2004 3 1999 160 02/07/2005 4...
3
1956
by: Daryl Austin | last post by:
I know this can be down, I'm just not sure where to begin. I have a table with fields , basically I am using Access to manage sales proposals. I export out to Excel a report query based on salesagent. I send this to each salesagent for updating. Now I have the returned Excel file from the different salesagents. How do I import or update my original table fields with the new information from the Excel files? Thanks.
1
987
by: jimkay | last post by:
1. table with fields id, LastName, FirstName, Address, City, State. 2. New records added to table are place at the end in last row. 3. Is there a way after a period of time to rearrange the records in alphabetica order by last name without indexing. I wish to have the default table in alphabetical order by last name including all records at startup.
1
1023
by: kevinsnewmatrix | last post by:
This can update daily only. Tuesday thru Thursday are fine as Im using date()-1. but on Monday it wont work as i have to change to date-3 etc. Is there a way to use the Max date from the table Im updating to add in the days 1 by 1? I hope for this to work if Ive been out for a few days or a week.
0
8817
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9193
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9092
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9037
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5964
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4472
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4734
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2531
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.