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...
Open Access97
Click on New Database
DoubleClick the blank database. Find the directory you created above and
enter MyDb.mdb in the space provided.
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.
Select the Tables tab (it should be the default).
Press the New button on the right.
Select Design View.
A two column form appears.
Under the Column 'Field Name' enter MyText
Under the column 'Data Type' select Text. (Text data type is usually the
default.)
Close the table. When closing the table you will be asked if you want to
save it. Select 'yes' and then enter tblMyTable as the name of the table.
The next message will ask you to create a primary key... select 'No'
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.
Press 'Open'.
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...
Select New from the menu.
Select Standard Exe from the options.
A form appears. This form is what you will see when you actually run the
program.
In file select Save Project As.
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
Select Project from the VB menubar
Select References
From the list check off Microsoft DAO 3.5 Object Library make sure
the check box shows a check mark.
Press Ok.
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!
