VB Contradicts itself! :)

[hipopony66]

Well actually it is an SQL statement, but it does contradict itself. It work perfectly while looping through certain folders of files, but gives "Error in INSERT INTO STATEMENT" error. on another folder. All folders contain MP3 files. So why in hell does it create the correct table for one folder and won't work for the other folder? I've posted my code below.
This is the code that calls a sub to build a table and then adds the info on the files to the table.

Private Sub Command2_Click()
'Dim stuff here
Dim Tag As String * 3
Dim Songname As String * 30
Dim Artist As String * 30
Dim Album As String * 30
Dim Year As String * 4
Dim Comment As String * 30
Dim Genre As String * 1
Dim x As Integer
Dim table As String
Dim fname As String
Dim strFilepath As String, strFileName As String, _
strFileSize As String, strArtist As String, _
strTitle As String, strAlbum As String, strTrack As String, _
strFrequency As String, strBitrate As String, _
strLength As String
 
 
'add the new category to the listbox
lstCategories.AddItem txtcategory.Text
 
'Create a new table in the Database for the category
Call ADOCreateTable
 
'connect to the database
Dim Cnn As ADODB.Connection
Dim SQL As String
Set Cnn = New ADODB.Connection
Cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\windows\system\kamo.mdb;"
 
'set table = to the category added
table = txtcategory.Text
 
'Loop through the filelistbox getting info on the files
MusicMaster.File1.Path = txtpath.Text
 
For x = 0 To MusicMaster.File1.ListCount - 1
    fname = MusicMaster.File1.Path & "\" & MusicMaster.File1.List(x)
    Filename = fname
'Read ID3v1 Tags
Open Filename For Binary As #1
    Get #1, FileLen(Filename) - 127, Tag
    If Not Tag = "TAG" Then
    Close #1
    HasTag = False
        
        End If
        HasTag = True
    Filename = Filename
    Get #1, , Songname
    Get #1, , Artist
    Get #1, , Album
    Get #1, , Year
    Get #1, , Comment
    Get #1, , Genre
    Close #1
'''''''''end of reading file''''''''''''''''''
    
'Store the info as strings
    strFilepath = fname
    strFileName = MusicMaster.File1.List(x)
    strFileSize = FileLen(fname)
    strTitle = RTrim(Songname)
    strArtist = RTrim(Artist)
    
    
    
'Use SQL to insert the information in the strings into the table
SQL = "INSERT into " & table &_
 "(FilePath, Filename, Filesize, Artist, Title) VALUES ('" & Replace(strFilepath, "'", "''") _& "','" _
& Replace(strFileName, "'", "''") & "','" _
& Replace(strFileSize, "'", "''") & "','" _
& Replace(strArtist, "'", "''") & "','" _
& Replace(strTitle, "'", "''") & "')"
 
 
Cnn.Execute (SQL)  'ERROR OCCURS HERE
 
Next

This is the code that in the sub that builds the table:

Sub ADOCreateTable()
 
   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.table
 
   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\windows\system\kamo.mdb;"
 
   ' Create a new Table object.
   With tbl
      .Name = categories.txtcategory.Text
      ' Create fields and append them to the new Table
      ' object. This must be done before appending the
      ' Table object to the Tables collection of the
      ' Catalog.
      .Columns.Append "FilePath", adVarWChar
      .Columns.Append "FileName", adVarWChar
      .Columns.Append "Filesize", adVarWChar
      .Columns.Append "Artist", adVarWChar
      .Columns.Append "Title", adVarWChar
   End With
 
   ' Add the new table to the database.
   cat.Tables.Append tbl
 
   Set cat = Nothing
   
   
 
End Sub

Any help is extremely appreciated. This doesn't make any sense to me.

[moinkhan]

Check to see that the folder name or filename doesn't conflict with any of the SQL Keywords or Reserved Words...
It would be fine if you tell which folders are successfully added and which are not..

[hipopony66]

It adds all the info in the folders: Reggae, Blues, Country, metal, pop. It won't add files in folders named Modern Rock, Hip Hop, Classic Rock. Could it have something to do with that space in the folder name?

[cafeenman]

anything that has a space or special character needs to be surrounded with square brackets. In fact, you can surround just about everything with square brackets.

[mendhak]

[yeah,] [it's] [so] [much] [fun]

[Dreamlax]

[do] [you] [have] [to] [put] [square] [brackets] [around] [punctuation] [?]

[what] [happens] [if] [you] [want] [to] [say] [?]

[BuggyProgrammer]

[maybe][ ][you][ ][should][ ][test][ ][it][ ][[]][[][[][[][][][][out][]][[][][[[][]]][][]]]

[rjlohan]

Different databases accept different punctuation as special characters in different ways. Access, will take single quotes if you submit them like: /'

Something like that anyway...