In this chapter, we will be covering the basic process of starting Access and creating a database. This chapter will also explain how to create a desktop database by using a template and how to build a database from scratch.
To create a database from a template, we first need to open MS Access and you will see the following screen in which different Access database templates are displayed.
To view the all the possible databases, you can scroll down or you can also use the search box.
Let us enter project in the search box and press Enter. You will see the database templates related to project management.
Select the first template. You will see more information related to this template.
After selecting a template related to your requirements, enter a name in the File name field and you can also specify another location for your file if you want.
Now, press the Create option. Access will download that database template and open a new blank database as shown in the following screenshot.
Now, click the Navigation pane on the left side and you will see all the other objects that come with this database.
Click the Projects Navigation and select the Object Type in the menu.
You will now see all the objects types — tables, queries, etc.
Create Blank Database
Sometimes database requirements can be so specific that using and modifying the existing templates requires more work than just creating a database from scratch. In such case, we make use of blank database.
Step 1 − Let us now start by opening MS Access.
Step 2 − Select Blank desktop database. Enter the name and click the Create button.
Step 3 − Access will create a new blank database and will open up the table which is also completely blank.
You store critical data in Access databases every day. Have you ever stopped to consider whether you're taking appropriate actions to protect your database in the event of a hardware failure, disaster, or other data loss?
Microsoft Access provides built-in functionality to help you back up your databases and protect your organization. You can store the backup file anywhere, be it on an online storage account or just a flash drive or external hard drive.
Make an Access Database Backup
These steps are relevant to MS Access 2007 and newer, but make sure to follow the instructions that pertain to your version of Access, be it 2010, 2013, or 2016. See how to back up a 2013 Access database if you need help there.
Start by opening the database you want to have a backup for, and then follow these steps:
MS Access 2016 or 2013
MS Access 2010
MS Access 2007
Tips:
The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.
Syntax
expression.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression A variable that represents a DoCmd object.
Parameters
Remarks
You can use the TransferSpreadsheet method to import or export data between the current Access database or Access project (.adp) and a spreadsheet file. You can also link the data in an Excel spreadsheet to the current Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Access while still allowing complete access to the data from your Excel spreadsheet program. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Access.
Note
You can also use ActiveX Data Objects (ADO) to create a link by using the ActiveConnection property for the Recordset object.
Example
The following example imports the data from the specified range of the Lotus spreadsheet Newemps.wk3 into the Access Employees table. It uses the first row of the spreadsheet as field names.
Download Dataset IndonesiaSupport and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results. To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you’re not sure how to make this happen, see Introduction to queries. ![]() A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = 'Chicago' is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is 'Chicago', Access includes the record in the query results. Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types. In this topicIntroduction to query criteriaA criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access. The following tables shows some sample criteria and explains how they work.
As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references. This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this: City = 'Chicago' AND BirthDate < DateAdd('yyyy', -40, Date()) 1. The City and BirthDate fields include criteria. 2. Only records where the value of the City field is Chicago will satisfy this criterion. 3. Only records of those who are at least 40 years old will satisfy this criterion. 4. Only records that meet both criteria will be included in the result. What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them? If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid. 1. The City criterion is specified in the Criteria row. 2. The BirthDate criterion is specified in the or row. Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below: City = 'Chicago' OR BirthDate < DateAdd('yyyy', -40, Date()) If you need to specify more alternatives, use the rows below the or row. Before you continue with the examples, note the following:
Criteria for Text, Memo, and Hyperlink fields
Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text.
The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid. A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = 'http://www.microsoft.com/', where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.
Criteria for Number, Currency, and AutoNumber fieldsThe following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.
Criteria for Date/Time fieldsThe following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.
Criteria for Yes/No fieldsAs an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.
Criteria for other fieldsAttachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. Lookup fields There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created. Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields. The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2. If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:
Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:
See AlsoComments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |