UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Save Files To A Table And Read Them Back Out, Access 2016    
 
   
RCDAWebmaster
post Jan 17 2020, 03:42 PM
Post#1



Posts: 81
Joined: 27-August 19



I have a db that I'm building that will be used by the HR department at my company to fill open positions. It will hold Jobs and applicants. Part of the applicant's data is a copy of their resume in PDF/word/other format.

The back end database is SQL server. The front end is Access 2016. The tables in SQL server are linked into access. I did some research on the web and found out that the field type for a file in SQL server is image. When the table is linked into access, the file field is seen as an OLEobject.

I created a file picker and can select the file to store into a text field. I can't though, figure out how to store the file. I read somewhere about ADO objects, but then found out that they no longer exist. I then found out that I need to use ADODB objects, but do not understand how to do this. Can someone explain this.

On the flip side, I need to be able to read the resume back out, display a preview on the form (single record with record selectors if multiple resumes) and give the HR dept the ability to right click the preview image and open the file.

I know this is doable as I have a copy of a database they use that has this functionality, but for the life of me I'm not sure how it works.
Go to the top of the page
 
theDBguy
post Jan 17 2020, 03:49 PM
Post#2


UA Moderator
Posts: 77,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. I couldn't find a better link at the moment, but take a look at this. Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Jan 17 2020, 03:56 PM
Post#3



Posts: 1,221
Joined: 25-January 16
From: The Great Land


Could they give you the code from that db?

Bing: access vba save extract oleobject adodb.stream

Review:

http://lebans.com/oletodisk.htm

https://stackoverflow.com/questions/4870928...-field-with-vba

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RCDAWebmaster
post Jan 17 2020, 04:04 PM
Post#4



Posts: 81
Joined: 27-August 19



I have a copy of the database and am trying to step through all the screens to see how it does what it does. the problem is that the person that created the DB is not available to explain how it functions.

I will take a look at the examples that were provided and see if I can come up with a solution that works. Thanks
Go to the top of the page
 
June7
post Jan 17 2020, 04:15 PM
Post#5



Posts: 1,221
Joined: 25-January 16
From: The Great Land


If db is accdb then should be able to open VBA editor window and view code.



--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RCDAWebmaster
post Jan 17 2020, 04:20 PM
Post#6



Posts: 81
Joined: 27-August 19



That is true. I can open VBA editor and view the code, I'm just having trouble locating the forms the code is attached to. I didn't build it and there are 65 forms to look at. It will take some time. I guess I was hoping to shortcut this Db and get a code sample from the net somewhere.

The other problem is that this db uploads all files in a hard drive directory as multiple records in a table rather than selecting a single file and uploading it as a single record determining first if the file exist so there are no duplicates.
This post has been edited by RCDAWebmaster: Jan 17 2020, 04:22 PM
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2020, 09:58 AM
Post#7


UA Admin
Posts: 36,770
Joined: 20-June 02
From: Newcastle, WA


Have you considered a 3rd party addin to manage images?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RCDAWebmaster
post Jan 22 2020, 08:59 AM
Post#8



Posts: 81
Joined: 27-August 19



I have not considered a 3rd party add-in. I'm looking for a pure code solution that allows me to store a file in the DB and query it back out. I understand that the underlying DB is SQL server, but the tables are linked in Access and I'd like to stick with Access here. I know that in access the field is type OLEObject so I's like to find code that lets me work with the field in access.

The questions I have are:
1. Once I get the code to stream the file in and out, how do I push and pull the other fields in the record?
2. How do I check that the file I'm pushing to the DB is not already in there? It looks like the person who built the db I'm looking at, recorded the file name and folder on the HDD that the file came from as part of the record and checks that to see if there is a match rather than comparing the files directly. Maybe that is a faster way to deal with files, but I'd rather compare file to file.
Go to the top of the page
 
GroverParkGeorge
post Jan 22 2020, 09:07 AM
Post#9


UA Admin
Posts: 36,770
Joined: 20-June 02
From: Newcastle, WA


"...I's like to find code that lets me work with the field in access. "

That's exactly what DBPix does. Please, take a look before rejecting it out of hand. The cost of the add-in may be favorable to the hours spent in developing your own custom solution.

"How do I check that the file I'm pushing to the DB is not already in there? "

and

"...but I'd rather compare file to file. "

I think that's possible, but I have to wonder how efficient that's going to be. Let's say your table holds ten images. You have to retrieve each one for the comparison against a potential new image. Okay, a few seconds per image for the comparison. Maybe thirty seconds to one minute?

Now, let's say your table holds one thousand images. You still have to retrieve each one for the comparison....

I wonder just how that is going to perform for your users. I may be wrong, but I am worried you'll not be too happy with the results.
This post has been edited by GroverParkGeorge: Jan 22 2020, 09:13 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RCDAWebmaster
post Jan 22 2020, 11:12 AM
Post#10



Posts: 81
Joined: 27-August 19



Regarding DBPix, that would be a good tool to have in my arsenal. The goal of the exercise is not just to make a functional database but to show that I can learn to do what the current DBA has been doing.

I'm currently learning to be her back up and the boss is planning to have me take on her job tasks on a permanent basis when she retires in the next year... That is if I can prove that I know how to work with databases.

The original goal was to tare apart an existing access DB, find the code and reuse it. I'm having trouble doing that since I didn't build it and am not familiar with how files are handled. Since I am struggling, I thought I could find a code example on the net and make it work.
Go to the top of the page
 
GroverParkGeorge
post Jan 22 2020, 11:18 AM
Post#11


UA Admin
Posts: 36,770
Joined: 20-June 02
From: Newcastle, WA


Ah, I see. So this is part of a learning program, not a specific work project.


Daniel has a good resource on his web site.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Jan 22 2020, 11:30 AM
Post#12


UA Moderator
Posts: 77,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. I've been sitting back for a while and was just wondering if you had a chance to look at the link I posted earlier.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RCDAWebmaster
post Jan 22 2020, 11:33 AM
Post#13



Posts: 81
Joined: 27-August 19



I did look at it and it connects to the SQL server directly. I couldn't adapt it to work with the Access Table in my database.
Go to the top of the page
 
theDBguy
post Jan 23 2020, 12:27 PM
Post#14


UA Moderator
Posts: 77,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. I'm not sure I understand. Are you creating a local copy of the tables and data from SQL Server into your Access database? Otherwise, if your Access database is connecting directly to SQL Server tables, then I think you should be able to use that code.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jan 23 2020, 02:06 PM
Post#15



Posts: 3,472
Joined: 27-February 09



I think you mean something like this?

INSERT INTO dbo.Consultants (ConsultantName, ResumeDoc)
SELECT 'Steve', * FROM OPENROWSET (BULK N'D:\Steve_Resume.docx', SINGLE_BLOB) Doc

See:
https://sqlserverinsight.blogspot.com/2013/...rver-table.html

pretty sure you'd parameterize the INSERT using a stored procedure and then pass the path to the file as a string value (VARCHAR(200) or so). and then pass that into the SELECT statement above.

Go to the top of the page
 
RCDAWebmaster
post Jan 23 2020, 02:22 PM
Post#16



Posts: 81
Joined: 27-August 19



I actually figured out how to manage this without any code.

I created a query based upon the table that holds the resumes.

tblResumes
---------------
ResumeID Autonumber
ResumeFile OLE Object
ResumeDate Date/Time
Notes Long text
Applicant_ID Integer

This query gets all resumes ordered by date.

I then use the query as the record source of a sub form on the applicants form. I link the main form and sub form on applicant_ID. This shows me the resumes that have been stored for that applicant. I can then use the record navigation buttons to get to a new record. If I right click on the OLE Object field, I select Insert Object and choose the resume file. I can then give the file a date and note if applicable. The applicant_ID for new records comes from a hidden filed on the applicant form. Every time a new recoerd is added, the form requeries to update the order by date descending.

My followup question is this: I want to add a filename field to my table so that I can check to see if the file already exists in another record before adding the file.
IE: right click the field... Insert file... from file... select file... warning "Resume already exists. Try again."

What I need to know is: is there a way to get the filename when I select a file and compare it to existing records. or maybe when I go to save the record it could check for duplicates and delete the duplicate with higher ID.
This post has been edited by RCDAWebmaster: Jan 23 2020, 02:23 PM
Go to the top of the page
 
RCDAWebmaster
post Jan 27 2020, 09:02 AM
Post#17



Posts: 81
Joined: 27-August 19



As it turns out, I need to use code to make this work. Looking at the DB that I am mirroring I found the following code snippet:


DoCmd.GoToRecord , , acNewRec
txtHoldFDate = Forms!frmFileDialog.txtFindDate
txtHoldNotesID = Forms!frmFileDialog.txtHoldNotesID

Dim fld_nam As String
Dim fld_path As String
Dim fld_file As String

fld_nam = Forms!frmFileDialog!txtFindPath

fld_path = fld_nam & "\" & "*.*"

fld_file = Dir(fld_path, vbNormal)

Do While Len(fld_file) <> 0
txtTSFileLocation = fld_nam
txtTSFileName = fld_file
txtNotesID = txtHoldNotesID
txtDateCreated = txtHoldFDate
oleTSFile.Class = OLEClass
oleTSFile.OLETypeAllowed = acOLEEmbedded
oleTSFile.SourceDoc = fld_nam & "\" & fld_file
oleTSFile.Action = acOLECreateEmbed
fld_file = Dir()
DoCmd.RunCommand acCmdRecordsGoToNew
Loop

DoCmd.Minimize
MsgBox "Files have been saved."
DoCmd.Close acForm, "frmUserDioNotes"


My questions are many:

1. How do I associate oleTSFile with a table field?
2. How do I insert the other fields into the record as I load the file into the table?
3. Does fld_file() = dir() actually cause the upload to happen?
4. Are any of the items in the code user defined since non of this makes much sense?




Go to the top of the page
 
RCDAWebmaster
post Jan 27 2020, 09:18 AM
Post#18



Posts: 81
Joined: 27-August 19



After looking at the code and the form, I'm seeing what was done...

There is a form that has a single record on it. The form simply goes to a new record and populates the new record based upon other form fields: path, file name, notes...

This type of form filling allows for a search of the underlying table to find duplicates and alert the user. The thing that I don't understand is why the designer chose to use the folder the file was saved from and the file name to catch duplicates. In my case, I'd rather look at the file and see if there is a duplicate. I could pull a query to check the table for records associated with a single individual then look at the resumeFile field for duplicates. This shouldn't take too much time since there would be few resumes on file for each person.

Now I'm off to try and make it work...
Go to the top of the page
 
MadPiet
post Jan 27 2020, 10:21 AM
Post#19



Posts: 3,472
Joined: 27-February 09



OLE Object....

What version of SQL Server are you using? Since Word documents are XML files, why not use an XML type to store the data? Because then to query the resume you'd use XQuery?
Go to the top of the page
 
RCDAWebmaster
post Jan 27 2020, 10:22 AM
Post#20



Posts: 81
Joined: 27-August 19



I'm getting as far as the line of code that performs the embed (ResumeFIle.Action = acOLECreateEmbed) and that's where it breaks with the error: Run-time Error '2793' Microsoft Access can't perform the operation specified in the action property of the visual basic procedure your trying to run.

My Code:

CODE
     If Forms!frmApplicants!txtSelectedFile = "" Then
         MsgBox "You must select a resume to upload. Try again.", vbCritical
         Forms!frmApplicants!txtSelectedFile.SetFocus
         End
     End If

     Forms!frmApplicants!frmSubApplicantsResumes2.SetFocus
     DoCmd.GoToRecord , , acNewRec
    
     Forms!frmApplicants!txtSelectedFile = Forms!frmApplicants!txtSelectedFile
     Forms!frmApplicants!frmSubApplicantsResumes2!ResumeDate = Forms!frmApplicants!date
     Forms!frmApplicants!frmSubApplicantsResumes2!Notes = Forms!frmApplicants!txtNotes
     Forms!frmApplicants!frmSubApplicantsResumes2!FileName = Forms!frmApplicants!FileName

     Forms!frmApplicants!frmSubApplicantsResumes2!ResumeFIle.Class = OLEClass
     Forms!frmApplicants!frmSubApplicantsResumes2!ResumeFIle.OLETypeAllowed = acOLEEmbedded
     Forms!frmApplicants!frmSubApplicantsResumes2!ResumeFIle.SourceDoc = Forms!frmApplicants!txtSelectedFile
     Forms!frmApplicants!frmSubApplicantsResumes2!ResumeFIle.Action =  acOLECreateEmbed

     If Me.Dirty Then
         Me.Dirty = False
     End If



I can't see why that line is failing. I set the file class, the type allowed to all, the source of the file
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 06:51 PM