Strategy for using Access 2013 with or without Word 2013 for managing 250 large groups of text - (clarification of previous responses)
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 https://www.experts-exchange.com/questions/28345616/Strategy-for-using-Access-2013-and-or-with-Word-2013-for-managing-250-large-groups-of-text.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:
FE
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]
So...
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.
For details please refer to the five answers now being awarded in my previous Question at https://www.experts-exchange.com/questions/28345616/Strategy-for-using-Access-2013-and-or-with-Word-2013-for-managing-250-large-groups-of-text.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:
FE
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]
So...
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.
aikimark
unless you use RTF, you won't have any way to store character or paragraph formatting in your database table.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks
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.
Thanks
You are welcome!
/gustav
/gustav