Getting started with Access 2007
There are several ways that you can get started with Microsoft Office Access 2007, depending on whether you are using Access for the first time, moving data from another database or spreadsheet program into Access, or upgrading from a previous version of Access. This article covers questions that are likely to arise in each of these situations.
What do you want to do?
Start using Access for the first time
Office Access 2007 features a number of improvements that make the process of creating a database easier than ever. Users who have created databases in Access before will appreciate how these new and improved features speed up the creation process.
When you start Office Access 2007, the first screen that appears is the Getting Started with Microsoft Office Access page (unless you started Access by double-clicking a specific Access database file, in which case that database opens instead). The Getting Started with Microsoft Office Access page is the starting point from which you can create a new database, open an existing database, or view featured content from Microsoft Office Online.
Access 2010 has powerful tools that make it easier to track, report, and share your data.
Create a database by using a template
Access provides you with a wide variety of templates that you can use to speed up your database creation process. A template is a ready-to-use database that contains all of the tables, queries, forms, and reports needed for performing a specific task. For example, there are templates that can be used to track issues, manage contacts, or keep a record of expenses. Some templates contain a few sample records to help demonstrate their use. Template databases can be used as is, or you can customize them to better fit your needs.
-
Start Access, if it is not already running.
If a database is already open, do the following to display the Getting Started with Microsoft Office Access page:
-
Click the Microsoft Office Button
, and then click Close Database. 
-
-
Several templates are displayed under Featured Online Templates on the Getting Started with Microsoft Office Access page, and more become available if you click one of the categories under Template Categories at the left side of the Access window. Still more can be downloaded from the Microsoft Office Web site (see the next section, Download a template from Microsoft Office Online, for details).
-
Click the template that you want to use.
-
In the pane at the right side of the Access window, Access suggests a file name for your database in the File Name box. You can edit the file name and specify a different folder. Optionally, you can create your database and link it to a Windows SharePoint Services 3.0 site.
-
Click Create (or click Download, for an Office Online Template).
Access creates and then opens the database. A form is displayed, in which you can begin entering data.
If your template contains sample data, you can delete each record as follows:
-
Click the row header for the record that you want to delete. (The row header is the box or bar just to the left of the record.)
-
On the Home tab, in the Records group, click Delete.
-
-
To begin entering data, click in the first empty cell on the form, and then begin typing. After entering a few records, you can use the Navigation Pane to see whether there are other forms or reports that you might want to use.
Download a template from Microsoft Office Online
If you can't find a template that fits your needs on the Getting Started with Microsoft Office Access page, you can explore the Office Online Web site for a larger selection.
-
Start Access, if it is not already running.
If a database is already open, do the following to display the Getting Started with Microsoft Office Access page:
-
Click the Microsoft Office Button
, and then click Close Database.
-
-
Near the bottom of the Getting Started with Microsoft Office Access page, under More on Office Online, click Templates.
The Office Online Web site appears in a new browser window.
-
Use the search tools on the Office Online site to find and download the template you want.
-
After you download a template, the new database is stored in one of the following folders:
-
Windows Vista c:\Users\user name\Documents
-
Microsoft Windows Server 2003 or Microsoft Windows XP c:\Documents and Settings\user name\My Documents
The next time that you want to work with the database, use Access or Windows Explorer to open it from that location.
-
Create a database from scratch
If none of the templates fits your needs, or if you have data in another program that you want to import into Access, you may decide that it is better to create a database from scratch. For most applications, this usually involves one or both of the following:
-
Creating new tables, and then entering, pasting, or importing data into those tables.
-
Importing data from other sources, which creates new tables in the process.
To learn more about planning and designing a database, or about creating relationships, forms, reports, or queries, follow the links in the See Also section of this article.
Create a blank database
-
Start Access.
-
On the Getting Started with Microsoft Office Access page, click Blank Database.
-
In the Blank Database pane, type a file name in the File Name box. If you do not supply a file name extension, Access adds it for you. The default file location is one of the following:
-
Windows Vista c:\Users\user name\Documents
-
Microsoft Windows Server 2003 or Microsoft Windows XP c:\Documents and Settings\user name\My Documents
To change the location of the file, click Browse
next to the File Name box, browse to and select the new location, and then click OK.
-
-
Click Create.
Access creates the database, and then opens an empty table (named Table1) in Datasheet view.
-
Access positions the cursor in the first empty cell in the Add New Field column.
To add data, begin typing — or you can paste data from another source, as described in the section Paste data from another source into an Access table, later in this article.
Notes:
-
Entering data in Datasheet view is designed to be very similar to entering data in an Excel worksheet. The main restriction is that data must be entered in contiguous rows and columns, starting at the upper-left corner of the datasheet. You should not try to format your data by including blank rows or columns as you might do in an Excel worksheet, because doing so will waste space in your table. The table merely contains your data. All visual presentation of that data will be done in the forms and reports that you design later.
-
The table structure is created while you enter data. Any time that you add a new column to the table, a new field is defined. Access sets the data type of the field based on the type of data that you enter. For example, if you have a column in which you have entered only date values, Access will set the data type of that field to Date/Time. If you later attempt to enter a non-date value (such as a name or a phone number) in that field, Access displays a message informing you that the value does not match the data type of the column. When possible, you should plan your table so that each column contains the same type of data, whether it is text, dates, numbers, or some other type. This makes it much easier to build queries, forms, and reports that select just the data that you want.
-
For more information about working with datasheets, see the article Open a blank datasheet.
-
If you are not interested in entering data at this time, click Close 
.
Add a table
You can add a new table to an existing database by using the tools in the Tables group on the Create tab.
Click Table to create a blank table in Datasheet view. You can use Datasheet view to begin entering data immediately and have Access create the table structure for you as you go, or you can use Design view to create the table structure first, and then switch back to Datasheet view to enter your data. Regardless of which view you start in, you can always switch to the other view by using the view buttons on the status bar of the Access window.
Insert a table, starting in Datasheet view In Datasheet view, you can enter data immediately, and let Access build the table structure behind the scenes. Field names are assigned numerically (Field1, Field2, and so on), and Access sets the field data type, based on the type of data that you enter.
-
On the Create tab, in the Tables group, click Table.
-
Access creates the table, and then places the cursor in the first empty cell in the Add New Field column.
Note: If you don't see a column named Add New Field, you may be in Design view instead of Datasheet view. To switch to Datasheet view, double-click the table in the Navigation Pane. Access prompts you for a name for the new table, and then switches to Datasheet view.
-
On the Datasheet tab, in the Fields & Columns group, click New Field.
Access displays the Field Templates pane, which contains a list of commonly used field types. If you drag one of these fields onto your datasheet, Access will add a field by that name and set its properties to an appropriate value for that type of field. You can change the properties later. You must drag the field into the area of the datasheet that contains data. A vertical insertion bar appears, showing you where the field will be located.
-
To add data, begin typing in the first empty cell — or you can paste data from another source, as described later in this article.
-
To rename a column (field), double-click the column heading, and then type the new name. It is a good practice to give a meaningful name to each field, so that you can tell what it contains when you see it in the Field List pane.
-
To move a column, select it by clicking its column heading, and then drag it to where you want it. You can also select several contiguous columns and drag them all to a new location together.
-
Insert a table, starting in Design view In Design view, you first create the new table's structure. Then you either switch to Datasheet view to enter data, or you enter your data by using some other method, such as pasting or appending.
-
On the Create tab, in the Tables group, click Table Design.
-
For each field in your table, type a name in the Field Name column, and then select a data type from the Data Type list.
Note: If you don't see the Field Name and Data Type columns, you may be in Datasheet view instead of Design view. To switch to Design view, click the Design view button in the status bar of the Access window. Access prompts you for a name for the new table, and then switches to Design view.
-
If you want, you can enter a description for each field in the Description column. The description that you enter is displayed in the status bar when the insertion point is in that field, and is used as the status bar text for any controls that you create by dragging the field from the Field List pane to a form or report, and for any controls that are created for that field by the Form Wizard or Report Wizard.
-
After you have added all of your fields, save the table:
-
Click the Microsoft Office Button
and then click Save, or press CTRL+S.
-
-
You can begin entering data into the table at any time by switching to Datasheet view, clicking in the first empty cell, and typing. You can also paste data from another source, as described in the next section.
If, after entering some data, you want to add one or more fields to your table, you can either begin typing in the Add New Field column in Datasheet view, or you can add new fields by using the commands in the Fields & Columns group on the Datasheet tab.
For more information about creating tables, including the use of table templates, see the article Create tables in a database.
Paste data from another source into an Access table
If your data is currently stored in another program, such as Office Excel 2007, you can copy and paste it into an Access table. In general, this works best if your data is already separated into columns, as they are in an Excel worksheet. If your data is in a word processing program, it is best to separate the columns of data by using tabs, or to convert the data into a table in the word processing program before you copy the data. If your data needs any editing or manipulation (for example, separating full names into first and last names), you might want to do this before copying the data, particularly if you are not familiar with Access.
When you paste data into an empty table, Access sets the data type of each field according to what kind of data it finds there. For example, if a pasted field contains nothing but date values, Access applies the Date/Time data type to that field. If the pasted field contains only the words "yes" and "no", Access applies the Yes/No data type to the field.
Access names the fields depending on what it finds in the first row of pasted data. If the first row of pasted data is similar in type to the rows that follow, Access determines that the first row is part of the data and assigns the fields generic names (F1, F2, and so on). If the first row of pasted data is not similar to the rows that follow, Access determines that the first row consists of field names. Access names the fields accordingly and does not include the first row in the data.
If Access assigns generic field names, you should rename the fields as soon as possible to avoid confusion. Use the following procedure:
-
Save the table.
-
Click the Microsoft Office Button
and then click Save, or press CTRL+S.
-
-
In Datasheet view, double-click each column heading, and then type a valid field name for each column. It might look as though you are typing over data, but the column heading row contains field names, not data.
-
Save the table again.
Note: You can also rename the fields by switching to Design view and editing the field names there. To switch to Design view, right-click the table in the Navigation Pane and click Design View. To switch back to Datasheet view, double-click the table in the Navigation Pane.
Import data from another source
You may have data that you've been collecting in another program and would like to import into Access. Or you may work with people who store their data in other programs, and you may want to work with their data in Access. Either way, Access makes it easy to import data from other programs. You can import data from an Excel worksheet, from a table in another Access database, from a SharePoint list, or from any of a variety of other sources. The process differs slightly depending on your source, but these instructions will get you started:
-
On the External Data tab, in the Import group, click the command for the type of file that you are importing from.
For example, if you are importing data from an Excel worksheet, click Excel. If you don't see the correct program type, click More.
Note: If you can't find the correct format type in the Import group, you may need to start the program in which you originally created the data, and then use that program to save the data in a common file format (such as a delimited text file) before you can import the data into Access.
-
In the Get External Data dialog box, either click Browse to browse to the source data file, or type the full path of the source data file in the File name box.
-
Click the option that you want under Specify how and where you want to store the data in the current database. You can create a new table by using the imported data, append the data to an existing table, or create a linked table that maintains a link to the data source.
-
Click OK.
Access starts the Import Wizard.
-
Follow the instructions in the Import Wizard. The exact procedure you follow depends on the import or link option that you chose.
-
On the last page of the wizard, click Finish.
Access asks whether you want to save the details of the import operation that you just completed.
-
If you think that you will be performing this same import operation again, click Save import steps, and then enter the details.
You can then easily reproduce the import operation by clicking Saved Imports in the Import group on the External Data tab, clicking the import specification, and then clicking Run.
-
If you don't want to save the details of the operation, click Close.
-
Access imports the data into a new table, and then displays the table under Tables in the Navigation Pane.
To learn more about how to import data into Access, follow the links in the See Also section of this article.
Open an existing Access database
-
Click the Microsoft Office Button
, and then click Open. -
Click a shortcut in the Open dialog box — or, in the Look in box, click the drive or folder that contains the database that you want.
-
In the folder list, double-click folders until you open the folder that contains the database.
-
When you find the database, do one of the following:
-
To open the database in default open mode, double-click it.
-
To open the database for shared access in a multiuser environment, so that both you and other users can both read and write to the database at the same time, click Open.
-
To open the database for read-only access, so that you can view it but cannot edit it, click the arrow next to the Open button, and then click Open Read-Only.
-
To open the database for exclusive access, so that no one else can open it while you have it open, click the arrow next to the Open button, and then click Open Exclusive.
-
To open the database for read-only access, click the arrow next to the Open button, and then click Open Exclusive Read-Only Other users can still open the database, but they only have read-only access.
-
If you can't find the database that you want to open
-
In the Open dialog box, click the My Computer shortcut — or, in the Look in box, click My Computer.
-
In the list of drives, right-click the drive that you think might contain the database, and then click Search.
-
Enter your search criteria, and then press ENTER to search for the database.
-
If the database is found, open it by double-clicking it in the Search Results dialog box.
-
Because the search was initiated from the Open dialog box, you must click Cancel in that dialog box before the database will open.
Note: You can directly open a data file in an external file format (such as dBASE, Paradox, Microsoft Exchange, or Excel). You can also directly open any ODBC data source, such as Microsoft SQL Server or Microsoft FoxPro. Access automatically creates a new Access database in the same folder as the data file, and adds links to each table in the external database.
Tips
-
To open one of the last several databases that you had open, click the file name in the Open Recent Database list on the Getting Started with Microsoft Office Access page. Access opens the database, applying the same option settings that the database had the last time that you opened it. If the list of recently used files isn't displayed, click the Microsoft Office Button
, and then click Access Options. The Access Options dialog box appears. Click the Advanced category and then, under the Display section, select the Recently used file list check box. (You can also specify the number of recently used files that you would like to have displayed in the list, up to a maximum of nine.) -
If you are opening a database by clicking the Microsoft Office Button
and using the Open command, you can view a list of shortcuts to databases that you have opened previously by clicking My Recent Documents in the Open dialog box.
Import data from a spreadsheet or other program
If you are familiar with other database or spreadsheet programs, you probably know the basics of how these applications work and what databases are used for. Access differs from many other database applications by allowing you to create relational databases. Access also provides many options for working with other database programs, such as SQL Server.
In this section
Import an Excel worksheet into Access
Many people begin to explore Access after first building a list in Excel. Excel is a great place to start a list, but as the list grows, it becomes harder to organize and keep updated. Moving the list to Access is usually the next logical step.
A database table is similar in structure to a worksheet, in that data is stored in rows and columns. As a result, it is usually easy to import a worksheet into a database table. The main difference between storing your data in a worksheet and storing it in a database is in how the data is organized. Simply importing your entire worksheet as a new table in a database will not solve the problems associated with organizing and updating your data, particularly if your worksheet contains redundant data. To solve those problems, you must split the spreadsheet data into separate tables, each one containing related data. For more information about how to arrange the data in your tables, see the article Database design basics.
Access features the Table Analyzer Wizard, which can help you to complete this process. After importing your data into a table, the wizard helps you to split the table into separate tables, each of which contains data that is not duplicated in any of the other tables. The wizard also creates the necessary relationships between the tables.
Import an Excel worksheet as a table in a new Office Access 2007 database
-
Click the Microsoft Office Button
, and then click New. -
Type a name for the new database in the File Name box, and then click Create.
-
Close Table1.
When asked if you want to save changes to the design of Table1, click No.
-
On the External Data tab, in the Import group, click Excel.
-
In the Get External Data dialog box, click Browse.
-
Use the File Open dialog box to locate your file.
-
Select the file, and then click Open.
-
In the Get External Data dialog box, ensure that the Import the source data into a new table in the current database option is selected.
-
Click OK.
The Import Spreadsheet Wizard starts, and asks you a few questions about your data.
-
Follow the instructions, clicking Next or Back to navigate through the pages. On the last page of the wizard, click Finish.
Note: Access asks whether you want to save the details of the import operation that you just completed. If you think that you will be performing this same import operation again, click Yes, and then enter the details. You can then easily reproduce the operation in the future by clicking Saved Imports in the Import group on the External Data tab. If you don't want to save the details of the operation, click Close.
Access imports the data into a new table, and then displays it under All Tables in the Navigation Pane.
Use the Table Analyzer Wizard to organize your data
After your data has been imported into an Access table, you can use the Table Analyzer Wizard to quickly identify repeating data. The wizard then provides a simple way to organize the repeating data into separate tables, so that it is be stored in the most efficient way. Access preserves the original table as a backup, and then creates new tables that you can use as the basis for your database application.
-
Open the Access database that contains the table that you want to analyze.
-
On the Database Tools tab, in the Analyze group, click Analyze Table.
The Table Analyzer Wizard starts.
In case you are new to the concept of normalization, two introductory pages in the wizard contain a short tutorial — complete with buttons that you can click to see examples. If you don't see the introductory pages, but instead see a check box labeled Show introductory pages?, select the check box, and then click Back twice to see the introduction. If you don't want to see the introductory pages again after reading the introduction, you can clear the check box.
-
On the page that starts with the sentence, Which table contains fields with values that are repeated in many records?, select the table that you want to analyze, and then click Next.
-
You can let the wizard decide which fields go in which tables, or you can make that decision yourself. If you follow the wizard's suggestions, you can still make changes to the table layout on the next page of the wizard.
Note: If you let the wizard decide which fields to put in which tables, its choices may not always be appropriate for your data, especially if there is not much data to work with. You should check the wizard's results carefully. On the other hand, the wizard may suggest a more efficient organization than the one that you are thinking of, so it is a good idea to at least try the wizard's decisions once. If you don't like the suggestions, you can still rearrange the fields manually, and you can always click Back and arrange all of the fields yourself.
-
Click Next. On this page, you specify which tables contain which fields. If you chose to let the wizard decide, you should see multiple tables connected by relationship lines. Otherwise, Access creates only one table containing all of the fields. In either case, you can make changes on this page.
-
You can drag fields from a table to a blank area of the page to create a new table that contains those fields. Access prompts you for a table name.
-
You can drag fields from one table to another table if you think that they will be stored more efficiently there.
-
Most tables will be given an ID or a Generated Unique ID field. To learn more about the ID fields, click Tips in the upper-right corner of the wizard.
-
To undo a change, click the Undo button.
-
To rename a table, double-click its title bar, type the new name, and then click OK.
-
-
After you have the fields arranged the way that you want them, click Next.
-
If the wizard finds records that have very similar values, it will identify those values as possible typographical errors and present a screen where you can confirm what to do about them. Scroll through the list to find any that have values in the Correction column, and then click the appropriate item in the drop-down list. Select (Leave as-is) to keep the wizard from making any changes to the value. When you are finished, click Next.
-
The wizard asks whether you want to create a query that resembles your original table. If you have already built forms and reports that are based on the original table, creating such a query is a good idea. If you choose Yes, create the query, the wizard renames the original table by appending "_OLD" to the table name, and then names the new query by using the name of the original table. The forms and reports that were based on the table now use the query for their data, and they continue to work as before.
-
Click Finish.
The wizard creates the new tables as specified, and then opens them. Close them when you are finished inspecting the results.
Work with data from other programs
Office Access 2007 provides features for working with data that is stored in other programs.
-
Create a new Access database that links to data in another file format You can use Access to open a file in another file format such as text, dBASE, or a spreadsheet. Access automatically creates an Access database and links the file for you.
-
Start Access.
-
Click the Microsoft Office Button
, and then click Open. -
In the Open dialog box, click the type of the file that you want to open in the list. If you're not sure of the file type, click All Files (*.*).
-
If needed, browse to the folder containing the file that you want to open. When you find the file, double-click it to open it.
-
Follow the instructions in the wizard. On the last page of the wizard, click Finish.
-
-
Import or link data to an existing Access database You can either import data from other sources and programs into Access tables so that the data is contained within the Access file, or you can link to the data from Access so that the data remains in the original file (outside of the Access file).
Import or link data
-
On the External Data tab, in the Import group, click the format in which the data is stored.
You can import or link data from the following formats:
-
Microsoft Office Access
-
Microsoft Office Excel
-
Microsoft Windows SharePoint Services
-
Text files
-
XML files
-
ODBC databases
-
HTML documents
-
Microsoft Office Outlook
-
dBase
-
Paradox
-
Lotus 1-2-3
The Get External Data dialog box appears.
-
-
Follow the instructions in the dialog box.
Access will import or link the data to your database. For most formats, you must specify the location of the data, and then choose how you want the data to be stored in your database.
-
Use an earlier-version database in several versions of Access
If your Access database or Access project was created in Access 2000 or later, you can use the database or project in the version of Access in which it was created or in any later version — even if the file is security enabled. For example, Access 2000 files can be used in Access 2000 through Office Access 2007, and Access 2002-2003 files can be used in Access 2002-2003 through Office Access 2007.
You may have a situation where you want to keep your data in an earlier version of Access, but you have users with a later version of Access that want to link to that data but still take advantage of some of the features of the later version. The solution is to create a new "front-end" database in the later version (containing forms, reports, queries, macros, but no tables), and link it to the tables in the earlier version file. Use one of the following procedures, depending on whether your database is contained in one file, or is already split into a front-end/back-end application.
Use a one-file Access database in several versions of Access
If all the tables, forms, and other objects of your Access database are contained in one .mdb file, and you want to use the database in several versions of Access, you can create a new front-end database in a later version and link it to the original file. Users that have the earlier version of Access are still able to use the original database. Users that have the later version can use the new front-end database to link to the same data.
-
Use the following procedure to convert the database to any of the three most recent formats: Access 2000, Access 2002-2003, or Access 2007. This command preserves the original database in its original format, and creates a copy in the format that you specify.
-
Close the Access file. If the file is a multiuser Access database located on a server or in a shared folder, ensure that no one else has it open.
-
Start Access 2007.
-
Click the Microsoft Office Button
, and then click Open. -
Browse to the location of the file to be converted, and then double-click it to open it.
If the Database Enhancement dialog box appears, asking whether you want to enhance the database, click No.
-
If a form opens when you start the database, close the form.
-
Click the Microsoft Office Button
, point to Save As, and then, under Save the database in another format, click the file format to which you want to convert. -
In the Save As dialog box, type a name for the new database.
Note: Unless you are going to save the new database in a different location, its name must be different from that of the original database. In either case, it is usually best to use a different name, so that you can easily distinguish between the front-end database and the back-end database. However, if you are converting to Access 2007 format, the file name extension changes from .mdb to .accdb, so you can use the same file name.
-
Click Save.
-
-
Split the converted database into a front-end/back-end application by using the following procedure:
-
On the Database Tools tab, in the Move Data group, click Access Database.
-
In the Database Splitter dialog box, click Split Database.
-
Type a name for the back-end database, and then click Split.
-
-
Delete the back-end database that the Database Splitter tool created — being careful not to delete your original database.
-
Link the new front-end database to the tables in the original database: On the Database Tools tab, in the Database Tools group, click Linked Table Manager.
-
Click Select All, and then select the Always prompt for new location check box.
-
Click OK, browse to the earlier-version database, and then double-click it.
If all goes well, Access displays a message stating that all selected linked tables were successfully refreshed.
You can now enhance the new front-end database to support new features for users who have upgraded to Access 2000, Access 2002-2003, or Access 2007. Users who have previous versions can continue to use the earlier version database.
If you want, you can also convert the new front-end database to yet another version. For example, if the original database was in the Access 2000 format, you could create an Access 2002-2003 front-end for users with that version, and an Access 2007 front-end for users with that version. Both front-end versions would link to the data in the Access 2000 file.
Use a front-end/back-end application in several versions of Access
If your Access database is already a front-end/back-end application, you need only convert the front end to the Access 2000, Access 2002-2003, or Access 2007 file format. No changes are necessary for the back-end database.
The following procedure shows you how to use the Save Database As command to convert the front-end database to any of the three most recent formats: Access 2000, Access 2002-2003, or Access 2007. This command preserves the original database in its original format and creates a copy in the format that you specify.
-
Close the front-end database. If the file is a multiuser Access database located on a server or in a shared folder, ensure that no one else has it open.
-
Start Access 2007.
-
Click the Microsoft Office Button
, and then click Open. -
Browse to the location of the front-end database, and then double-click it to open it.
If the Database Enhancement dialog box appears, asking whether you want to enhance the database, click No.
-
If a form appears when you open the database, close the form.
-
Click the Microsoft Office Button
, point to Save As, and then, under Save the database in another format, click the file format to which you want to convert. -
In the Save As dialog box, type a name for the new database.
-
Click Save.
You can now enhance the new front-end database to support new features for users who have upgraded to Access 2000, Access 2002-2003, or Access 2007.
No comments:
Post a Comment