Foxpro can handle plenty of size, (unlike Access), but it’s an 80s language. Very procedural. If you learned programming with tools like VB, VFB may just be too antiquated to be easily learned. Intuitive it is not. Even for a fluent VFB programmer, VB is still much faster for development.
You do not want to store actual mp3s in your database. Rather, you want filenames and some sort of convention/methodology for knowing what folders they are in, (I would not recommend storing any absolute path names. Storing a relative path, however, would be fine.) Also, you want a Memo field for lyrics.
Access is your easiest (and thus best) bet in this regard. Text is (roughly) two bytes per character to store in Access. So, do you really think you’re going to store more than 500,000,000 characters from the lyrics?
One of my Access databases has 30+ megs of text in various Memo fields with no performance issues. I’d be surprised if anyone had more than 50 megs worth of song lyrics associated with mp3s on their hard drive.
I would strongly recommend the following:
- Use your most comfortable front end language. (Mine would be VB)
- Start off by using Access as the back end, as you obviously are familiar with it.
- Create a DataLayer module where all database functions of any kind are stored, and move all other code into other various forms and modules.
Once you are finished in this fastest of all possible development cycles, use the Package & Deployment wizard (or better yet, Wise Installer) to bundle it up and test it out on a machine that doesn’t have Access. It should work without issue.
If you run into some unforseen problems and are forced to abandon Access, all you have to do is modify your data layer. Trust me, when all data access is stripped down and contained in a single module, changing your backend is a breeze.
One technique, if you want to isolate the data layer, is to send custom user data types to functions. Here’s an example basDataLayer.bas module you might start with if you use VB: (Forgive the obsolete DAO method; it’s easier than ADO for these quickie examples)
Option Explicit
Public Type SongInfoType
Dim lngSongID As Long
Dim strTitle As String
Dim strArtist As String
Dim strLyrics As String
Dim dtmLastPlayed As Date
Dim lngPlayCount As Long
End Type
Public Function LoadSong(ByRef psi As SongInfoType)
Dim db As Database
Dim rst As Recordset
Set db = OpenDatabase("Songs.mdb")
Set rst = db.OpenRecordset("SELECT Title, Artist, Lyrics, LastPlayed, PlayCount FROM tblSongs WHERE SongID = " & psi.lngSongID & ";",dbOpenSnapshot)
If Not rst.EOF() Then
psi.strTitle = rst!Title
psi.strArtist = rst!Artist
psi.dtmLastPlayed = rst!LastPlayed
psi.lngPlayCount = rst!PlayCount
End If
rst.Close
Set rst = Nothing
db.Close
End Function
Public Function SaveSong(ByRef psi As SongInfoType)
Dim db As Database
Dim rst As Recordset
Set db = OpenDatabase("Songs.mdb")
Set rst = db.OpenRecordset("SELECT SongID, Title, Artist, Lyrics, LastPlayed, PlayCount FROM tblSongs WHERE SongID = " & psi.lngSongID & ";",dbOpenDynaset)
If psi.lngSongID = 0 Then
rst.AddNew
Else
rst.Edit
End If
rst!Title = psi.strTitle
rst!Artist = psi.strArtist
rst!LastPlayed = psi.dtmLastPlayed
rst!PlayCount = psi.lngPlayCount
rst.Update
If psi.lngSongID = 0 Then
rst.Bookmark = rst.LastModified
psi.lngSongID = rst!SongID
End If
rst.Close
Set rst = Nothing
db.Close
End Function
Now to save and load your song info, you simply declare a variable of type SongInfoType and pass it to the data layer. Looking at an example, which might be inside a form:
Option Explicit
Sub cmdOpenSong_Click()
Dim siSong As SongInfoType
With Me
siSong.lngSongID = .txtSongSearch.Text
LoadSongInfo siSong
.txtTitle.Text = siSong.strTitle
End With
End Sub
Sub cmdSaveSong_Click()
Dim siSong As SongInfoType
With Me
siSong.lngSongID = .txtSongID.Text
siSong.strTitle = .txtTitle.Text
SaveSongInfo siSong
End With
End Sub
Now this is off the top of my head, and certainly not debugged or anything, but it gives you the basic ideas and concepts. Imagine the entire application is fully developed, and you need to change the data access from, for example, DAO to ADO. Or from Access to (Yikes!) FoxPro. Or to Delphi.
Looking at the data layer, all the functions are going to be pretty straightforward and mindlessly simple to deal with. Nothing else in the app needs to be messed with. This approach lets you use your easiest-to-use backend to get your app up and running, and then once it’s been polished up, you can switch data models on a whim.
One final tip for the data layer strategy: looping through the database. In the data layer:
Public Function SongCount() As Long
Dim db As Database
Dim rst As Recordset
Set db = OpenDatabase("Songs.mdb")
Set rst = db.OpenRecordset("SELECT Count(SongID) As Songs FROM tblSongs;",dbOpenSnapshot)
If Not rst.EOF() Then SongCount = rst!Songs
rst.Close
Set rst = Nothing
End Function
Public Function GatherSongIDs(pa() As Long)
Dim db As Database
Dim rst As Recordset
Dim i As Long
Set db = OpenDatabase("Songs.mdb")
Set rst = db.OpenRecordset("SELECT SongID FROM blSongs;",dbOpenSnapshot)
i = 0
Do While Not rst.EOF()
i = i + 1
pa(i) = rst!SongID
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
Now if you want to run through all the Songs, you can do it thusly:
Private Function ValidateSongs(ppgb As Progressbar)
Dim lngMax As Long
Dim i As Long
Dim alngSongs() As Long
Dim siSong As SongInfoType
lngMax = SongCount()
Redim alngSongs(1 To lngMax)
ppgb.Max = lngMax
ppgb.Visible = True
For i = 1 To lngMax
ppgb.Value = i
LoadSongInfo siSong
If Dir(siSong.FileSpec) <> "" Then
Msgbox siSong.Title & " not found on disk.", vbExclamation, "Error"
Exit For
End If
Next
ppgb.Visible = False
End Function
You can see that once again, even though we’re looping through the table one record at a time and performing an action based on the contents of each record, there is precious little going on in the actual data layer. Certainly nothing complicated, as far as re-writing it for a different back end is concerned.
Let me know if you have any questions. Hope this helps.
PS: I always use a module-level database variables for the actual connection (the db variable, in this case). I’d never keep opening and closing (or forgetting to) the database in every function like I posted in these examples. You always want InitData and CloseData functions in the data layer to open and close your connections, and ideally only call each of them once when your app starts and once when it ends.
