Using WSH to Catalog MP3 Files - Creating the database
(Page 2 of 4 )
At this point we need to write the code that determines whether or not the database file exists and then creates it in the event that it doesn’t.
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & strDatabase & ""
We’ll start by creating our database connection string. We’re going to use Microsoft’s Jet driver to create and access our database. I’ve chosen this driver because it is compatible with Access databases and because it is available natively. You could use any other driver you choose, or even DSN, to create and manage your database. The Jet driver just provides a nice level of simplicity and availability.
If Not objFso.FileExists(strDatabase) Then
At this point, we’re going to begin an If statement. This statement uses the FileSystemObject to determine whether or not the database file exists. If it doesn’t, it will execute the code inside the If statement before continuing. In this case, that code will be used to create our database.
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create strConnection
Set objCatalog = Nothing
This is the code used to create the database. Notice that we’re using the connection string that we specified earlier. You should also note that I’ve intentionally released the catalog object in the last line. I’ve done this so that the database file will not be in use as we continue.
Set oConn = CreateObject("ADODB.Connection")
oConn.Open strConnection
Next, we’ll use ADODB to open the database we’ve created. This database is empty and has no structure, so the next logical step is to create a table in which to store our data.
oConn.Execute "CREATE TABLE MP3Table(" _
& "[Name] TEXT(255), " _
& "[Title] TEXT(255), " _
& "[Artist] TEXT(255), " _
& "[AlbumTitle] TEXT(255), " _
& "[Year] TEXT(25), " _
& "[TrackNumber] TEXT(25), " _
& "[Genre] TEXT(255), " _
& "[Duration] TEXT(255), " _
& "[Size] TEXT(255), " _
& "[BitRate] TEXT(255), " _
& "[Comments] MEMO)"
We use the ADODB Connection object’s Execute method to execute a line of SQL code that creates the MP3Table table in our database. We’re also specifying each of the fields to create within the table. These match the data that we’ll be collecting from the ID3 tags on our MP3 files.
You’ll also notice that I’ve kept the field names in square brackets. This is because several of my field names are considered reserved keywords by Microsoft Access. Enclosing them in square brackets will avoid errors when this query is executed. For uniformity, I carried this practice for each of my fields.
oConn.Close
Set oConn = Nothing
End If
The code needed to create our database is complete, so we can close the database connection, release the object and close our If statement.
Next: Updating the database >>
More Windows Scripting Articles
More By Nilpo
