Microsoft Access is a DBMS, i.e., Database Management System provided by Microsoft. It is a part of MS 365. MS Access stores data electronically in a computer. The stored data can be easily updated, Accessed, or modified using Microsoft queries.
MS Access has four main objects, namely, Tables, Queries, Forms, and Reports. We have researched and have updated this article with the most frequently asked “Microsoft Access Database Interview Questions & Answers.” Go through our Interview Questions and Answers guide so that you can easily crack your interview.
Read the general interview questions and answers which should help you when you are asked generic questions related to your attitude, aptitude.
Top 30 MS Access Database Interview Questions and Answers
1. Explain the Microsoft Access database in simple terms?
Microsoft Access Database is a DBMS, i.e., Database Management System or a Relational Database Management System. It is a tool for information management. MS Access helps to store information for references, reporting, and analyzing. The data that is stored in the database can be modified, updated, and Accessed using MS queries. Microsoft Access is a member of Microsoft 365.
2. Explain the technique used to increase the size of a database?
One way to increase the size of the database is to use SQL Server Management Studio.
- Open object explorer, then connect to an instance of SQL Server Database Engine and click on the instance.
- Expand database, and click properties.
- In the properties, select the Files page.
- Increase the value by at least by 1 Megabyte in the initial size(MB) column.
- Suppose you want to increase the database’s size by creating a new File. Then create a new File and enter the values and click ok.
Transact SQL to increase the size of the database.
- You need to connect to a database engine.
- Select a new Query from the standard bar.
- Write an appropriate Query code and execute.
3. What is the maximum size for an Access database?
The preferable maximum size for an Access database is generally 2 Gigabytes. There is always an option to work around the Access limitations by linking to the tables in another Access database(Multiple databases).
4. What is the need for the Pivot table?
We use pivot tables to sort, group, recap, restructure, count the total or average the data stored in the database.
- Here the user can transfer rows into columns and columns into rows.
- Pivot tables analyze a large amount of data.
- Pivot tables allow the grouping of any data fields.
- Pivot tables allow Access to a whole new set of data.
- It allows organizing data using automatic calculations.
- Pivot tables section or segment the data based on users, date and time, and other variables.
5. Explain how to export data from an Access Database into Excel format?
- Open the data in the database that you want to export.
- Open the table that you want to export and click on the object windows.
- If you are using Microsoft Access 2003, click on File and then click export.
- An export dialog box appears. From the drop-down, select one of the excel formats, select the data you want to transfer or you can click on “Export All” and follow the steps in the wizard and click ok.
6. How to create a table using Microsoft Access Object Library (MAOL)?
- Open Microsoft Access, then Click on Create→ Table Design.
- A new table opens. Fill in the details such as name and data type.
- You need to set the primary key for the table. For more details, check Primary Key.
- Click on the File and save the table.
7. Explain the WHERE clause in MS Access?
SQL Queries use WHERE CLAUSE and Where clause limits your search Query results. For example, you want to write a Query related to employees. You need to fetch all the employee details stored in the database, whose age is above 30.
Select * From employees WHERE age>30.
The above Query uses a WHERE clause to retrieve all the employee details whose age is above 30.
8. Explain how to create a form in Microsoft Access 2013?
Microsoft Access forms allow a non-technical person to add data to the database. To create a Form in Microsoft Access 2013, let us assume we are creating an Employee FORM.
- Select Employee table and click Create–>Form. Microsoft Access now creates a new form for employees.
- Now go to File→View–>Form View.
- Enter the data ” XYZ” in the respective fields related to employees.
- Now the data “XYZ” will be stored in the Employee’s table.
9. Explain how to update records in the Access database table using VBA(Visual Basics for Application)?
Let us create a table named “ Employees.” For that, we need to write a code.
Dim table_name As String Dim fields As String table_name = "Employees" fields = "([ID] varchar(150), [Name] varchar(150))" CurrentDb.Execute "CREATE TABLE " & table_name & fields
The below SQL code closes a table.
DoCmd.Close acTable, "Employees", acSaveYes
To update records, you need to implement the following code.
DoCmd.SetWarnings (False) DoCmd.RunSQL "Update EmployeesT SET EmployeesT.EmployeeName = 'Name XYZ' WHERE (((EmployeesT.EmployeeID)=1))"
10. How to delete records from the Access database table using VBA(Visual basics application)?
To delete records from the Access database table using VBA, apply the below code.
Let us consider the employee scenario.
DoCmd.RunSQL ("DELETE * FROM " & "Employees" & " WHERE " & "name=XYZ")
Here the above code deletes records related to employees’ names.
11. Name the several File extensions in MS Access?
- Access project.adp
- Access database.mdb
- Access workgroup. mdw
- Access detached database.mdf
- Windows access shortcut report.mar
- Windows shortcut access query.maq
- Windows shortcut access table.mat
- Windows shortcut access form.maf
- Access database.accdb
12. Explain the importance of MS Access?
- MS Access provides easy import and export functions.
- Business people make use of MS Access.
- Programmers use MS Access. The programmers make use of the database.
- Most of the workplaces make use of MS Access to organize the companies data.
- Microsoft Access is implemented in schools and colleges to keep track of student’s and teacher’s data.
- MS Access is of low cost.
- MS Access is designed to integrate with other products of the Microsoft office tools.
- MS Access has Jet Database Format, which contains both the application and data in one File.
- Scalability and legacy data are some of the features of MS Access.
13. Explain super key? How is it different from a candidate key?
A super key is specified as a set of one or more columns(Attributes), specifying a unique row in a table. Here the super key is grouped with specific other keys to identify a row uniquely. For example, consider an employee table with an employee ID, Name, and email as their fields.
Here Employee ID is unique. It can be a super key. Other super keys are (Employee ID, Email), (EmployeeID, Email, Name).
A candidate key is specified as a set of attributes where it identifies unique tuples in a table.
Microsoft Access Interview Questions and Answers
14. How to maintain a relationship between different databases in Access?
- Open MS Access, go to Database Tools–>Relationships.
- A show table window appears. From there, select the tables you want and click add.
- Drag a File from one table to another table.
- Choose the relationship you want to apply for the tables like cascade update related fields, enforce referential integrity, or cascade delete related records and click create.
- Specify the join types.
15. Name the methods used to enter data in MS Access 2013?
You can add data to MS Access 2013 through:
- Datasheet view.
- SQL view (Structured Query Language).
- You can import the external data through MS Excel, CSV Files, and XML.
16. What is a Microsoft Jet Database engine?
Several Microsoft products are built in a database engine known as the Microsoft Jet Database engine.MS Access mainly combines the Microsoft Jet Database engine with a Graphical User Interface(GUI) and Software Access tools.
17. Explain queries in MS Access?
A Query in the database is used to perform calculations, retrieve data, add, change, update and remove data.
Some of the SQL type queries are:
- Select Query.
- Action Query.
- Parameter Query.
- Aggregate Query.
18. How to import data from one Access Database to another Access Database?
To import an object from another Access database, follow the given steps.
- Open the database, click the select File, then Get external data and then click on Import from the menu.
- From the import objects dialog box, click on the type of database object you want to import.
- Click on the Forms tab.
- Click on the desired object tab and click on the select the database object and click ok.
19. How do you add records to a table using an Append Query?
- First, create a select Query to copy the data that you want.
- Next, you need to change the Query type to append Query.
- Next, choose the destination fields in the append query.
- You can preview the appended records in the Dashsheet View and run the Query.
20. Name the types of DBMS?
- Network Database.
- Hierarchical Database.
- Relational Database.
- Object-oriented Database.
21. What is a collection in VBA(Visual Basics For Application)?
Collections contain a group of related objects. It is easy to add items using collections.
22. How to delete a specific table from the database using Access VBA?
We use the following code to delete the table.
For example, to delete a table named Employee, use the below-given code:
Public Sub delTbl() DoCmd.DeleteObject acTable, "Employee" End Sub
23. Explain the advantages of MS Access over MS SQL Server?
|Access database size is 2 gigabytes.||SQL database can hold up to 50000 TB.|
|It has 32000 objects per database.||It has over 2 Billion objects.|
|Access Database has four main objects, namely tables, queries, forms, and reports.||The object in SQL points a reference or stores the data.|
|It creates a modest database for users.||SQL databases are technical.|
24. Differentiate between a Form and Table in MS Access?
A Form is a Microsoft Access Database object used to edit, enter or display a table from a database table. Or through a Query.
A Table is defined as a database object used to store data about a specific subject.
25. What are reports in MS Access?
Reports in MS Access are used to view, format, and summarize the data. It is a result of the database quires.
26. In what format should the database be saved to allow different versions of Microsoft Access to open the database?
.accdb is the database format that should be saved to allow different versions of Microsoft Access to open the database.
27. What can you alter by using Alter table statement in MS Access?
- To add a new field to a table, use “ADD COLUMNS.”
For example, ALTER TABLE Customers ADD COLUMN Email TEXT(25)
Here we are trying to add a new column called email to the customer table.
- To change the data type of an existing field, use “ALTER COLUMN.”
For example, ALTER TABLE Customers ALTER COLUMN ZipCode TEXT(10)
Here, we are altering the customer table zipcode column from integer to text.
- To delete a field, use “DROP COLUMN.”
- To delete a multiple-field index, use “DROP CONSTRAINT.”
28. Can you create a simple Query in MS Access 2013? If so, how?
- Click on the Create tab.
- Click on Query wizard in the Query group, a new Query window opens.
- Select Simple Query Wizard and select ok.
- Select the table from the drop-down you want to use for your Query. For example, let’s take the Employee table.
- Select the fields you want to work with the Query and click next to continue.
- Give a name to your Query and select finish.
29. Name the technique used to increase the size of the database?
To increase the size of the database replication technique is used.
30. What are joins in MS Access?
Joins in MS Access are used to create relationships between the tables.
We have four joins, namely.
- Inner Joins.
- Outer Joins.
- Cross Joins.
- Unequal joins.