Can we create database in Excel?
This article covers the basic process of starting Access and creating a database that will be used on desktop machines, not over the Web. It explains how to create a desktop database by using a template, and how to build a database from scratch by creating your own tables, forms, reports, and other database objects. It also explains some techniques that you can use to get existing data into your new database. Show
In this articleOverviewWhen you first start Access, or if you close a database without closing Access, Microsoft Office Backstage view is displayed. Backstage view is a starting point from which you can create a new database, open an existing database, view featured content from Office.com — anything you can use Access to do to a database file or outside of a database, as opposed to within a database. Creating a databaseWhen you open Access, Backstage view displays the New tab. The New tab provides several ways that you can create a new database:
Adding to a databaseOnce you are working in a database, you can add fields, tables or application parts. Application parts are a feature that let you use several related database objects together as if they were one. For example, an application part might consist of a table and a form that is based on the table. You can add the table and the form at the same time by using the application part. You can also create queries, forms, reports, macros - all the database objects that you are used to working with. Create a database by using a templateAccess comes with a variety of templates that you can use as-is or as a starting point. A template is a ready-to-use database that contains all the tables, queries, forms, macros, and reports needed to perform a specific task. For example, there are templates that you can use to track issues, manage contacts, or keep a record of expenses. Some templates contain a few sample records to help demonstrate their use. If one of these templates fits your needs, using it is usually the fastest way to get a database started. However, if you have data in another program that you want to import into Access, you might decide it is better to create a database without using a template. Templates have a data structure already defined, and it might require a lot of work to adapt your existing data to the template's structure.
For more information about working with templates, see the article Use a template to create an Access desktop database. Create a database without using a templateIf you are not interested in using a template, you can create a database by building your own tables, forms, reports, and other database objects. In most cases, this involves one or both of the following:
Create a blank database
Entering data in Datasheet view is designed to be very similar to working in an Excel worksheet. The table structure is created while you enter data. When you add a new column to the datasheet, a new field is defined in the table. Access automatically sets each field's data type, based on the data that you enter. If you do not want to enter data in Table1 at this time, click Close . If you made any changes to the table, Access prompts you to save the changes. Click Yes to save your changes, click No to discard them, or click Cancel to leave the table open.Tip: Access looks for a file named Blank.accdb in the folder located at [install drive]:\Program Files\Microsoft Office\Templates\1033\Access\. If it exists, Blank.accdb is the template for all new blank databases. Any content it contains is inherited by all new blank databases. This is a good way to distribute default content, such as part numbers or company disclaimers and policies. Important: If you close Table1 without saving it at least once, Access deletes the entire table, even if you have entered data in it. Add a tableYou can add new tables to an existing database by using the commands in the Tables group on the Create tab. Create 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 automatically sets each field's data type, based on the data you enter.
Create a table, starting in Design view In Design view, you first create the table structure. You then switch to Datasheet view to enter data, or enter data by using some other method, such as pasting, or importing.
Set field properties in Design view Regardless of how you created your table, it is a good idea to examine and set field properties. While some properties are available in Datasheet view, some properties can only be set in Design view. To switch to Design view, right-click the table in the Navigation Pane and then click Design View. To see a field's properties, click the field in the design grid. The properties are displayed below the design grid, under Field Properties. To see a description of each field property, click the property and read the description in the box next to the property list under Field Properties. You can get more detailed information by clicking the Help button. The following table describes some of the field properties that are commonly adjusted. Property Description Field Size For Text fields, this property sets the maximum number of characters that can be stored in the field. The maximum is 255. For Number fields, this property sets the type of number that will be stored (Long Integer, Double, and so on). For the most efficient data storage, it is recommended that you allocate the least amount of space that you think you will need for the data. You can adjust the value upwards later, if your needs change. Format This property sets how the data is displayed. It does not affect the actual data as it is stored in the field. You can select a predefined format or enter a custom format. Input Mask Use this property to specify a pattern for all data that will be entered in this field. This helps ensure that all data is entered correctly, and that it contains the required number of characters. For help about building an input mask, click at the right side of the property box.Default Value Use this property to specify the default value that will appear in this field each time that a new record is added. For example, if you have a Date/Time field in which you always want to record the date that the record was added, you can enter "Date()" (without the quotation marks) as the default value. Required This property sets whether a value is required in this field. If you set this property to Yes, Access does not allow you to add a new record unless a value is entered for this field. Copy data from another source into an Access tableIf your data is currently stored in another program, such as Excel, 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 you copy the data, especially 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, etc.). 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:
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, append, or link to data from another sourceYou might have data that is stored in another program, and you want to import that data into a new table or append it to an existing table in Access. Or you might work with people who keep their data in other programs, and you want to work with it in Access by linking to it. Either way, Access makes it easy to work with data from other sources. You can import data from an Excel worksheet, from a table in another Access database, from a SharePoint list, or from a variety of other sources. The process you use differs slightly, depending on your source, but the following procedure will get you started.
If you chose to import a table, Access imports the data into a new table and then displays the table under the Tables group in the Navigation Pane. If you chose to append data to an existing table, the data is added to that table. If you chose to link to data, Access creates a linked table under the Tables group in the Navigation Pane. Add an application partYou can use an application part to add functionality to an existing database. An application part can be as simple as a single table, or it can comprise several related objects, such as a table and a bound form. For example, the Comments application part consists of a table with an AutoNumber ID field, a date field, and a memo field. You can add it to any database and use it as-is, or with minimal customizing.
Open an existing Access database
Note: You can directly open a data file in an external file format, such as dBASE, Microsoft Exchange, or Excel. You can also directly open any ODBC data source, such as Microsoft SQL Server. 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
|