Insert queries in VBA code: syntax error -2147217900 (1 Viewer)

L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
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
 
Guus2005

Guus2005

AWF VIP
Local time
Today, 10:29
Joined
Jun 26, 2007
Messages
2,641
on which line does the error occure?

try executing the sql statements using the currentdb.execute function instead of creating a new connection.

HTH:D
 
L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
The error happens on the execute and still happens when I use currentdb.

I think the error must be a missing carriage return or something like that, because the problem is so weird.

I'm glad you told me about currentdb, though, because I like it better than what I had.

Thanks!! :)

Guus2005 said:
on which line does the error occure?

try executing the sql statements using the currentdb.execute function instead of creating a new connection.

HTH:D
Click to expand...
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:29
Joined
Aug 30, 2003
Messages
36,127
Welcome to the site. What is the data type of Date_Year? All the others for that matter.
 
L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
Thanks for thinking about this....

Before building the strSQL string, all the fields are strings, except for Genre and Track which are bytes. The Access types are Text, except for Genre and Track, which are numbers. At one point, I tried doing a cstr on the strSQL after building the SQL, but it did not help.

pbaldy said:
Welcome to the site. What is the data type of Date_Year? All the others for that matter.
Click to expand...
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:29
Joined
Aug 30, 2003
Messages
36,127
I'm not clear on what this means:

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).

Are you saying they fail if fields other than file name are filled out, or do the ones that fail contain unusual symbols in the file name? Can you post the db?
 
L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
If strFileName is the only variable that has a value, then the SQL works fine. It's only the data that comes from the tag that is the problem.

I'm almost sure it must be a data problem of some kind, but what kind of data problem would create a syntax error when used directly, but work fine when cut and pasted from the debug window.

pbaldy said:
I'm not clear on what this means:

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).

Are you saying they fail if fields other than file name are filled out, or do the ones that fail contain unusual symbols in the file name? Can you post the db?
Click to expand...
 
Guus2005

Guus2005

AWF VIP
Local time
Today, 10:29
Joined
Jun 26, 2007
Messages
2,641
You have changed your code right?

Could you post what you have now or better post a sample database. Debugging is easier when you have a database to play with.


So your code looks like this?
Code: 
Public Function AddToDB(tag As ID3Tag, strFileName As String)

    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
        
    CurrentDb.Execute strSQL
    
AddToDBEnd:
    Exit Function
AddToDBError:
    MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
    Resume AddToDBEnd
End Function

This probably doesn't solve the problem but you could use TRIM to remove any leading or trailing (in your case) spaces.
Code: 
Public Function AddToDB(tag As ID3Tag, strFileName As String)

    Dim strSQL As String
    Dim strLine As String
    
    strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES (" _
        & "'" & Trim(strFileName) & "', " _
        & "'" & Trim(tag.Title) & "', " _
        & "'" & Trim(tag.Artist) & "', " _
        & "'" & Trim(tag.Album) & "', " _
        & "'" & Trim(tag.Year) & "', " _
        & "'" & Trim(tag.Comment) & "', " _
        & tag.Track & ", " _
        & tag.Genre & ")"
        
    Debug.Print strSQL
        
    CurrentDb.Execute strSQL
    
AddToDBEnd:
    Exit Function
AddToDBError:
    MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
    Resume AddToDBEnd
End Function

HTH:D
 
DCrake

DCrake

Remembered
Local time
Today, 09:29
Joined
Jun 8, 2005
Messages
8,632
Never come across ID3Tag type before bit have you tried passing the tag values as individual parameters.

Or you could create a simple array to hold the values

Dim TmpArray(7)

then populate the array with the tag.names


TmpArray(0) = tag.Title
TmpArray(1) = tag.Artist
etc
Then in your strSql use the array elements

& "'" & strFileName & "', " _
& "'" & TmpArray(0) & "', " _
& "'" & TmpArray(1) & "', " _
& "'" & etc & "', " _


David
 
L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
Thanks, Guus2005. In past experimentation, I have put in and taken out trim(). Here is all my code. As you can see, ID23Tag has defined sizes. You are exactly right about my AddToDB function. The only differences are commented out.

Below the code, is my table definition.

Option Compare Database
Option Explicit

Public Type ID3Tag
Header As String * 3
title As String * 30
artist As String * 30
album As String * 30
year As String * 4
comment As String * 28
tag As Byte
track As Byte
genre As Byte
End Type

Public Function GetID3Tag(FileName As String, tag As ID3Tag) As Boolean

'******************************
'Instructions:
'Pass an variable declared as type ID3Tag to Tag Parameter
'and read its member data after the function returns (assuming
'the function returns true)
'****************************

On Error GoTo GetID3TagError

Dim FileNum As Long

tag = clearTag(tag)

If Dir(FileName) = "" Then
GetID3Tag = False
Exit Function
End If

FileNum = FreeFile

Open FileName For Binary As FileNum
Get FileNum, LOF(1) - 127, tag
Close FileNum

If tag.Header <> "TAG" Then
tag = clearTag(tag)
GetID3Tag = False
Else
GetID3Tag = True
End If

Exit Function

GetID3TagError:
Close FileNum
tag = clearTag(tag)
GetID3Tag = False
End Function

Public Function clearTag(tag As ID3Tag) As ID3Tag
tag.title = ""
tag.artist = ""
tag.album = ""
tag.year = ""
tag.comment = ""
tag.track = 0
tag.genre = 0
clearTag = tag
End Function

Public Function AddToDB(strSQL As String)

Debug.Print strSQL

On Error GoTo AddToDB_Error

'cut and pasted from immediate window
'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 ('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)"
'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)"
'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)"

CurrentDb.Execute (strSQL)

AddToDB_End:
Exit Function
AddToDB_Error:
MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
Resume AddToDB_End
End Function

Public Function BuildSQL(tag As ID3Tag, strFileName As String) As String

Dim strSQL 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 & ")"

BuildSQL = strSQL

End Function

Table Definition
create table MP3Desc
(
ID number,
FileName char (255),
Title char (30),
Artist char (30),
Album char (30),
Date_Year char (4),
Comment char (28),
Track number,
Genre number
)

Guus2005 said:
You have changed your code right?

Could you post what you have now or better post a sample database. Debugging is easier when you have a database to play with.


So your code looks like this?
Code: 
Public Function AddToDB(tag As ID3Tag, strFileName As String)

    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
        
    CurrentDb.Execute strSQL
    
AddToDBEnd:
    Exit Function
AddToDBError:
    MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
    Resume AddToDBEnd
End Function
This probably doesn't solve the problem but you could use TRIM to remove any leading or trailing (in your case) spaces.
Code: 
Public Function AddToDB(tag As ID3Tag, strFileName As String)

    Dim strSQL As String
    Dim strLine As String
    
    strSQL = "insert into MP3Desc (FileName, Title, Artist, Album, Date_Year, Comment, Track, Genre) VALUES (" _
        & "'" & Trim(strFileName) & "', " _
        & "'" & Trim(tag.Title) & "', " _
        & "'" & Trim(tag.Artist) & "', " _
        & "'" & Trim(tag.Album) & "', " _
        & "'" & Trim(tag.Year) & "', " _
        & "'" & Trim(tag.Comment) & "', " _
        & tag.Track & ", " _
        & tag.Genre & ")"
        
    Debug.Print strSQL
        
    CurrentDb.Execute strSQL
    
AddToDBEnd:
    Exit Function
AddToDBError:
    MsgBox ("Error writing db record: " & Err.Number & ", " & Err.Description)
    Resume AddToDBEnd
End Function
HTH:D
Click to expand...
 
L

Liz

New member
Local time
Today, 01:29
Joined
May 17, 2009
Messages
6
Thanks, DCrake. I did try passing the tag values as individual parameters a couple of times in a couple of different ways. I did not have any luck.

DCrake said:
Never come across ID3Tag type before bit have you tried passing the tag values as individual parameters.

Or you could create a simple array to hold the values

Dim TmpArray(7)

then populate the array with the tag.names


TmpArray(0) = tag.Title
TmpArray(1) = tag.Artist
etc
Then in your strSql use the array elements

& "'" & strFileName & "', " _
& "'" & TmpArray(0) & "', " _
& "'" & TmpArray(1) & "', " _
& "'" & etc & "', " _


David
Click to expand...
 
Guus2005

Guus2005

AWF VIP
Local time
Today, 10:29
Joined
Jun 26, 2007
Messages
2,641
Code: 
Public Type ID3Tag
    Header As String
    title As String 
    artist As String 
    album As String
    date_year As String 'Year is a reserved word.
    comment As String
    tag As Byte
    track As Byte
    genre As Byte
End Type
Every undefined string is empty ""
Every string with a defined length is of that defined length.

Without a sample database it is difficult to pinpoint the error.
It takes to much time.
 
You must log in or register to reply here.

Similar threads

A
Solved Where Is Syntax Error In My Code - Run-time Error 3075
2
Replies
22
Views
1,725
Ashfaque
A
A
Syntax error (missing operator) in query expression when running Audit Trail module
Replies
13
Views
2,394
AliG
A
L
Query syntax in VBA headbanger
Replies
10
Views
1,015
Lol999
L
M
VBA Insert into combo box and requery issue
2
Replies
21
Views
2,195
Mr_Si
M
E
VBA Macro Syntax Error While Combining Multiple Worksheets
Replies
2
Views
2,013
Escondido
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom