SQL Insert Statement usage...

[hipopony66]

Ok, I use the following code to get info about mp3 files and then store as strings. Now I want to take the strings and use SQL to INSERT them into a table (add rows). The "Call ADOCreateTable" in the code creates a new table in the db. You should be able to see what I want to do by looking at the below code. I tried just typing INSERT and VALUE SQL commands, but it says it needs to be set = to something. Do I need to declare I'm using SQL first or something?

Private Sub Command2_Click()
Dim x As Integer
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
 
 
 
lstCategories.AddItem txtcategory.Text
Call ADOCreateTable  'create the new table in the db
 
For x = 0 To File1.ListCount - 1    'loop through the filelistbox
    fname = File1.Path & "\" & File1.List(x)
    ReadMP3 fname, True, True
    
    strFilepath = fname              'store the info as strings
    strFileName = File1.List(x)
    strFileSize = FileLen(fname)
    strArtist = GetMP3Info.Artist
    strTitle = GetMP3Info.Songname
    strAlbum = GetMP3Info.Album
    strTrack = GetMP3Info.Track
    strFrequency = GetMP3Info.Frequency
    strBitrate = GetMP3Info.bitrate
    strLength = GetMP3Info.Duration
    
    '------------------------------------------------------------------------
    'Here is where I want to add all the strings into the table
    '------------------------------------------------------------------------
     Next    'do this for every file in the filelistbox
 
 
Call WriteTextFile   'Updates entries in category combobox
End Sub

[hipopony66]

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
      .Columns.Append "Album", adVarWChar
      .Columns.Append "Frequency", adVarWChar
      .Columns.Append "Bitrate", adVarWChar
      .Columns.Append "Length", adVarWChar
      .Columns.Append "Track#", adVarWChar
      
   End With
 
   ' Add the new table to the database.
   cat.Tables.Append tbl
 
   Set cat = Nothing
   
   
 
End Sub

[Pickler]

You can add something along these lines.

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;"
 
SQL = "INSERT into Table (field1,field2,field3 etc..) VALUES ('" & strTitle & "','" & "etc..."
Cnn.Execute SQL

A couple of notes, you can declare the connection object so it can be used by both functions.
and just be careful with your SQL statement that you have apostrophes etc.. in the right spot.

Hope it helps