INTERACT FORUM

Oh, sweet, sweet, sweet. Yee haw! That is brilliant. I just didn't know where to look. God, I love this program.

Do you use any of the PPC programs or do you just use Excel?

tlongacre,
I only use Excel but it would be nice to have something preset to make it a single step update. I just use it when I am shopping to make sure I don't buy something I already have. I am on Pocket PC 2002 and there is some reference to Access db but I have not been able to use MS Acess on the Pocket PC

tlongacre,
I only use Excel but it would be nice to have something preset to make it a single step update. I just use it when I am shopping to make sure I don't buy something I already have. I am on Pocket PC 2002 and there is some reference to Access db but I have not been able to use MS Acess on the Pocket PC

Ah, you must have a collection like mine. There is nothing more embarrassing, particularly in front of your spouse, than realizing you've bought something you already own.

I've been working on a way to generate customized HTML (among other things) from the MC library.  Here's a screenshot of what you can do using scott_r's MCXMLExport plugin and my XSLT:  http://pages.sbcglobal.net/scottraymond/XAlbumTomcat.jpg.

The plugin is available here:  http://yabb.jriver.com/interact/index.php?board=5;action=display;threadid=16966
The XSLT is available here:  http://yabb.jriver.com/interact/index.php?board=5;action=display;threadid=16293

As far as being confused about the acronyms, I'm going to write a primer on XML (I've been saying this for a week now, so I hope I finish it soon).  It will explain everything you need to use MCXMLExport.

Scott-

Well, I gotta admit, that looks cool. Particularly if it is searchable. If it looked like that page (the page with the James Taylor album) on my PPC, that would be excellent.

How big is your library?  There are practical limits to how big of an HTML document you want to open with a webrowser - especially one on the PocketPC.

Well, as of this moment, I've got 12,217 songs, and I'm about 2/3 the way through my collection. I've always thought I had somewhere between 1,500 and 2,000 CDs. So now that I think about it, perhaps having every song listed is not all that practical and I should just do Albums. . .

Okay Scott, now I actually have the PocketPC PDA and I'm back on this.

I got the XML Export plugin for MC9 and that seems to work fine. But now I don't know what to do with the document it creates. What I'd like is to convert it to a database. There are a couple of Database programs for the PPC that can use an Access file. I thnk I'm going to use DB Anywhere.

So how do I get an XML file into an Access mdb file?

Then, any ideas about how to keep the database up to date without overwriting the primary key (which I need to keep intact to keep it synched on the PDA)?

Thanks.

BTW, I've got about 17,000 and think it may top out at 18-20,000.

I've rewritten the XSLT for importing the MPL into Access - it now replaces all illegal element characters with a character reference.  This is how Access deals with the problem, so the end-result is that the Access DB field names are identical to MC's field names.  The only downside is that it can take some time to generate - it uses recursion to parse through each filename, looking for illegal characters.  It takes about two minutes to process my 3,000 item library  using MCXMLExport.  I'm pretty sure the performance is linear, so I guess it would take you about 15 minutes to process your 20,000 item library.

(I do have a very straightforward XSLT that just translates “_” for a space, “N” for “#”, and removes “(“ and “)”.  It runs extremely fast, but it doesn’t result in Access field names that are identical to MC)

You can download the XSLT and a brief explanation at http://pages.sbcglobal.net/scottraymond/MPL2Access.zip.  The config file won't work with MC XML Export (yet), but it should give you an idea of what settings to use with MCXMLExport.

Concerning updates and primary keys...  the [Filename] field should be suitable for a primary key, if I remember correctly.  That is, it must be unique within MC.  I'm not sure what you're after with "updates".  Can you explain in more detail?

Scott-

Thanks. I don't mind about the time, but it helps to know so I don't abort it thinking it isn't working.

By updates I mean -- I am always adding new music to MC that I would then want reflected in the database. I am imagining that if I just do a complete export from MC and then import into the database, when it overwrites records (will it overwrite records??) the primary key would be lost. But maybe I'm worrying unnecessarily?

I'm only planning to take the Name, Album, Track#, Artist, Genre, and Date(yr) fields.

Just those six fields won't take long as tall.

Let me play with Access to see what it does with importing records that already exist.

Scott-

The primary key is needed for synching the Access database on the PC with the PDA. DBAnywhere specifically mentions this, but it makes sense -- it allows for making changes on either side and keeping things up to date. I see what you mean about using a query--I could come up with something that sees adds/changes and puts them into the main table.

I also need a primary key because I want to add a few new fields, maybe as a separate table -- to show if I've lent a CD out, to whom, and when it's coming back. As I am ripping my entire collection, I've now realized I've lost at least 5 CDs I know. Bummer.

I did the export with the plugin and tried just importing that into Access, but I got a table with one field with numbers in it (maybe the track# field?), that was it. So it seemed like I was missing something.

"applying the XSLT"??

oh, not doing that. How do I?

Brilliant! Yes, this is what I was looking for.

Now if there were just someone who was a whiz at Access, or had time (which I do not) who could whip up the appropriate query. . .

Today, I have 17,260 songs in MC and 17,260 songs on my PDA. Life is good. Thank God for CF Cards.

Thank you for your assistance. It all seems so simple now.

Now that this is working technically, I can see that I definitely want to put the tracks in a table separate from the albums. So it is going to take some thought about how to do this. So if anyone out there is fast with databases and has a little time, maybe I'm not the only one who is interested in this and we could create something that would automate the process and work really well.

Thanks again for all your help.

Well, since you asked, I'll tell you what I want in a fair amount of detail.

Context: I am currently ripping my entire music collection to MC. I'm pretty close to done actually and that may happen by the end of the weekend. I am trying to make sure everything is complete, accurate, coded, etc. as I go along (I've been getting all the cover art and lyrics I can find as well as making sure Genres, artists, spelling, and filenames are correct as well). However, I am always getting new music, so I will always be ripping or adding music to MC. Don't know if I'll be making many changes to current files or not. At the moment I am often making changes as I clean things up. The only other big project part of this is that I realize that I have got to get serious about playlists (or smartlists) because, with a 40gig iPod and over 100gigs of music, I have to pick and choose in a way that makes sense and has me able to carry around what I want. Besides, the program is now slowing way down on me (on startup -- takes a good 10 minutes or so) which I think is a function of some smart/playlists being really large.

What I need: Some way to know what music I already have, what I have lent out, to whom, when I lent it and when I expect it to be returned. This has to be mobile so I can, for instance, look it up when I'm standing in a music store or at a friend's house.

What I envision: an Access database that can be synchronized to the PDA with DBAnywhere. The Access database would allow for Album view and Track view. Track view would show the name of the song, artist, album, genre and maybe duration. Album view would show the album, artist, year, genre and might have a sub-form that displayed the tracks on the album. There would also be a view that would show (or perhaps this would be in album view as well) when I lent it, to whom and when they said they'd give it back.

Reports needed:  list of albums currently OUT showing album/artist, to whom, date lent, date expected back, sort by date expected back, them possibly also: alpha list of albums, alpha list of albums by genre, alpha list of artists & albums, maybe alpha list of tracks (but this may just be easier to do with a find). But reports aren't so important as having Forms (in Access and in DBAnywhere) so I can just look something up quickly and easily.

I don't think there's any need to hold onto the Lent info -- so that once something was returned, those fields could just be cleared, rather than building a history of what was lent to whom at what time. I'm not a library, just a bit too forgetful to match the level of my generosity.

This may be a whole other level of complexity, but it would be neat to be able to build playlist ideas on the PDA. I can't envision how to do that--maybe have a playlist table and a playlist/track join table--but I find the iPod functionality a little too clunky and obtuse for me and it would be cool to be able to sit on the train and create playlists, even if I had to actually input them separately once I got back to the desktop--I'd be doing the thinking on the train which is the thing that is hard to get to when I'm home.

Lemme know if you wanna know more!

- Tracy

Well, since you asked, I'll tell you what I want in a fair amount of detail.

Context: I am currently ripping my entire music collection to MC. I'm pretty close to done
.
.
.
the train which is the thing that is hard to get to when I'm home.

Lemme know if you wanna know more!

- Tracy

Well, it sounds like you want a fully-functional relational database on your PocketPC.  Are you familiar enough with Access to generate your own queries, forms, and reports if I create an XSLT that will populate the tables?

Do you need to update the database on the PocketPC?  If not, this gets a lot easier because "synching" is essentially a "delete existing records in the remote database and then do a complete import from the MC database".

I think you've piqued my curiosity - I'm going to play with XSLT this weekend and see if I can't create an XML that imports into Access while maintaining referential integrity.

Scott-

Scott,

Well, the synch between Access and the PDA is handled by DBAnywhere -- that is what it does as a program.

I have the capability to do the queries and forms in Access, but not the time. I may have time next summer, but definitely not before then (own a business, going to school, active volunteer. . .)

Hmm...  I spent a few hours playing with MS Access (which is pretty paltry when it comes to XML support) and a product called XML-DBMS.  Unfortunately, I can't get XML-DBMS running (it's a java application that depends on some other java apps and I *hate* CLASSPATHs).

I'm thinking maybe the best way to proceed is to create SQL statements to be run from within a database.   I'll keep thinking.  If anyone has an idea, I'm all ears.

Scott-

Yes, I was reading the other thread about the Export plugin and realized that this must be what is slowing MC down. I uninstalled it 'cause I didn't know about the auto start feature. I just did an export and figured I'd just reinstall the plug-in the next time I needed it.

Can't tell you how happy I was to figure out what the problem was!

BTW, on that thread, you said you didn't have a very big library and I know mindracing sent you his. I sent mine as well since it is so large. But it was a very large file, so I don't know if you got it, or if it was useful.