Okay,
I’ve Built My Database, Now What?
Well
Folks, it certainly is nice to see all of you again. In case you
missed the previous articles, our trek along the Access database
creation highway has taken us through preparation
and into the realm of the various data
types available to us. We are going to continue this adventure
with Populating the Database.
Now
you have all your planning done, and your tables are built, normalized
and ready to go. You are probably asking yourself “What
Next?” I answer, “Populate the database!” In Database terms,
that merely means putting the data in or data entry. We will
discuss several methods of populating our database including datasheet
entry, forms, and importing and linking tables.
Adhering
to the K.I.S.S. Principle
The
simplest method of entering data in the database, for a beginner, is by
using the datasheet (see figure 1). The datasheet is a simple
representation of the table with one row for each record.
The
datasheet has several advantages and disadvantages to its use.
Some of the advantages include the simplification of data entry by
eliminating unnecessary graphics, the ability of the user to see all of
the data; (this can be a disadvantage as well.) A simple tab
through of all the fields in the table.
While
there are some advantages, the datasheet is generally not accepted as a
viable means of data entry in a user database. The main
disadvantage of the datasheet is the fact that the creator does not have
as much control over what the user can do or see. Another
disadvantage lies in the inability to use some of Access’s more
advanced features.
To
enter data using the datasheet you merely highlight the table in your
database window and click Open. This will bring you up in the
datasheet view.
Figure 1
Steering
the User
While
the datasheet is quite simple, the Form can be quite complex. The
form gives the developer a great deal of control over the user’s
ability to enter and view data. To see the difference between a
datasheet and a view, take another look at Figure 1, and then look at
the same data in Figure 2. Both of these are based on the same
table. The form in Figure 2 also has a sub form. We will
touch briefly on these a little later. By using the form’s
greater variety of controls, the developer can steer the user into only
the actions that are desired.
Figure 2
Going
to the forms tab and clicking the New button creates the form. You
are given the opportunity to select the table you choose to make a form
for and several methods of creating the form. We will be discussing two
of these, Design View and the Form Wizard.
The
Form Wizard is a simple tool that will take the guesswork out of
creating your form. I highly recommend the Form Wizard when you
are first starting out. It is a great tool to learn about the
basics of creating a form. It will automatically create your form
with the fields that you designate. You can then go into the newly
created form and add additional elements to it.
Now
that you have your form created, you are ready to set your form and
field properties. This is where you steer the user. We will
briefly discuss some of the major elements of form control here.
Next month’s issue will cover forms in greater detail. For our
purposes here, we will concentrate on the Data tab of the Form
properties.
The
data tab (See Figure 3) is where you control what the user is able to
see as he/she enters new data into the table. A few of the key
controls are Allow Edits, Allow Deletions, and Data Entry. Allow
Edits does just what it sounds like it does. If set to no, it will
not allow the user to edit data once it has been entered. Allow
Deletions, again, is exactly what it sounds like. If set to yes,
you are allowing the user to delete a record.
Finally
Data Entry, this property is not quite as obvious as it sounds. However,
it can be the most restrictive of these properties. By setting
Data Entry to yes, the form will open to a new record only. Once a
user leaves a record, they can no longer view it. This can be very
useful when the user is dealing with confidential data, such as a social
security card number. They can enter it, do what they need on the
current form, then after they leave the form, the data is secured from
their view.
There
are numerous other property controls on a form that can be used to steer
the user in a particular direction, from tab settings and stops to input
masks. All of which guide the user into entering data the way the
developer wants them to. We will cover each of these in greater
detail in next month’s issue.
Figure 3
Another
little treat of using forms is the sub-form. A sub-form is another
form that is made part of your form. If you look back at Figure 2,
there is a sub-form on it. Basically the sub-form is a subset of
data from another table that matches a part of your current form.
In this case, it is all the products within the category. With
this one form, you can view all of your categories and the products
within each category. Sub-forms will be addressed in a future
article.
“My
Data already exists in a database, why should I Re-enter it?”
If
you already have access to your data in another database there is no
reason to duplicate your efforts. You can get that data into your
database in one of two ways. If you want the complete set of data
and it will not change, you can simply import the data, or if the data
is likely to change, (or is simply too large for your database) you can
link to the data.
The
process for importing or linking are much the same, the only difference
being where the data will be stored. As with most Microsoft
products, there are conversion wizards to allow you access to various
forms of data. In the import selection box, you can choose another
Microsoft Access Database, Microsoft Excel, Text Files, HTML Files,
Dbase versions 3, 4, and 5, Microsoft FoxPro databases and ODBC
databases which opens up a whole new set of databases.
The
process for either is quite simple, provided you are selecting any
option other than the ODBC database. Merely click file, Get
External Data, then select whether you want to Import or Link.
Next select your input type and navigate to its location. Select
what you want to import and a wizard will walk you through the rest.
ODBC
(Open DataBase Connectivity) databases require a little extra
preparation to get to their data. The advantage of doing this
extra work is that you can link to a much larger database. ODBC
databases include Microsoft SQL Server, Oracle, Sybase SQL Anywhere, and
PostgresSQL. When you select ODBC for a database type, you will
get a new window asking you to select the ODBC connection you wish to
use. You may also set up a new connection at this point.
Let’s use an SQL database as an example. In our example, we want
to connect to the Products table in the Northwinds database on the Sales
server. The first selection you will need to make is whether you
want this connection to be for this machine or for this particular file.
Which you choose will depend on how often you will need to make this
connection. For our purposes we will select Machine and click on
that Tab. After clicking New, we are presented with another
option. Do we want this to be a User or System connection?
Again this will depend on your situation. Will multiple users be
using this application from this machine? Select system. If
only one use will be using this particular application but others use
the machine, select User. This will provide an extra layer of
security for your application, as even if someone were to open it, they
would be unable to access the data.
We
have chosen system, as we will have several users for our example.
Now you are presented an option of what kind of driver you need to use,
this will be selected based upon your data source. In our case, we
have a Microsoft SQL Server database so we will select that. If
you do not see the connection you need, do not fear, not all are loaded
with the default install of Access and more are available from
Microsoft. The next page to come up is a summary showing you what
you have selected. If you are following along, you should see:
System
Data Source
Driver:
SQL Server
If
this is correct, click Finish. You are not actually finished; this
will bring you into a new wizard to set the properties of your SQL
Server connection. The name of your Data Source will play a part if you
choose to use it again. Name it appropriately so that you know
what it actually is. The description field is to help you in
finding it again. The Server field is critical; you must enter the
name of the server exactly as it appears in the network. In our
example, the server is named Sales, so we enter that and click next.
This is where the authentication is set up. Do you want to use the
network authentication (if you are on an NT network) or do you want to
use SQL authentication. These setting depend on what the SQL
database is set up to handle. This is where you want to check the box
marked change to default database and enter your database name, in this
case, we will enter Northwinds. Click Next. The one thing to watch
for here, is one of the default settings. Use ANSI nulls and paddings
can cause corruption in an SQL database, uncheck it! The rest of
the settings here depend largely on your SQL database but most are
acceptable as defaults as are those on the next page. Click Next
and then after perusing your settings on that page, click finish.
You will now be taken to a Test Your Connection screen. Click on
the Test Connection and make sure that you get a Test Completed
Successfully message. Then click close. We are now ready to
select our table. They will be listed in a text box with a check
box beside them. Select the box next to the tables you want, in
this case, Products. Before clicking finish, decide whether you
want to save the password. If you do not check this box, the
password you entered in the ODBC connection must be entered by the user
when they enter this table. Click Import, then close. You
now have your table linked.
Any
changes made to that table are now made on the SQL Server. The
user will also be able to see any changes that have been made in the
table on the server.
“What
about Excel?”
Excel
imports are pretty much the same as the procedure for importing data
from another database. It is actually one of the options
available. Here are a couple of methods to keep you on the right
track.
As my
wife often reminds me, it isn’t just importing the data, it’s the
quality of the data. When importing from Excel it is imperative
that your data be in good shape. Clean data is the key to a
successfully importing data from Excel to Access.
The
first method is by far the simplest, a simple copy and paste of your
data. Open both Excel and Access, then highlight and copy your
data in Excel, switch to Access and click on the Tables tab, then click
paste. It will only ask you one question; “Does your first row
contain field names?” A Yes response uses the first row as your
field names and a No response gives you a simple, but unhelpful Field1,
Field2, etc.
The
second method is the one described above in the Importing section.
Simply click, File, Get External Data, Import and then on the pull down
list at the bottom for files of type, change it to Microsoft Excel.
This will start a wizard that will assist you in importing your Excel
spreadsheet. The first page will ask you whether you want to
import worksheets or ranges and show you a sample of your data to be
imported as well. Click next and you will be taken to another
question, does your first row contain field names for your table?
Next is the option of importing into a new table (highly recommended) or
into an existing table. Until you have done this numerous times, I
highly recommend importing into a new table then moving it into your
existing table after you ensure that you have a good import. Next
in the wizard is the field options page. This lets you set up to
three options on each field, first the field name, second is the data
type, and last is whether or not to index the column. You can also
elect to skip an entire column if necessary. If you have
calculated fields in your Excel spreadsheet and are importing all your
component fields it is recommended that you skip the calculated field.
Access can do the calculations on the fly. It also allows you to
change the fields without worrying about having to change the result
manually. Next is the primary key selection. After clicking
next all that is required is a table name to import to. Click
Finish and your data is in. Access will produce an Import Errors
table so that you know if data failed to import, what it was and why.
Still
yet another method and a slightly automated one at that is to use the
macro command Transfer Spreadsheet. While it is not quite as
detailed as the Import Wizard, it does automate the tasks for repeated
imports. The macro requires six pieces of data. First is the
transfer type, you can import, export or link. Next is the
database type. You simply select your spreadsheet format from a
pull-down list. Next is the table name that you would like it to
import into. This is followed by the File Name, unless your file
is in the same directory, give the full path. Then the Has Field
Names option is needed, simply select yes or no. Finally you will
need to input your range. You have three options in this field.
Leave it blank and you get the entire spreadsheet or you can input your
range as either a range of cells or a named range. After saving
your macro you are ready to go.
As
you can see you have numerous options for getting the data into your
database. Direct Input requires either the Datasheet or a form,
depending on your users skill level and the level of security you want
on it. If the data already exists, you can opt to import or link
to it, largely depending on how dynamic the data is. And of
course, you can get your data from an Excel spreadsheet.
Stay
tuned in coming months for:
September:
Forms
– an in depth look at creating various types of forms to guide your
user through your database.
October:
Queries
– What you can do with your data once you have it in the database
November:
Reports
– Presenting your data in an end user friendly format.
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
|
,
