Word - Access 'content management solution'

Thanks for the initial ideas on my question here
https://www.experts-exchange.com/questions/29074132/Uploading-sections-in-a-Word-document-into-an-Access-database.html?anchor=a42407148&notificationFollowed=201631007#a42407148 (still open BTW)

I can now take my quest a step further.

Say I have a 'block of content' on Word document. It can be
  • a paragrah
  • a bulleted list
  • a table

I want to be able to 'copy' that block of content to a field in an Access table, and also to be able to copy it back to a Word document, preserving the formatting as was.

I have experimented simply (manually) copying and pasting across, with some success. So I suppose it is possible - Access seems to store the relevant data, though I'm still not clear how the Styles come into play.

My question is, eventually I need to do this programmatically - (preferably with ADO/SQL as that's what I know).

Is there a super-elegant way for creating what (effectively) is a 'content management solution' with Word on the front-end and Access on the back?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried it? Just curious, as Crystal seems to have given you quite an extensive solution to your other question.

Only real trouble I could see was if there is a "Header" or something of that nature at the beginning of the document. You'd have to be sure that "Header" is in place (as well as a Footer, if needed). Documents that are formatted at RTF (or other formatting methods) typically have that sort of nested/enclosed structure, and figuring out how to properly create that "nesting" could take some time.
hindersalivaAuthor Commented:
Hi Scott. Crystal's solution made it possible for me to analyse a 411 page document and get a better understanding of what I'm dealing with (paras, tables, bookmarks).

The sheer volume of paragraph elements (8,000 +)  makes assembling the document from text + styles retrieved from a database impractical (I think, as you elude to in the last sentence).

So I'm thinking (hence the question) whether it is possible to take this to a higher level of granularity - ie. some ways of grabbing an entire Clause (with it's formatting, bulleted lists, any tables within it intact) and holding it in a field in an Access table.

I have attached a sample of page of a sample 411 page doc.
In this, the whole of the block that begins with 'M020400      DEFINITIONS' The next block begins with 'M020700      GENERAL'.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:

> "preserving the formatting as was"

The ONLY way to ensure that formatting is exactly the same is to save the blocks as Word documents. You could try writing to a Rich Text field, but there will be slight differences.

If I understand correctly, to find the beginning of each of your "blocks":
   1. section*** number starts a new block
   2. font.bold will be true for the section number
   3.  section number is followed by 1 or more TAB characters, which are chr(9)
   4. left indent is  0

so you would loop through each paragraph, as the code does.
then check to see if the above rules are true and you are at the beginning of a block
if so, mark the character position of the beginning, nPosStart* in my code
keep looking
when the next block starts, mark its position
nPosEnd (previous block) = nPosStart-1 (of next block)
save what is between nPosStart and nPosEnd as a Word document -- probably name it according to its section*** number and document type or category.** (ie: M020700_abcd.docx)
record the document name for the section in Access, along with its relative position (first, second, third, etc)

* Pos is short for 'Position', which is the word I use to mean a character position in a string. 'n' is what I use for a long integer Number. Normally, I use '1' for 'Start' and '2' for 'End', but I used words instead of numbers for you (smile)

** or make a folder for each document type and name files according to section number, or ... ~ basically, devise some logical plan. I like to include enough to make a document name unique in the system.  Maybe a name like "M020700_abcd.docx" where 'abcd' is an abbreviation that correlates to the document type and makes that name unique in all the file names for all document type/category.

***Maybe what I am calling 'section' might be called something else -- subsection, clause, etc ... so hopefully you see the meaning ~

I might go one step further and save M02_abcd.docx as a master document with subdocuments (linked to the actual files for those blocks) too.

You can use Access to open files in Word for changing (keep track of who changed what and when -- maybe backup previous versions too) -- and develop a way to rearrange the parts. Maybe renumber them too, make new documents with sections from multiple places, customize text inside documents, ...

Moving forward, ideally the Word documents will be marked behind the scenes a little better to take advantage of Word features like Styles and Bookmarks. I would also mark them with Index entries -- these could be done programmatically if you have a table with words and phrases to mark.

have an awesome day,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hindersalivaAuthor Commented:
I just put a note on the other earlier question. A Content Management Solution with Word and Access would be too clunky (too much content) and unnecessary in this instance as the content will not need to be changed, just sections/clauses included or excluded. This can be done by hiding blocks of text and excluding from the TOC. I would, however, use an Access table to hold the show/hide status of any of the document. More about that when I get further on this.

Thanks all.

Crystal, your answers are superb. Tracking changes in an Access table. I would also put a snippet of the text in a field to have a preview feature (just plain text, unlike what I was trying to do yesterday. ie. formatting.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi (what is your name?)

thank you very much! ~ happy to help. Glad you can draw the analogies.

> "I just put a note on the other earlier question"

I looked .. but am adding code to this thread instead of that one ...

> " would also put a snippet of the text in a field to have a preview feature (just plain text, unlike what I was trying to do yesterday. ie. formatting."

there is really no need ... if you store the blocks as Word documents below the back-end of the database and all users have privileges to directories below as well, then you can open the block of text on demand.

Storing this in Access bloats the database and is probably not necessary.

Ideally, changes to Word documents would also be driven by Access, so what is stored in the shared place is also current ...  or, as changes are made to the master document(s), the program to separate into files is also done

 to clarify, for other readers, "M020700" used in my filename example, appeared to be the 'name' of a block of text -- this was observed by opening the sample document posted


in the code, you will see an example of setting oRng ... but it is not actually used in what was posted.  Once you can defne a range for a 'block', you can copy what is in the range (ie: oRng.Copy) then use this code:
Function SaveClipboardToFile(psPathFile As String) As String
'171218 strive4peace
'save whatever is on the clipboard to the specified filename

   SaveClipboardToFile = "" 'not done yet

   Dim oDoc As Word.Document
   'add a new document
   Set oDoc = Application.Documents.Add
   With oDoc
      'paste what is on the clipboard
      'save new document with specified name
       .SaveAs FileName:=psPathFile
      'return path\file name, with extension (which may not have been specified)
      SaveClipboardToFile = .Path & "\" & .Name
      'close the document 
      '   without saving changes since it was just saved
      .Close False
    End With
   'release the object variable
   Set oDoc = Nothing
End Function

Open in new window

 here is a sub to test it:
Sub testSaveClipboardToFile()
   Dim sPath As String _
      , sPathFile As String _
      , sPathFileActual As String
   sPath = ActiveDocument.Path
   sPathFile = sPath & "\MyFilename"
   sPathFileActual = SaveClipboardToFile(sPathFile)
   Application.Documents.Open sPathFileActual
End Sub

Open in new window

copying from the clipboard will be sure to get the formatting properly, as well as other things Word can store

Note: I did not modify this to run from Access but it could be done, of course

have an awesome day,
hindersalivaAuthor Commented:
Crystal, you are a mind reader! I'm building a small demo of all of these so I can explain to the client the various options to help decide architecture.
hindersalivaAuthor Commented:
I'm tempted to programatically create Bookmarks for every text block (using the M ref). There will be around 1,000 of them then. But that will make locating a block programatically easier (to Hide, for example).

Before I try that, I'm wondering if 1,000 Bookmarks is a lot, for Word. Will it make the Word file (now 500kb) many gigabytes big????

(should I start a new E-E question? I'm thinking I should but not sure what the etiquette is.)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:

not a mind reader -- just have a good imagination ;) ~

that many bookmarks is not a good idea!

better would be to define a style and when you want to find the beginning of a block, look for the block number text in that style.  Style can apply to a whole paragraph, or just specific characters. In this example, I am looking for a term that has a style of Heading 2, which is a paragraph style.

Microsoft Word - find text that is a certain Style
In your case, I'd suggest creating a character style and tagging all the block numbers with that style.

> "(should I start a new E-E question?"

yes -- that is better for organizing information

have an awesome day,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.