03.06.08

Database Tools for Non-Techies

Posted in Computer Tools and Tricks at 9:24 pm by Ivey

For the past few months, I’ve been focused on my data management issues.  With so many novels to keep track of, I needed to get some of this out of my head and into a database.  I figured I would post a quick rundown on my experiences for what benefit they might be to others in similar situations.

I initially went with Microsoft Access, which I love for its flexibility and ease of use.  Made some great tables and forms on there and was happy–especially because of the features in Access 2007 that let you store multiple values in a field and attach documents–but then I wanted to work on my database and its linked documents on my second and third computers.  That’s when things got tricky.  For various reasons, all my computers aren’t on the same network, so it wasn’t as easy as calling up the files on a different computer.

I put it all on my portable drive, which got the job done, but the constant hassle of lugging my portable drive around started to get to me.  PLUS my laptop kept wanting to give the portable device a different designator than my other computers used for it, which caused some problems with the links.

I already had been using Xdrive and Google Documents for some other purposes–both of them have nice functionality–but for this Access problem they still required some movement of files back and forth among computers because they don’t have features to support Access connectivity.

I have an account at Office Live, which I love for my Microsoft Word, Excel and PowerPoint documents (if you haven’t tried it yet, I recommend it; there’s a really nice plug-in that allows for pretty much seamless interoperability between your computer and your online files… it’s at http://www.officelive.com). It didn’t work out.  My Access database was too big to send to Office Live (it limits document size).  Just as well, since Office Live isn’t set up to work with Access, so it might’ve been a glitchy solution anyway.  I’m still hoping–as are some other Office Live users, judging from the forums–Microsoft will expand Office Live to support Access use.

Soooooooooooooooo… it looks like I’ll be working to expand my SQL and PHP knowledge in order to achieve my final solution. I don’t want to go there.  I want to write novels, not develop applications.  I’m looking into Oracle Apex as well, for what it might allow me to do.  If you’re a little more technically inclined than I am, you might be able to get up and running pretty quickly.  It’s quite powerful.  Check it out at:  http://www.oracle.com/technology/products/database/application_express/index.html.

I still haven’t completely ruled out going with one of the ‘easy’ database tools.  I’m reading up on/testing a few to determine what all they have to offer.  So far, I like:

Trackvia at http://www.trackvia.com - It has some great features, but their built-in stuff seems to support business customers and their needs (customers, employees, order tracking, etc.), when what I need is something that will let me handle books, characters, and the related multimedia content.  Still, it looks promising for a lot of things.

DabbleDB at http://www.dabbledb.com — This one’s fun to play with and I really like the ease of use and flexibility.  It has a really nice copy-from-spreadsheet capability.  It lets you do all kinds of stuff with your tables and the data in them. 

I won’t recommend one over the other.  Which you prefer will really depend on what, exactly, you want to do with it.  The best way to determine which fits is to visit the sites and check out the intro material and online demos.  They both cost about the same (in the $10/month range) but Dabble offers the service for free to those who open up their data publicly.  Sorry, you probably won’t want to do that if you need a place to store info. about your unpublished novels–although if your characters are interesting enough to lure visitors, you might be able to generate some interest in your work. MUHAHAHAHAHA!

If I could roll TrackVia and DabbleDB together, and get rid of all the parts that are only in the way for me, I’d be set.

Here’s another that I’m really excited about, BUT it’s in Beta so it’s a bit clunky and buggy.  It’s not advertising itself as ready to handle operational needs yet but it’s working to get there.  For now, don’t try to make it ‘the place’ to store your data–but keep it in mind for the future.  In the interim, it is great fun to play with and I think it’s going to be an awesome capability if they keep going along the line they’ve plotted.  It’s like spreadsheets on steroids–and with a great user interface.  Super-easy to use and you can get some pretty nice reports and views from it.  Right now it’s free, but I imagine that’ll change once they get the kinks worked out. 

Blist — http://www.blist.com

Anyway, I figured I’d post this for the benefit of others who might be in the market for a solution, either now or in the near future. It’s worth at least taking a look at these before you commit yourself to a life of PHP and SQL or invest in a high-end solution that ends up costing more than it’s worth to you.

Happy writing–and databasing!

Permalink Comments

09.28.07

Hey, Rocky! Watch me link from Microsoft Excel or Access to a Specific Place in a Word Document!

Posted in Computer Tools and Tricks at 9:40 pm by Ivey

After numerous attempts, in various Microsoft Office versions, to create links from Excel or Access cells to a bookmark in a Microsoft Word file, I was about ready to give up.

See, both Excel and Access show an option to go to a bookmark. But when you try to use the bookmark, it says it can’t parse the Word file. Evidently Microsoft made the decision to keep these tools frustratingly unaware of each other’s innards. To Excel and Access, ‘Bookmark’ means one of their own or one imbedded in html and perhaps other non-Word applications. Anyway, I’d searched the web and even ventured into VBE-land for awhile but knew there had to be an easier way.

Then I remembered (doh!) from the old days of manual html that a Word Bookmark is basically the same principle as an HTML Target. And so I tried framing the reference as a target and it worked great — in various available versions of EXCEL and ACCESS.

Soooooooo… for those who want to keep tables but also have the ability to rapidly go to a specific place in a Word document, here goes:

–

1) If you don’t already have a bookmark in a Microsoft Word document, make one. This entails clicking on the place you want to link to in the document. Then go to your menu and choose Insert and then Bookmark. Give the bookmark a name and save it by clicking ‘Add’. Save the file.

2) What you do next depends on whether you want to link from Excel or Access:

• Excel: If you are linking from an Excel file, just right-click over the cell that you want to use to link to the Word document and choose ‘Hyperlink’.
• Access: If you are linking from an Access file, you’ll want to create a place for the hyperlinks. Use the menu to choose Insert and then Hyperlink Column. Then just right-click over the cell that you want to use to link to the Word document and choose ‘Hyperlink’ and then ‘Edit Hyperlink’.

3) Use the screen that pops up to navigate to your document and click on it. Once its name appears in the box, just add the following at the end of the file name:

#whateveryounamedyourbookmark

4) Hit OK.

That’s all there is to it!

If you don’t remember what you named your bookmark, go back into Word. From the menu, choose Insert and then Bookmark. This will let you view your list of bookmarks. 

–

One thing to keep in mind is that, should you move the original Word file, Excel and Access won’t know how to find it anymore.

It would be great if you could store that location as a relative link, but evidently Access doesn’t allow relative linking. For more information on absolute links (which do work in Access) and relative links (which don’t), check out:

http://www.compugoddess.com/relvsabs.htm

Hope this information saves someone else some frustration along the way.

–

On a side note, I am so excited as I await my copy of Microsoft Office 2007. I will finally be able to drag my ‘My Writing’ database into this century. Looking forward to experimenting with all the possibilities that the XML-based Word format provides. Plus, the ability to put multiple values in an ACCESS cell will be a tremendous time-saver and should allow for cleaner representations in forms and reports. Now, I just hope it doesn’t disappoint.

Vista — which I didn’t go out seeking, but came installed on my laptop when I bought it — was a major disappointment. Thanks bunches, but I don’t like it when my computer tries to second-guess me. Grrrrrrrrrr… if I wanted to click that, I would’ve clicked it!

Permalink Comments