Using a MEMO field vs a Hyperlink

[liddlem]

Hi there
Looking for a bit of info (Pro's/Con's) regarding the use of a MEMO field vs linking to a doc.
As a school, we often need to manage either a students health, behaviour or learning ETC.(or a combination of these.)

Some requirements
* This involves developing a plan to manage the issue.
* Review the plan at designated dates and (if necessary) update the plan.
* Need to track who LOOKS at a plan (and when)
* Need to track CHANGES in the plan (be able to go back to past versions if required)
* At least ONE report will require the latest version and the one before it.
*Currently, users access this data on the local lan, but I suspect that somewhere in the future, access might be from the cloud?
* Will need to enable RTF as I suspect that tables and formatting will be used quite extensively in the doc.
* Using SQL 2016 back-end / Access 2016 Front End

I am currently looking at a table to store the following
ID_Plan
ID_PlanType - (Behaviour, Health, Learning, etc.)
ID_Student
ID_Staff
DATE_Plan - (Date that plan is developed.)
DATE_Review
DATE_LastUpdated
Is_Open - (Identifies if the plan is still active or not.)
Plan - **** This is where I am undecided and need some input

At first glance, the following may seem like a 'no brainer' in favour of option 1, but what are the caveats/limitations related to the use of memo fields in this way? (IE database performance, copy/paste issues etc)


OPTION 1:
Using a MEMO field for users to edit maintain this data
..IF isnull(me.PLAN) then
....User edits the data accordingly. (Other fields updated 'automatically' by a 'before_Update' event)
..ELSE 'Record the new version
....Copy existing MEMO text and relevant other fields to create a NEW record.
....Paste data to the new record
....Save record accordingly
..END IF


OR OPTION 2:
Allow users develop a word (or any other type of document - Excel/PDF etc) and 'import' it as follows.
This field is just a fancy text field that acts as a pseudo hyperlink.
User double-clicks the 'PLAN' field.
..IF isnull(me.PLAN) then
....Open the FileDialog window for the user to select their file.
....Save the file as (some naming convention applied) to a reserved folder on the network.
....Store the filename in the 'PLAN' field
..ELSE
....MyMsg = msgbox("Do you want to EDIT this document")
....IF MyMsg = 6 then
......I need a whole system of mananging this document? (Perhaps dumping to a specified local directory?)
....END IF
..END IF

Thanks

 

[isladogs]

Yes I think its a no brainer
Go for option 1

Option 1
- much simpler to set up & administer
- no external documents needed

BUT memo fields cannot be indexed and are not fully searchable or sortable

Option 2
- more complex to code
- external programs used so additional complications / more can go wrong

BUT you would have a separate document for use if needed

 

[The_Doc_Man]

And from the other side of the bleachers...

Storing a document in a memo field or as an internal OLE object takes up space very fast compared to having an external file. A hyperlink field with a complex path as part of that link might take up 80 to 160 characters, depending on just how funky your directory structure happens to be. On the other hand, embedding the document in the database quickly eats up your 2 Gb limit because any WORD document with any meat to it measures in the 10s of Kb.

Further, if you have the document named according to the proper Windows associations (i.e. DOCX and DOC for WORD files, XLSX and XLS for Excel etc.), opening the link separately opens the appropriate utility and Access has NOTHING to do with what you do inside that utility.

I need a whole system of mananging this document?

Probably true, and your surmise of using a local document repository makes perfect sense to me. If you were going to use the File System Object to find the document originally, you will be able to copy, rename, move, delete, and otherwise examine the attributes of the document file. Therefore, your management tool would already have been in place.

At least ONE report will require the latest version and the one before it.

Which means if you do Memo or OLE, you just DOUBLED the amount of space you chew up while mucking about with the file. AND... if you are going to edit the file, Access is NOT a text editor. It will be clunky at best and hair-tearing-out frustrating at worst.

I would look towards thinking about a way to make a backup copy of a file by perhaps using an OnClick for the hyperlink field to make an automatic copy to a backup folder before allowing the hyperlink to be followed, regardless of the document format. Then allow the hyperlink to do its thing and activate the correct editor for the file type.

 

[liddlem]

Thanks for both of your replies.
I guess this is a bit like asking the TV station to tell you which shopping store is the cheapest.

While option 1 seems to be the 'no brainer', Doc Man - I like your thinking here.

I would look towards thinking about a way to make a backup copy of a file by perhaps using an OnClick for the hyperlink field to make an automatic copy to a backup folder before allowing the hyperlink to be followed, regardless of the document format. Then allow the hyperlink to do its thing and activate the correct editor for the file type.

Question : Supposing that I did make a backup copy before presenting the doc to the user,
A. How would I be able to ensure that I link to the latest version when the user is done (Since access has no idea when the user closes the relevant app - word/excel etc.)
B. Would I be able to prevent users from making changes, but then saving the doc to a location that the database knows nothing about? Hence losing the link to the most up to date plan.
Do you know if there is a (cost effective) document 'check in/check out' utility that access is able to communicate with?

 

[The_Doc_Man]

but then saving the doc to a location that the database knows nothing about?

Well.... if the database knows nothing about it, where will it ever save it? Perhaps did you mean that the USER knows nothing about?

How would I be able to ensure that I link to the latest version when the user is done

You probably don't care that the user is done because you would make the copy BEFORE the user does the update. To my way of thinking, you set aside a folder and keep the current copy there. That's the one that is ALWAYS current. Force the file to exist there, perhaps by having a check-in that you use to select the file initially and when you do the check-in, you copy it to where you want.

You also can set aside a second folder to hold the most recent backup of the file BEFORE editing. So if you have to undo the last operation, the backup copy is still there.

Would I be able to prevent users from making changes, but then saving the doc

But if they made no changes, what's to be saved?

You ask a good question about knowing whether the user is finished with the document (presumably because someone else wants to use it?). There IS such a thing as opening, say, WORD as an application object and make it visible to the user so that they are IN the MS WORD utility at the time. If that happens, then you have some hope that you would know they are done. However, if they are in WORD, the catch is that they can do a Save As and you wouldn't know where.

If we are talking a shared Front-End situation, you can't look at file locks directly because users generally cannot see them. I know of only two ways to do this.

First, trap a File Alread Open (Error 55), and advising the user who gets that message to try again later.

Second, when someone clicks on the link to the file, have a Yes/No flag in the table to say "File In Use" and SET that when the user enters. You have to then use the App Object method to know when the user is done so you can CLEAR the flag and unlock it.

Do you know if there is a (cost effective) document 'check in/check out' utility that access is able to communicate with?

Offhand, no. However, if you look up "configuration management" and "version management" on the web, some possibilities might show up.

 

[isladogs]

Excellent.
The perfect response to a question with only 2 allowed answers....


Actually I also accept the points made by the Doc and use external docs s lot with my own databases

However its definitely much more work

Both solutions have their own merits

 

[liddlem]

Hi all - just a little feedback re my 'two steps forwards and one step back"
I have been testing "the doc man's" ideas - most seem to work well for what what I want to achieve. IE

docName field Locked = Yes (The user MUST double-click on it to either add a document OR open an existing file)

If isnull(me.docName) then
- Open the filepicker
- Assign the file a new name (according to our requirements) and copy it to the designated location.
- Update the field with the filename only
- Update other fields (Date\time added, username, etc)
ELSE
- Copy the file to a 'backup' folder (appending 'yyyymmdd hh mm_Username' to the filename)
- Open the file
END If

HOWEVER - I was hoping to give the user an opportunity to PREVIEW ONLY (as opposed to opening the file for editing) using the WebBrowser control. (Access 2016)

The problem is that MS \ MS OFFICE is trying to be too clever if its a MS document (doc,docx, xls,xlsx) and asks the user if they want to open or save the file. I just want them to be able to VIEW the contents. It does show a preview for PDFs in the manner that I was hoping to use it. (Would be nice to be able to set the WebBrowser's Locked/Enabled property)

ONLY if they click an 'EDIT' button then open the file.
If I use this approach, then I don't have to make a backup of the file.(Although I may need to record who previewed and when.)

 

[The_Doc_Man]

If you want to preview the file AND you are using a Word App Object, there is a way to open the file read-only. It's the third parameter for the WordObj.Documents.Open function, the first two being file name and "Confirm Conversion."

 

[liddlem]

thanks Doc Man...
Problem is that they might want to use other file formats too (Excel, PDF, JPG, BMP, Audio file, video etc)
In the case of 'editable' doc types (Word, Excel etc), it would be more efficient to simply display a NON-Editable version. If they want to edit the doc, then click a button which ONLY THEN makes a backup etc and opens for editing.
Otherwise, I am backing up every time...even if they just want to read what the file says.
thinking about it, I might only make backups of the Word and Excel files, but even that could generate a lot of 'unnecessary' backups. (If they are just READING the file for info.)

 

[The_Doc_Man]

If you are careful in the way you stage the backups, making an extra backup is not a waste of time. Backups are NEVER a waste of time.

I know for Word that the .Document.Open has a Read-Only option, and I'm pretty sure that you have one in Excel. Those other document types aren't editable unless you have some fairly specific utilities so for them it might be a moot point. However, remember that if you truly open the document using the associated utility, they have a way to get to the File >> Save As option through the utility's menu bar or ribbon. You probably should at least think about whether that ability bothers you.