Build Example Program : From Scratch

This is just a very simple, brain-dead database and app, so it's not meant to be sophisticated or correct in terms of database design in any way!! The first thing you need to do is just make yourself up a database table in Access and save it to an mdb file. Sooooooo simple:

The Database Design

1. Open up MS Access (Start - Programs - Microsoft Access)
2. Pick "Start a blank database" from the wizard that pops up
3. Pick a spot to save the mdb file and a name for it (mine was "CDCollectionA.mdb")
4. You'll get to the following window, where you double click on "Create a table in design view:"

the database design main window

5. When you double click that "create table in design view" thingie, you get to this window:

The table design view window

6. You want to follow the following steps to get the table I was working with:
   1. Make a field called ArtistName whose type is Text
   2. Make a field called AlbumTitle whose type is Text
   3. Make a field called Tracks whose type is Number (just a long integer is cool enough)
   4. Select the rows in the design view (as pictured above) that have ArtistName and AlbumTitle
   5. Right-click on that selection, and pick Primary Key from the menu you get. This will make both fields into primary keys. The idea is that they can be primary because you'll never have identical artist names and album titles (otherwise what's the point?!).
7. Once you've got your table built, just close that window. You'll be automatically prompted to save changes to the table design and to give the table a name. I picked CDs, how original. :)
8. Once that's all done, you can either add a couple entries to the database by double clicking the CDs table from the database design main window and inputting them manually or just move on to:
9. Export the database to Access '97 (this is the annoying part of the whole thing) Here's what you do:
   1. Go to the Tools menu
   2. Select Database Utilities
   3. Select Convert Database
   4. Select To Prior Access Database Version
   5. You'll get a save file dialog that will let you enter a name for your database. (I used "CDCollection.mdb") Just save it in the same folder as your Access 2000 version of the file. Whatever you call it, make sure you keep track of which database file is which version!!

Export your database to a prior Access Database Version

The Software Design

Okay, now you've got the database file, and remember where you saved that mdb file at the start of the process. That's where you'll save your vb project too, just to keep things simple. You'll probably eventually want your program and data living in the same folder for simplicity's sake, so it makes sense to just start out like that. Okay, let's build this app:

1. Start up VB
2. Pick Standard EXE from the new project list
3. Add the MSFlexGrid control to your project:
   1. Go to the Project menu, pick Components.
   2. Scroll down the list of components until you find "Microsoft FlexGrid Control 6.0 (sp3)". Select that checkbox and hit the OK button to add the control to your project.

4. Add a FlexGrid to your form by picking the  tool and drawing it on your main form.
5. Add a data source to the form using the  tool and drawing on the form. Change its visibility property to False.
6. Add two frames to the form using the  tool and drawing them on the form.
7. Change the caption of one to Add new entry and the caption of the other to Remove Entry
8. Draw the following controls in the Add new entry frame (yes, actually in the frame):
   1. A text box with the (name) txtArtistName
   2. A label above that text box with the caption Artist Name
   3. A text box with the (name) txtAlbumTitle
   4. A label above that text box with the caption Album Title
   5. A text box with the (name) txtTrackCount
   6. A label above that text box with the caption Number of Tracks
   7. A command button with the (name) cmdAddEntry and the caption Add this info
9. Now, to the Remove Entry frame, add the following controls:
   1. A command button with the (name) cmdRemoveEntry and the caption Remove Selected
   2. A label with the caption Select the entry you want to remove and click the button:
10. Now, the most complicated part is formatting the FlexGrid (which is called MSFlexGrid1) to do what you want. It's fairly customizable, but here's all I did for this example program:
    1. the AllowUserResizing property was set to 1
    2. the Cols property was set to 3
    3. the DataSource property was set to Data1 (** this is required **) this hooks the data source up to the FlexGrid.
    4. the FixedCols property was set to 0 while the FixedRows property was set to 1. (this is recommended)
    5. the FocusRect property was set to 0.
    6. the HighLight property was set to 1.
    7. the ScrollTrack property was set to True.
    8. the SelectionMode property was set to 1 (selection by row only).
    9. the WordWrap property was set to True.
11. Okay, next the code. In the form design window, double click the form, which should bring up the code window with a blank Form_Load() subroutine. Here's the code for it:

 Private Sub Form_Load()
    'the format string just lets you define a format for how
    'your flexgrid will appear
    MSFlexGrid1.FormatString = "Artist Name             |" & _
        "Album Name                                 | Tracks"
    
    'make sure the search path to the db is always in the right spot
    Data1.DatabaseName = App.Path & "\CDCollection.mdb"
    
    'set up the recordsource for the datasource and flexgrid control
    'in this case, it's just a raw SQL query, simple simple.
    Data1.RecordSource = "select * from CDs order by ArtistName"
 End Sub

12. Now, go back to the form design window and double click the Add this info button. You should now have a blank cmdAddEntry_Click() subroutine. Here's what to fill in there:

 Private Sub cmdAddEntry_Click()
    'add a new entry to our table.
    With Data1.Recordset
        .AddNew
        !ArtistName = txtArtistName
        !AlbumTitle = txtAlbumTitle
        !Tracks = txtTrackCount
        .Update
    End With
    Data1.Refresh

    'clear the text fields once the new record is added
    txtArtistName = ""
    txtAlbumTitle = ""
    txtTrackCount = ""
 End Sub

13. Last thing you need is the remove code. In the form design window, double-click the Remove Selected button. You should get a shell for the cmdRemoveEntry_Click() subroutine. This is the code:

 Private Sub cmdRemoveEntry_Click()
    'delete an entry from the database
    With Data1.Recordset
        .Move (MSFlexGrid1.Row - 1) ' we minus one because row zero is the header row
        .Delete
    End With
    Data1.Refresh
    
    'set the focus back to the first add field
    txtArtistName.SetFocus
 End Sub

14. And we are done! It's actually pretty simple to do this stuff, no problem!! Before you run this thing, you will need to save the project to the same folder that you saved your database file from the above section to.