[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Strategy for using Access 2013 and/or with Word 2013 for managing 250 large groups of text

1.  I want to use MS Access 2013 to help manage and organize groups of text that will become parts of an approximate 100k word Word 2013 document, maybe as high as from 750 Kb to 1.5 Mb in size.

The size of these text groups will probably range from 200 words to 2500 words.  At an average size of 400 words (at 8 chars. ea.), the estimated number of groups would be 250 at roughly 3kb each.

It might be simpler store the text groups in long text fields within Access, but I've heard various reports of text corruption and truncation in those long text fields.  I need a design approach that minimizes these problems.

So, I'm considering an alternative where the text groups are stored external to Access, such as by my groups stored in (up to maybe 250) Word 2013 docs (linked or not).

MS Access would be used during the first half of the project to help manage and keep track of information about those text groups.  I plan to have several tables in Access.  Each field in the main table will contain the unique code or filename of an external Word doc (one for each text group) along with descriptive information about the text group.

The second half of the project will start when the text groups have been both sufficiently described in Access and sufficiently drafted (externally).  At that point I will be working almost entirely with the word docs, refining them and assembling them into their final sequence; then I will integrate them into a single doc.  During this 2nd part of the project, the previously entered Access data would be used to help me find possible integrity issues with the finalization of the individual and assembled Word docs.

2.  An additional requirement, which I have not resolved, is the ability to sequentially search through each of the (up to 250) groups, in order to identify which ones contain a (then to be) specified word. The groups would all be in their own exclusive Win Explorer folder.  The first three characters of each document name in the group could contain the unique group number known to the Access database.  My alternatives for this seem to be (a) using Access to manage the search and report results (using linking or not), or (b) using a Word 2013 macro to manage the search and report results.  Search speed, as well as development and implementation ease are important.  For these reasons my non-expert guess is that the Access alternative would be the best for me.

I'm not looking for exactly how to do this, but for the overall design approach for which I can ask specific questions from EE Experts when I need detailed help.

Any thoughts, problems and suggestions with my proposed alternatives?

WS
0
WaterStreet
Asked:
WaterStreet
  • 4
  • 4
  • 3
  • +1
5 Solutions
 
Gustav BrockCIOCommented:
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.

/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
+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.
0
 
WaterStreetAuthor Commented:
All,

1. "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. " 
Why use a separate, linked, database?

2. Never used a linked db. Let's see if I understand this.
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?

3.  Gustav said, "The issues you have read about relate to multi-user access and update of tables with both normal fields and memo fields."  So, if I have a (normal) Keywords field and the Memo field in the same (linked?) db then don't I expose myself to the issues I read about?  

Or maybe, the idea is to have the keywords field also be a Memo field?

Thanks in advance.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Gustav BrockCIOCommented:
1. 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.

3. 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.

/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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").
0
 
Gustav BrockCIOCommented:
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.

/gustav
0
 
aikimarkCommented:
What about the formatting of the text?  There is a feature in Word that allows you to store (formatted) text and insert it as needed.
0
 
WaterStreetAuthor Commented:
All,

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.

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

[Edited 5:02PM PST -  I just plan on using Access to manage and track the first draft of a large Word doc.  When the first rounds of drafting are completed, I will probably abandon use of Access and be refining the doc. exclusively through Word (unlinked to Access)].

Let me try to conclude what I understand from the above comments.  Please tell me if I've understood 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 doc.)?

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.  If the ideas above are problematic or are flawed then please suggest and alternative based on my expressed interest.


I hope to award this thread, after I get responses to the above six questions based on the understanding (or misunderstanding) I expressed above of what you experts have already told me.  I can then open separate EE questions for my further clarification, if needed.

Thanks in advance.

WS
0
 
aikimarkCommented:
@WS

I'm not advocating that solution.  I don't see a need for you to use a database to store text when Word has that capability.
0
 
WaterStreetAuthor Commented:
Hi Mark.

This is not simply about storing text. I thought I made that clear in my earlier comments.

I wasn't aware that there's a robust database capability within MS Word.  

I've written thousands of lines of macro code in both Access 2003 and Word 2003. I want to stick with that familiarity. I don't have time to learn a new language such as SQL, if that's what you have in mind.

All,

At this point, I'd like to get answers to the six questions, so as to clarify the postings that have been made earlier, regarding Access; and to close this Question.

WS
0
 
aikimarkCommented:
@WS

You can use your existing Word VBA skills.  Here is an example of Word VBA code that will search the autotext entries for those that contain the word "Now".
Sub Q_28345616()
    Dim vItem As AutoTextEntry
    For Each vItem In NormalTemplate.AutoTextEntries
        If vItem.Value Like "*Now*" Then
            Debug.Print vItem.Name, vItem.Index, vItem.Value
        End If
    Next
End Sub

Open in new window


Sub Q_28345616_InsertByText(parmSearchText)
    Dim vItem As AutoTextEntry
    For Each vItem In NormalTemplate.AutoTextEntries
        If Instr(1, vItem.Value, parmSearchText, vbTextCompare) <> 0 Then
            Selection.TypeText  vItem.Value
            Exit For
        End If
    Next
End Sub

Open in new window

Here is an example of inserting the entry, referenced by its name.  This could also be done with the autotext dialog.
Sub Q_28345616_InsertByName(parmName)
        Selection.TypeText  NormalTemplate.AutoTextEntries(parmName)
End Sub

Open in new window

0
 
aikimarkCommented:
@WS

With Word 2007/2010, you can use the BuildingBlockEntries collection of the template.
0
 
WaterStreetAuthor Commented:
I've opened another Question for the clarification some understandings I asked about here, after my primary questions were answered.  These other questions have been updated and are centered around the use of Access 2013 with or without using Word 2013.

See http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28347770.html

Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now