Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    file name import (2003)

    I use this code (from this forum) to place path and file name into a table(image) to reference a photo.

    Const strPath = "FrontofficecMy DocumentsAccessPhoto"

    Dim strFile As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("image", dbOpenDynaset)

    strFile = Dir(strPath & "*.*")
    Do While Not strFile = ""
    rst.AddNew

    rst![photo] = strPath & strFile

    On Error Resume Next
    rst.Update
    On Error GoTo 0

    strFile = Dir
    Loop

    rst.close
    Set rst = Nothing
    Set cnn = Nothing

    DoCmd.close


    End Sub

    Two questions 1-this code often takes 5 min to run when transferring less than 100 names to a table with less than 5000 entries. Access is often locked while the code is running. Why is the code so slow to execute?
    2 after I run this code I then run an update query to assign an ID based on the photo file name e.g FrontofficecMy DocumentsAccessPhoto00979.jpg is assigned ID 979 in image table
    SQL:UPDATE [Image] SET [Image].PatientID = Mid([photo],45,3)
    WITH OWNERACCESS OPTION;

    Can I combine both processes name transfer and update query in a single subfunction?

    Thanks
    nebbia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file name import (2003)

    I'll do some testing for question #1, but here is an answer to #2:

    Yes, you can set the ID when adding the record:

    ...
    rst.AddNew
    rst!photo = strPath & strFile
    rst!PatientID = Mid(rst!photo, 45, 3)
    ...

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file name import (2003)

    Thank you I hope there is a solution to (1)

    nebbia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file name import (2003)

    I tested by adding the file names from a folder with 3,200 files to a table with 15,400 records, and generating the ID field at the same time. It took 0.12 seconds. If the code also had to fill an AutoNumber field, it took 0.15 seconds. So the code isn't the problem.

    However, the folder I used was located on my hard disk. Processing a network folder will always take more time than processing a local folder, but unless your network is very slow, it shouldn't take 5 minutes.

    Some things to try:
    - Compact the database before running the code (Tools | Database Utilities | Compact and Repair Database).
    - If the database is stored in a network folder, copy it to hard disk, run the code there, then copy the database back to the network folder.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file name import (2003)

    Following your suggestions, I have no trouble whatsoever on my local drive. I do not have access to the network over the weekend but I will try on Monday. Thanks
    nebbia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •