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.
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)
strCrapola = GetOpenFile(Me.txtFolder)
If strCrapola > "" Then
Me.Crapola = Replace(strCrapola, Me.txtFolder, "%dir%")
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.
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.