Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22

    Unanswered: Will it make a significant space difference to display OLE objects as icons instead?

    I have a form called Employees with a few tabs. Two of the tabs show different biographical data and the other tabs have OLE fields for scanned documents related to the employee, such as work visa, etc. I love being able to just click on the tab and immediately see the scanned document without having to click or open or do anything. However, it is becoming apparent that with the volume of employees we will be tracking in this database (over 500), that we are going to have major issues with space because Access can't go over 2MG.

    I already split the database into front and back-end databases. I even gave each OLE field it's own separate backend database. I only have about 250 OLE objects in one of the fields so far (one that has its own backend database) and that backend database is already at 1.58 MG, so I know this isn't going to work.

    If I were to change it so that the OLE field just displays the icon, how much difference would it make?

    My co-worker was talking to a consultant about something else but asked him for me what some of our options might be besides converting to a SQL database. He told him that we could put all of the scanned documents into a folder on our server and have them all named consistently, for example with an identifying prefix and name, and have Access get the employee name from the record and go and pull up the file from that folder. The only thing is I don't know where to start with this. Does anyone know where I might be able to find some information, or has anyone ever tried something like that before?

    If just saving them all as icons can save me the trouble of trying to figure out how to do the above, then I think the work will be worth it, but I don't want to start doing that without knowing if it will even make a difference.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Not sure how storing just an icon would help. Where will you fidn the actual document?

    I use two methods for document management:

    1) store the files externally and only store the path name.

    2) Store the file as a BLOB. You still have to extract the file to the hard drive to use it.

    I have created this example:

    Document Links 2

    Hopew this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22

    Thumbs up

    That example is AWESOME, thanks. I'm going to poke around in your example and see if I can figure out how to get mine to do that. Thanks so much.

  4. #4
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    Hi Tech Coach, I used the code that you had in your "Select File Path" button, replacing the field name "DocPath" with the field name "Crapola" because I made a field to practice in to see if I could get it to work. When I try to run it, it says: Compile Error: Sub or Function Not defined.

    Here is the code exactly as I have it:

    Private Sub Command197_Click()
    Dim strCrapola As Variant

    If Me.Crapola > "" Then
    strCrapola = GetOpenFile(Me.Crapola)
    Else
    strCrapola = GetOpenFile(Me.txtFolder)
    End If

    If strCrapola > "" Then
    Me.Crapola = Replace(strCrapola, Me.txtFolder, "%dir%")
    End If
    End Sub

    Any idea what I'm doing wrong?

  5. #5
    Join Date
    May 2010
    Posts
    601
    Did you import the code modules?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    No.

    Pardon my complete and utter ignorance, but how do I do that?

  7. #7
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    Never mind, I think I figured out how to import it. Now I have it working.
    Last edited by Glühbirne; 05-28-10 at 13:08.

  8. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Glühbirne View Post
    No.

    Pardon my complete and utter ignorance, but how do I do that?
    I would urge you to make a backup of your database before importing anything. I actually make a ZIP/RAR backup every time before I open a database.

    The way I do it is to use the External data option on the Menu or Ribbon. Then you can select the other databases an import the desired objects.

    It is also possible to drag a object between two open databases. I always recommend making a backup of both databases before attempting this.

    There is also the old cut and paste also. If you do copy and pasted VBA code, I always make sure to do a manual compile before saving
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    Thanks.

    I have another question. Is there a way I could get the file display on the form from the file path I have stored? All of the files I'm working with are PDF, plus one picture file for each record.

    If I could have them display and then have it so that when someone clicked on them they'd open up in full size, that would be wonderful. That's why I originally wanted to use the OLE object field because it did all that, until I realized how huge that was making my database.

    Also, is there a way I might be able to save files from within Access. Lets say that there is no file path in one of the file path fields for a particular record because that file hasn't been created yet. Could I have, for example a button or something, that would open Adobe right up so I could scan and then it would prompt me for a file name and and location to save it but also put that file path into the file path field all at once, so I don't have to leave Access, go scan the file in Adope and save it in the correct location, then return to Access and insert the correct file path?

    Is it even possible? Thanks again for your help. You really helped me today.

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Glühbirne View Post

    ... Lets say that there is no file path in one of the file path fields for a particular record because that file hasn't been created yet. ...
    This raise a big ref flag.

    It sounds like there multiple file path fields in one record.

    Is this true? Hopefully not.

    If it is, then you have some design issues that need to be addressed before you go any further.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    I do have multiple files associated with each record. For each Employee I have, for example, a scanned copy of thier application, scanned copies of thier work documents, and various other files (mostly PDFS) that are documents related to them. I also have a foto associated with each employee. Why is this bad design?

    Or did you misunderstand me to be saying that I have more than one path to each file?

    ?? Please let me know, because if this is bad design, I have no idea how I can get around it. It wouldn't be very convenient to store all of the documents in one single PDF file because we'd always have to page through it to find what we're looking for.

  12. #12
    Join Date
    May 2010
    Posts
    601
    Lets see if I can clears things up some.

    Referring back to Post#2, in the link I provided it shows how to have a single document per record.

    If you need to have more than one document attached to a record, you would use a child/sub table with a one-to-many relationship.

    A sub form could be used to display all the attachments for a single record.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  13. #13
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    Yes, I understand.

    My question is, is there a way that I can make a preview window so that I can preview the documents that I have attached via this method before actually opening them?

  14. #14
    Join Date
    May 2010
    Posts
    601
    I am glad you understand.

    Before continuing, I need your answer to: Do you have a separate record for each attached document?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  15. #15
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    No. I have several attached documents for each employee. They are on a different tab within the same form.

Posting Permissions

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