Error Updating DB: Error executing SQL statement "COMMIT"

Post a reply

Visual Confirmation

To prevent automated access and spam, you are required to confirm that you are human. Please place a check mark next to all images of monkeys or apes. If you cannot see any images, please contact the Board Administrator.

Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:
BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON
Topic review
   

Expand view Topic review: Error Updating DB: Error executing SQL statement "COMMIT"

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

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

Error Updating DB: Error executing SQL statement "COMMIT"

Post by 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.

Top

cron