Solved

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

Posted on 2014-01-22
13
699 Views
Last Modified: 2014-01-24
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
Comment
Question by:WaterStreet
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 39802514
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39802593
+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
 
LVL 18

Author Comment

by:WaterStreet
ID: 39802623
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 39802649
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39802868
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 39802900
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 45

Expert Comment

by:aikimark
ID: 39803238
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
 
LVL 18

Author Comment

by:WaterStreet
ID: 39805296
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39805445
@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
 
LVL 18

Author Comment

by:WaterStreet
ID: 39805561
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39806607
@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
 
LVL 45

Expert Comment

by:aikimark
ID: 39806632
@WS

With Word 2007/2010, you can use the BuildingBlockEntries collection of the template.
0
 
LVL 18

Author Closing Comment

by:WaterStreet
ID: 39807767
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now