Error Updating DB: Error executing SQL statement "COMMIT"

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

Error Updating DB: Error executing SQL statement "COMMIT"

Postby skj88 » Tue Nov 08, 2011 12:39 am

I am running the code below in MS Access to perform some bulk updates on some songs and I am getting the error:

Error executing SQL statement "COMMIT" : SQL logic error or missing database (1, 21)

It works for about 30 to 60 songs, then stops with the error above. I have tried adding a delay function thinking the DB was not able to keep up with all the updates, but the delay actually made the problem worse! It would only do 5 to 10 updates before throwing the error.

I am using MM 3.2.1

Code: Select all
Sub UpdateMMDB()
Dim SDB, rs, objSong
On Error GoTo ErrHandler
Set SDB = CreateObject("SongsDB.SDBApplication")
SDB.ShutdownAfterDisconnect = False
Set rs = SDB.Database.QuerySongs("Artist = " & Chr(34) & "The Beatles" & Chr(34) & " AND Instr(SongTitle, 'USB')<>0")
Do Until rs.EOF
    Set objSong = rs.Item
    SDB.Database.BeginTransaction
    objSong.Title = Left(objSong.Title, InStr(1, objSong.Title, "USB") - 3)
    objSong.Updatedb
    objSong.writetags
    SDB.Database.commit
    rs.Next
Loop

ExitRoutine:
Set objSong = Nothing
Set rs = Nothing
Set SDB = Nothing
On Error GoTo 0
Exit Sub

ErrHandler:
Debug.Print Err.Number
Debug.Print Err.Description
GoTo ExitRoutine
End Sub


Any suggestions would be appreciated.
skj88
 
Posts: 15
Joined: Sat Jul 02, 2011 10:44 am

Re: Error Updating DB: Error executing SQL statement "COMMIT

Postby skj88 » Tue Nov 08, 2011 8:40 am

I think I may have fixed it. I moved the .commit before the UpdateDB and I was able to update 120 songs without an error. If the error pops up again I will reply to this post with updates.

Steve
skj88
 
Posts: 15
Joined: Sat Jul 02, 2011 10:44 am


Return to Addons developer forum

Who is online

Users browsing this forum: trixmoto and 1 guest