Error Updating DB: Error executing SQL statement "COMMIT"

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

Moderators: Gurus, Addon Administrators

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

Error Updating DB: Error executing SQL statement "COMMIT"

Post by skj88 »

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: 17
Joined: Sat Jul 02, 2011 10:44 am

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

Post by skj88 »

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
Post Reply