Import from Access

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

jaqkar
Posts: 9
Joined: Fri Dec 02, 2005 2:26 pm

Import from Access

Post by jaqkar »

Hi

I need some help please guys. I have my cd collection indexed in a Access database. Can anyone help me import this collection into MM access DB. I have been struggling for months to get something like this going and I have lost money on an application that couldnt do this so I am really desperate.

Regards
J
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

Hello Jaqkar

To get some useful responses to your question about importing an Access database, you will have to be a little bit more clear. I.e. what tables are in the DB, what fields (columns) are in it, how many entries (cd's or songs) are we talking about, ... .
Basicly, it is necessary for anyone trying to help you to know the structure of your database.

Maybe, it would be useful to make a copy of your DB and leave only one complete row per table (small DB size). Then you could ZIP and upload it, and post a link to it.

Cheers
Steegy
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
jaqkar
Posts: 9
Joined: Fri Dec 02, 2005 2:26 pm

Post by jaqkar »

Hi

Sorry for not being more clear on the details. I have alink to a sample of my DB: http://kingscsi.phnuk.com/music.zip [12KB]

The real database has 15000 records and as you will see from the sample I only have 1 table.

Hope this is possible :)
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Hi jaqkar

I just looked at your db. I assume that you have scanned your collection into MM and now you want to update the MM db with some info only found in your db?

If that is correct:
1. Do all your collection have title, artist and album in the MM db?
2. Exactly what info do you want to "transfer" from your db to the MM db?


/Bex
jaqkar
Posts: 9
Joined: Fri Dec 02, 2005 2:26 pm

Post by jaqkar »

Hi Bex

I have not imported anything in MM, this collection is cd's only and I dont want to convert and store to mp3 etc. on the machine. I just want to keep the cd information and transfer that to MM. I would like to transfer all the information in my db to MM.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Ok I see.

Well it might be possible todo but it's very tricky since there are so much info that needs to be updated in MM db that your db doesnt contain.

It is indeed possible to just add the track information on a CD to MM without to rip the content. Check out "Adding CDs to the Library" section in the help file. It's very fast since you dont rip anything.

If you really want to "transfer" your db to MM db you have todo it in the way MM would have done if MM had used the "Adding CDs to the Library" function.
It means that you need to try with one CD and figure out how its stored in MM. And then convert the data in your db to MM db format and then when you append your data to MM you also need to set all other values that your db doesnt contain.

Actually I think the easiest way to get what you want is to manually add the CD's to MM with the "Adding CDs to the Library" function. If you have like 100 CD's it would only take 1 - 2 hours. If you then need to update some info in MM from your old db it should be easier.

Give it a try and let us know the progress.

/Bex
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

I think the importing can be done by a script, but the major drawback of this is that MM won't recognise the cd when you put it in. It will think it's a new one.
Normally, MM stores the serial number of a cd you add to the library. That way, it can recognise it later and show the tracks on it, that are already in the database.

The only way to add these serial numbers to the MM database is to add all your cd's manually to MM by "rescanning/adding" them. For your 15000 records, that would mean 750 cd's for 20 tracks/cd. That would be a huge work. If you have some patience, you can add 15 cd's per day (in your spare time) and that would take 50 days. Hmm...

If you don't bother MM recognising your cd's when you put them in, you can use a script.

PRE-POSTING EDIT: I partially withdraw my words.
It seems that MM does recognise cd's with serial number set to 0 in MM's database, but it only shows this recognition in the "My Computer" node (the right cd name is shown and the right track properties are there)
However, in the rest of the program (My Locations, Albums and Artists, ... nodes), the tracks are gray (offline).
Maybe this is some kind of shortcoming. (Recognised in My Computer should be recognised everywhere doesn't it ??)

Cheers
Steegy
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
jaqkar
Posts: 9
Joined: Fri Dec 02, 2005 2:26 pm

Post by jaqkar »

Thanks alot for the replies!

Im sure info from this thread will help others in the same situation as me. I have seen that alot of people cannot find modern software to accomodate older databases and it is very frustrating. As mentioned before I have even bought software and then gave up on it because it could not do this.

Going further, I have too many CD's to manually add them and the import option is really essential. I do not really mind if MM cannot recognize the cd if I put it in the PC as I will always use my sound system cd player to play the cd's.

I will use the suggestion from Bex to see how MM stores the data and try to match up my DB. Any other suggestions on how I can go further, maybe using a script or something?

Would also be great if anyone that knows can post some description of the fields used in the MM Access DB.
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

Hello

please try this script, it's a draft version but it works on the db you uploaded:

The script assumes that your DB (to be imported) is stored in "c:\music.mdb". If this isn't so, please change that in the 4th line.

EDIT: I used the Custom3 field for the ID field of your database, but this is probably not necessary so you can put a ' in front of the line MyTrack.Custom3 = QResult("ID")

Code: Select all

Sub Test

  Dim DBConn, QResult, MyTrack, CDSuffix
  Const MDBFile = "c:\music.mdb"
  CDSuffix = ""

  Set DBConn = CreateObject("ADODB.Connection")
  DBConn.Provider = "Microsoft.Jet.OLEDB.4.0"
  DBConn.Open "Data Source=" & MDBFile
  Set QResult = DBConn.Execute("SELECT * FROM Music")

  Do While Not QResult.EOF

    Set MyTrack = SDB.NewSongData

    MyTrack.Custom3 = QResult("ID")
    MyTrack.ArtistName = QResult("Artist")
    MyTrack.Title = QResult("Song Title")
    if QResult("Disc Number") <> "" then CDSuffix = " (CD " + QResult("Disc Number") + ")"
     MyTrack.AlbumName = QResult("Album Title") + CDSuffix
    if QResult("Year") <> "*" then MyTrack.Year = QResult("Year")
    MyTrack.TrackOrder = QResult("Track Number")
    TimeMinSec = QResult("Play Time")
     MyTrack.SongLength = (Left(TimeMinSec,2)*60+Right(TimeMinSec,2))*1000
    MyTrack.Genre = QResult("Genre")
    if QResult("Composer") <> "" then MyTrack.Author = QResult("Composer")
    if QResult("Comments") <> "" then MyTrack.Comment = QResult("Comments")
    'MyTrack.Path = 

    MyTrack.UpdateDB

    QResult.MoveNext
  Loop

End Sub
You have to paste the code in a plain text file (like notepad) and save it to C:\Program Files\MediaMonkey\Scripts\Test.vbs

At the end of the Scripts.ini file, please add:

Code: Select all

[Test]
FileName=Test.vbs
ProcName=Test
Order=7
DisplayName=&Test
Description=Test
Language=VBScript
ScriptType=0
Then restart and find a "Test" menu item under Menu "Tools" > "Scripts"

Cheers
Steegy
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
jaqkar
Posts: 9
Joined: Fri Dec 02, 2005 2:26 pm

Post by jaqkar »

Hi Steegy

This is great so far thanks alot. I sent you a pm.
quarter pro
Posts: 3
Joined: Thu Aug 13, 2009 7:49 pm
Location: Germany

Re: Import from Access

Post by quarter pro »

Iam currently try to get this script working but a few problems occured that i can't manage myself. I have a MS Access DB file and problem to import some columns correctly.

1. My Song Length field "Time" has this format "3:45" in min:sek. I set Acces mdb file typ to date/time.
How is the right code for importing this correctly?

2. My Bitrate field e.g. "320" is a date/time datatyp. When importing with this line:
MyTrack.Bitrate = QResult ("Bitrate")
but my imported Bitrate field value is "0" not "320". Don' know why.

3. I want to import my field "Volume/CD Label" so thats it imported as "MediaLabel" and with a "CD" Typ. The "MediaLabel" is a seperat table in the MM3 database thats what i know.

4. I want to import my "date added" to "DateAdded". In MM3 i couldnt find a place where to check this value. Maybe with an special script, didnt tried that yet. However, my field has this values: 29.09.2003 18:52:12 Its in "dd.mm.year h:min:sec" format.

These are my four problem where i have no idea how to get this to work. I took the script above. I tried this method to import a database from Wisetec Music Express app after my first try with triximotos "import from txt" script failed.
trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK
Contact:

Re: Import from Access

Post by trixmoto »

1) Based on the code above...

Code: Select all

arr = Split(TimeMinSec,":")
MyTrack.SongLength = (Int(arr(0))*60+Int(arr(1)))*1000
...where TimeMinSec="3:45" - this converts it into milliseconds.

2) I don't know why this isn't working, unless it's because you're passing in a string instead of an integer, maybe try...

Code: Select all

MyTrack.Bitrate = Int(QResult("Bitrate"))
3) Based on my "Album Art Tagger" script...

Code: Select all

Set pics = MyTrack.AlbumArt
Set img = pics.AddNew            
img.RelativePicturePath = rel
img.ItemType = 6    '<--- 6=Media/CD
img.ItemStorage = 0   '<--- 0=In tag,  1=as link
pics.UpdateDB
...where rel is the relative path from the MyTrack.Path to the image file (I have a function for calculating that based on the full paths, if you want it).

4) Based on my "iDateAdded" script...

Code: Select all

s = DateSerial(y,m,d)+TimeValue(t)
MyTrack.DateAdded = FormatDateTime(s,0)
...where y="2003", m="09", d="29", t="18:52:12".

Hope that helps! :)
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
quarter pro
Posts: 3
Joined: Thu Aug 13, 2009 7:49 pm
Location: Germany

Re: Import from Access

Post by quarter pro »

Thanks for your help. Got the half working except point 3) with the "Media Type" and "Media Label". I don't know what your script does and what you mean.

To be clear, I want to import a column called "Volume Name". This column contains the CD Label Name. On this CD are the files. So i can search for the right CD when i want a Song File from it.

Like here:
Image

The CD is called "Sonstige Formate 41". Is that possible to get the "CDROM Type" and "Label" with only one column "Volume Name" that i have?
bdb484
Posts: 68
Joined: Wed Jul 10, 2013 12:31 pm

Re: Import from Access

Post by bdb484 »

Is there anyone still monitoring this who could help?

Having screwed up a giant chunk of my database, I'm trying to take a few hundred lines from an old copy and plug them into my current one. I tweaked the script as best as I could to account for the different file names and fields:

Code: Select all

Sub Test

  Dim DBConn, QResult, MyTrack, CDSuffix
  Const MDBFile = "c:\music.mdb"
  CDSuffix = ""

  Set DBConn = CreateObject("ADODB.Connection")
  DBConn.Provider = "Microsoft.Jet.OLEDB.4.0"
  DBConn.Open "Data Source=" & MDBFile
  Set QResult = DBConn.Execute("SELECT * FROM tvtable")

  Do While Not QResult.EOF

    Set MyTrack = SDB.NewSongData

    MyTrack.ID = QResult("ID")
    MyTrack.Artist = QResult("Artist")
    MyTrack.IDAlbum = QResult("IDAlbum")
    MyTrack.Album = QResult("Album")
    MyTrack.AlbumArtist = QResult("AlbumArtist")
    MyTrack.DiscNumber = QResult("DiscNumber")
    MyTrack.TrackNumber = QResult("TrackNumber")
    MyTrack.EpisodeNumber = QResult("EpisodeNumber")
    MyTrack.SeasonNumber = QResult("SeasonNumber")
    MyTrack.Actors = QResult("Actors")
    MyTrack.Producer = QResult("Producer")
    MyTrack.SongTitle = QResult("SongTitle")
    MyTrack.SongPath = QResult("SongPath")
    MyTrack.Extension = QResult("Extension")
    MyTrack.Year = QResult("Year")
    MyTrack.Genre = QResult("Genre")
    MyTrack.FileLength = QResult("FileLength")
    MyTrack.SongLength = QResult("SongLength")
    MyTrack.IDMedia = QResult("IDMedia")
    MyTrack.CacheStatus = QResult("CacheStatus")
    MyTrack.CacheName = QResult("CacheName")
    MyTrack.Rating = QResult("Rating")
    MyTrack.Bitrate = QResult("Bitrate")
    MyTrack.Seekable = QResult("Seekable")
    MyTrack.Broadcast = QResult("Broadcast")
    MyTrack.PreviewState = QResult("PreviewState")
    MyTrack.PreviewName = QResult("PreviewName")
    MyTrack.PreviewStartTime = QResult("PreviewStartTime")
    MyTrack.PreviewLength = QResult("PreviewLength")
    MyTrack.Author = QResult("Author")
    MyTrack.SamplingFrequency = QResult("SamplingFrequency")
    MyTrack.Stereo = QResult("Stereo")
    MyTrack.VBR = QResult("VBR")
    MyTrack.BPM = QResult("BPM")
    MyTrack.SignType = QResult("SignType")
    MyTrack.SignPart1 = QResult("SignPart1")
    MyTrack.SignPart2 = QResult("SignPart2")
    MyTrack.SignPart3 = QResult("SignPart3")
    MyTrack.SignPart4 = QResult("SignPart4")
    MyTrack.PlayCounter = QResult("PlayCounter")
    MyTrack.LastTimePlayed = QResult("LastTimePlayed")
    MyTrack.AudioCDTrack = QResult("AudioCDTrack")
    MyTrack.FileModified = QResult("FileModified")
    MyTrack.TrackModified = QResult("TrackModified")
    MyTrack.MaxSample = QResult("MaxSample")
    MyTrack.NormalizeTrack = QResult("NormalizeTrack")
    MyTrack.NormalizeAlbum = QResult("NormalizeAlbum")
    MyTrack.Custom1 = QResult("Custom1")
    MyTrack.Custom2 = QResult("Custom2")
    MyTrack.Custom3 = QResult("Custom3")
    MyTrack.Custom4 = QResult("Custom4")
    MyTrack.Custom5 = QResult("Custom5")
    MyTrack.DateAdded = QResult("DateAdded")
    MyTrack.OrigFileLength = QResult("OrigFileLength")
    MyTrack.PreGap = QResult("PreGap")
    MyTrack.PostGap = QResult("PostGap")
    MyTrack.TotalSamples = QResult("TotalSamples")
    MyTrack.PlaybackPos = QResult("PlaybackPos")
    MyTrack.StartTime = QResult("StartTime")
    MyTrack.StopTime = QResult("StopTime")
    MyTrack.SkipCount = QResult("SkipCount")
    MyTrack.GaplessBytes = QResult("GaplessBytes")
    MyTrack.IDFolder = QResult("IDFolder")
    MyTrack.IDEpisode = QResult("IDEpisode")
    MyTrack.TrackType = QResult("TrackType")
    MyTrack.VideoWidth = QResult("VideoWidth")
    MyTrack.VideoHeight = QResult("VideoHeight")
    MyTrack.FrameRate = QResult("FrameRate")
    MyTrack.Copyright = QResult("Copyright")
    MyTrack.Publisher = QResult("Publisher")
    MyTrack.Encoder = QResult("Encoder")
    MyTrack.Lyricist = QResult("Lyricist")
    MyTrack.Conductor = QResult("Conductor")
    MyTrack.Remixer = QResult("Remixer")
    MyTrack.InvolvedPeople = QResult("InvolvedPeople")
    MyTrack.OrigTitle = QResult("OrigTitle")
    MyTrack.OrigArtist = QResult("OrigArtist")
    MyTrack.OrigLyricist = QResult("OrigLyricist")
    MyTrack.ParentalRating = QResult("ParentalRating")
    MyTrack.GroupDesc = QResult("GroupDesc")
    MyTrack.SubTitle = QResult("SubTitle")
    MyTrack.ISRC = QResult("ISRC")
    MyTrack.InitialKey = QResult("InitialKey")
    MyTrack.Language = QResult("Language")
    MyTrack.WebCommercial = QResult("WebCommercial")
    MyTrack.WebCopyright = QResult("WebCopyright")
    MyTrack.WebFilepage = QResult("WebFilepage")
    MyTrack.WebArtist = QResult("WebArtist")
    MyTrack.WebSource = QResult("WebSource")
    MyTrack.WebRadio = QResult("WebRadio")
    MyTrack.WebPayment = QResult("WebPayment")
    MyTrack.WebPublisher = QResult("WebPublisher")
    MyTrack.WebUser = QResult("WebUser")
    MyTrack.ContainerType = QResult("ContainerType")
    MyTrack.StreamCount = QResult("StreamCount")
    MyTrack.StreamInfo = QResult("StreamInfo")
    MyTrack.OrigYear = QResult("OrigYear")
    MyTrack.Tempo = QResult("Tempo")
    MyTrack.Mood = QResult("Mood")
    MyTrack.Occasion = QResult("Occasion")
    MyTrack.Quality = QResult("Quality")
    MyTrack.Lyrics = QResult("Lyrics")
    MyTrack.Comment = QResult("Comment")
    MyTrack.ArtworkModified = QResult("ArtworkModified")
    MyTrack.BPS = QResult("BPS")
    MyTrack.Custom6 = QResult("Custom6")
    MyTrack.Custom7 = QResult("Custom7")
    MyTrack.Custom8 = QResult("Custom8")
    MyTrack.Custom9 = QResult("Custom9")
    MyTrack.Custom10 = QResult("Custom10")
    MyTrack.ExtendedTags = QResult("ExtendedTags")

    MyTrack.UpdateDB

    QResult.MoveNext
  Loop

End Sub
I can get MM4 to pull it up, and it appears to at least get through

Code: Select all

    MyTrack.ID = QResult("ID")
But then it throws Error #348: "Object doesn't support this property or method: 'MyTrack.Artist'" If I delete that line, it throws the same error for the next line, and so on.

Any ideas what could be going on?

Alternatively: If anyone knows a way to just plug a bunch of rows directly into the database, that would also be helpful. I spent a bunch of time trying to teach myself SQLite before learning that MM4 uses some different kind of syntax that makes this impossible, or at least impossible with my limited database proficiency.
Lowlander
Posts: 56465
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Re: Import from Access

Post by Lowlander »

Post Reply