Hi All,
I am working in ACCESS 2003 and am getting a syntax error: -2147217900 when the VBA code tries to execute strSQL using the below Function.
This problem occurs only on the SQL statements that have values other than for the file name (problems are - Samy Deluxe, Black Eyed Peas, TI Paper Trail).
The commented out SQL statements in the code below are cut and pasted from the immediate window.
Every one of the sql statements work if I cut and paste it from the immediate window and directly assign it to strSQL (replacing the original value).
Every one of the sql statements work if I cut and paste it from the immediate window into the SQL View of the Query Designer and click run.
I have tried to do the insert for just FileName and Title and still get the same error. In fact, I have tried lots of different combinations. I've also tried doing separate assignments and trims in the ID3Tag.
Thank you very much,
Liz
Public Function AddToDB(tag As ID3Tag, strFileName As String)
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strLine As String
strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES (" _
& "'" & strFileName & "', " _
& "'" & tag.Title & "', " _
& "'" & tag.Artist & "', " _
& "'" & tag.Album & "', " _
& "'" & tag.Year & "', " _
& "'" & tag.Comment & "', " _
& tag.Track & ", " _
& tag.Genre & ")"
Debug.Print strSQL
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('01 - Samy Deluxe - Internetional Love.mp3', 'International Love ', 'Samy Deluxe ', 'Internetional Love [Maxi] ', '2001', ' ', 0, 7)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('01 Wishing Well ~ Greenwood Studios.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Black_Eyed_Peas_-_Boom_Boom_Pow.mp3', 'Boom Boom Pow ', 'Black Eyed Peas ', ' ', '2009', ' ', 0, 12)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('digitaldivide.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Kelly_Clarkson_-_My_Life_Would_Suck_Without_You.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('T.I.___Paper_Trail___16___Dead_And_Gone__feat._Justin_Timberlake_.mp3', 'Dead And Gone ', 'T.I. ', 'Paper Trail (Explicit) ', '2008', 'Gibzen EXCLUSIVE REMEMBER TH', 16, 7)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Working on a Farm 4_15_07 8_35 AM.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Black_Eyed_Peas_-_Boom_Boom_Pow.mp3', 'Boom Boom Pow ', 'Black Eyed Peas ', ' ', '2009', ' ', 0, 12)"
Set cn = New ADODB.Connection
On Error GoTo AddToDBError
With cn
.ConnectionString = CurrentProject.Connection
.Open
.Execute strSQL
End With
AddToDBEnd:
cn.Close
Set cn = Nothing
Exit Function
AddToDBError:
MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
Resume AddToDBEnd
End Function
I am working in ACCESS 2003 and am getting a syntax error: -2147217900 when the VBA code tries to execute strSQL using the below Function.
This problem occurs only on the SQL statements that have values other than for the file name (problems are - Samy Deluxe, Black Eyed Peas, TI Paper Trail).
The commented out SQL statements in the code below are cut and pasted from the immediate window.
Every one of the sql statements work if I cut and paste it from the immediate window and directly assign it to strSQL (replacing the original value).
Every one of the sql statements work if I cut and paste it from the immediate window into the SQL View of the Query Designer and click run.
I have tried to do the insert for just FileName and Title and still get the same error. In fact, I have tried lots of different combinations. I've also tried doing separate assignments and trims in the ID3Tag.
Thank you very much,
Liz
Public Function AddToDB(tag As ID3Tag, strFileName As String)
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strLine As String
strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES (" _
& "'" & strFileName & "', " _
& "'" & tag.Title & "', " _
& "'" & tag.Artist & "', " _
& "'" & tag.Album & "', " _
& "'" & tag.Year & "', " _
& "'" & tag.Comment & "', " _
& tag.Track & ", " _
& tag.Genre & ")"
Debug.Print strSQL
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('01 - Samy Deluxe - Internetional Love.mp3', 'International Love ', 'Samy Deluxe ', 'Internetional Love [Maxi] ', '2001', ' ', 0, 7)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('01 Wishing Well ~ Greenwood Studios.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Black_Eyed_Peas_-_Boom_Boom_Pow.mp3', 'Boom Boom Pow ', 'Black Eyed Peas ', ' ', '2009', ' ', 0, 12)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('digitaldivide.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Kelly_Clarkson_-_My_Life_Would_Suck_Without_You.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('T.I.___Paper_Trail___16___Dead_And_Gone__feat._Justin_Timberlake_.mp3', 'Dead And Gone ', 'T.I. ', 'Paper Trail (Explicit) ', '2008', 'Gibzen EXCLUSIVE REMEMBER TH', 16, 7)
'insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Working on a Farm 4_15_07 8_35 AM.mp3', ' ', ' ', ' ', ' ', ' ', 0, 0)
'strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES ('Black_Eyed_Peas_-_Boom_Boom_Pow.mp3', 'Boom Boom Pow ', 'Black Eyed Peas ', ' ', '2009', ' ', 0, 12)"
Set cn = New ADODB.Connection
On Error GoTo AddToDBError
With cn
.ConnectionString = CurrentProject.Connection
.Open
.Execute strSQL
End With
AddToDBEnd:
cn.Close
Set cn = Nothing
Exit Function
AddToDBError:
MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
Resume AddToDBEnd
End Function