Go Premium for a chance to win a PS4. Enter to Win


Strategy for using Access 2013 with or without Word 2013 for managing 250 large groups of text - (clarification of previous responses)

Posted on 2014-01-24
Medium Priority
Last Modified: 2014-01-26
This Question is to clarify my understanding of answers to my previous Question

For details please refer to the five answers now being awarded in my previous Question at http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28345616.html

They are copied below, for your convenience. And, my seven questions asking for clarification of these answers are at the end.
A.  by: Gustav Brock
If you only need to store once the text parts, then read them later multiple times, you can safely store these in a table with a memo field and an ID.
The issues you have read about relate to multi-user access and update of tables with both normal fields and memo fields.

B.  by: Scott McDaniel
+1 for gustav's suggestion. I've done something very similar to this for a QA app, and I haven't had any issues with it. The text blocks in mine are stored in a separate database, and I've linked them back to my main app. I've setup the tables so they have a Memo field and a Keywords field, and the users can search on a keyword field. The program does regularly insert new records, and they occasionally edit existing records. I've had no issues with this, and it's been in use for 10+ years.

C. by: Gustav Brock
1. [Why use a separate, linked, database?] Either for easy backup, to split into several files for large databases, or the be ahead of the possible issues with corruption with multi-user update.

2. [Are the Memo and Keywords field each duplicated in both the main and linked database; or are they both available to the main db, but linked and actually stored in the in the linked db?] Yes. See 1. Should the memo file corrupt (though not very likely with the split setup), them main database file is intact and the memo database can easily be restored.

D.  by: Scott McDaniel
Why use a separate, linked, database?
I make the assumption that you would have some form of interface to manage those Memo fields. If so, then you should store the data (i.e. Tables) separately from everything else (Forms, Reports, Queries, etc). If you do not have an interface - that is, you open the tables directly, and insert/edit the Memo data in the table "datasheet" view - then you don't need linked tables. However, I'd strongly encourage you NOT to do that. Create a simple form that allows users to maintain those Memo tables, and split the two. Your Word document can attach directly to the "data" database, as can the "Interface" database (commonly referred to as the "FrontEnd").

E.  by: Gustav Brock
Indeed. I missed that. What I mentioned about splitting the memos was from the other data. The separate FE was assumed.

Thus, you would have a setup like this:

BE - normal data
BE - memo table(s)
BE - picture table(s)

I have only used the combo with a separate picture BE database file as many and/or large pictures will clutter a normal database file.

Please read the following for background and updated information.

I need the following clarifications to the responses to the (now being) awarded Answers in the previous Q.

The application will just have one user (me) on the same local Win 8.1 laptop, using Access 2013 and Word 2013.  All data will be stored on the C drive, with automatic rotating backups to a USB drive and Cloud.

[UPDATE: I've selected Access (instead of a collection of Word macros) because, based on extensive experience with earlier versions, it fits my vision of the best, fastest and easiest way to manage and track the creation and development of the 200 or so Groups of text, in my application.  It's high-level and extremely flexible for quickly building standard queries and the many unexpected ones that will frequently arise.]

[UPDATE: Access 2013 will be used to manage and track the information as Groups of text corresponding to roughly a couple hundred Memo and/or Word 2013 records.  Whether the Groups are stored in Access or in Word, each of these Groups will have a corresponding Access record in a table with roughly 15 normal fields describing characteristics of the respective Group.  There will be roughly four or five other tables in Access that will be used to manage and track characteristics of entire collection of groups.  I don't envision these several other tables each having more than 100 or so records or more than roughly eight fields.]

The estimated amount of text and/or Memo data to be stored in Word and Access will be in the range of 300 KB 1200 KB.

[UPDATE: The plan is use Access to manage and track the first draft of Groups of text that will be consolidated into a large Word doc (300 KB to 1200 KB).  When the first rounds of drafting are completed, I will abandon use of Access and will be refining the doc. exclusively through Word (unlinked to Access). That's why the title of this new Question specifies using Access with or without Word]


Let me try to conclude what I understand from the awarded postings in the previous Q.  Please tell me if I've understood them correctly.

1.  A good approach would be to have the Access db linked to a word document (I'm thinking each group could be in a separate Word doc. section, rather than in a couple hundred separate Word docs.)?

2.  Access Memo fields would, and could easily, be linked to their corresponding sections in the Word doc?

3.  The Access Memo fields would be in sync with their Word doc. sections such that an update from either would result in the corresponding update in the other?  In other words, each would be in duplicate of the other after sync.

4.  (a) both Access and Word must be simultaneously running for sync to occur, or (b) if either the db or the Word doc were not running then the one not running will automatically sync with the other when it runs next?

5.  It would or would not be a good approach to have the Access memo field be on a separate BE db that links between the word doc. and main (FE) Access db?

6.  Should my several other tables be stored in the BE with the Memo field table or in the FE with the forms, queries and reports?

7.  If the ideas above are problematic or are flawed then please suggest and alternative based on my expressed interest.
Question by:WaterStreet
  • 2
  • 2
LVL 46

Expert Comment

ID: 39807784
unless you use RTF, you won't have any way to store character or paragraph formatting in your database table.
LVL 18

Author Comment

ID: 39807841
Thanks Mark.

I won't be using character or paragraph formatting for the first drafts that will be prepared in conjunction with the Access db.  As I mentioned, I want to break away from Access after the early stages of drafting are completed, and then move over to Word exclusively for more layers of drafting and the finer touches, such as paragraph formatting.  If I should decide otherwise, I might open a separate Question about using RTF.  

But right now, I don't want to get this thread or me sidetracked by focusing or responding to anything other than the seven needed clarifications that I raised here.
LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 39808803
If at all possible, I would stick to one-way syncing - meaning selection and/or adjusting the text parts in Access, then export the finished document to Word.

LVL 18

Author Closing Comment

ID: 39810489
That seems to answer a lot of my concerns.  Your response got me to rethink my overall strategy.  

Along with answers to my previous question, I believe I have a good approach for adjusting text parts in Access, and then selectively export them (one or more at a time) after which I will then be working with them in text or Word docs.

I won't be doing any syncing at all, just exporting selected memo fields as text, as I complete my rough drafts of them in Access.  I'll still use Access for all my original management and tracking, and to also track which memo field texts are still to be edited in Access or externally.

If I have any more questions about what I plan, I'll ask them.

LVL 52

Expert Comment

by:Gustav Brock
ID: 39810592
You are welcome!


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question