Creating & Using an Access97 DB
Name: Access97 with VB
Author: Joseph C. Antonaccio
Date: May 06, 1998
Description: A basic example that will show you how to create a database in Access97 and then use it with Visual Basic. Many thanks to Joseph who pitched in and wrote this in answer to a question.
Controls needed: Access97
Level: All Levels.
This example shows how to connect to an Access97 Database. When you are finished with this example you will;
1. Know how to create an Access97 database.
2. Know how to create a table in the database.
3. Know how to create a field in the table in the database.
4. Know how to enter some data into a field in the a table in a
database.
5. You'll know how to connect to the database from a VB project.
6. You'll know how to retrieve data from the database.
This is a really simple example that is not useful for anything else other than teaching the basic concepts. It is important enough to be the starting point for other more complex and useful projects. Here's how I go about setting up a VB project.
First I create a Subdirectory on my hard drive where I can save my work.
Let's call it C:\DBBasics (If you want to do Database work, in particular Access97 work, learning Access97 database fundamentals is almost as important as doing the VB work itself.)
Next we need a database. Let's call it MyDb.mdb and let's put it in the directory we created above. Here's how:
With Access97...
When you see the empty access97 screen with all the tabs on top you know that you have just created the shell of a new database.
A two column form appears.
You should be back to the main Access97 screen with the all the tabs showing, and you should see the table tblMyTable that you just created.
Your table tblMyTable will open and you will see a single column. If you see the single column you have successfully created a simple database with one table in it and the one table has a single data field in it.
Now lets enter a test string into the data field. enter the following: My first data retrieval.
Now open VB...
A form appears. This form is what you will see when you actually run the program.
Find the directory we set up and just save the form1 and project1 into the directory. You can get a bit fancier later on and fix the names of the tables, forms, projects etc as you see fit. For now we are just going to run ahead and retrieve our first data string from a real database using VB.
Place a button and a label on the form. (Presumably you are proficient enough to do rudimentary VB.)In the properties box set the label properties:
Border Style = 1
Appearance = 0
Under General Declarations
Dim Db as Database
Dim Rs as Recordset
Under Command1_Click method;
Set Db = OpenDatabase("c:\DBBasics\MyDB.mdb")
Set Rs = Db.OpenRecordset("tblMyTable", dbopensnapshot)
Label1.Caption = Rs!MyText
Run the program. You should see the text string My first data retrieval. appear in the label caption. That's all there is to it. Not!
I purposely left out lots of error checking just so you could get to see the results really quick. But the basics are here. You could easily add many records to the database and more controls in the VB project to navigate those records.
Have fun!