MS Access Interview Questions

Every day the volume of data is increasing exponentially. As a consequence, the storage the data and access to data are becoming more crucial and complex than ever. So, every organisation strives for excellent database management tools to manage their data, which is a great asset.

Are you aware of which elements drive organisations to pick the right database management system?. The answer is simple – easy to install and maintain, flexible management and scaling, easy importing data, and interactive user interface. In this way, Microsoft Access is a fantastic relational database management system that supports users by fulfilling the parameters mentioned above.

This blog has covered the top-most interview questions in three categories – freshers, experienced, and advanced. So, all types of learners can use these questions and answers to level up their knowledge and confidently attend Microsoft Access interviews.

This blog has covered the top-most interview questions in three categories:

• Freshers
• Experienced
• Advanced

Microsoft Access Frequently Asked Interview Questions:

What is Microsoft Access? What file formats or extensions are used in Microsoft Access, and brief them a bit? What is the use of Append Query? What is the use of Macros in Microsoft Access? What is the size limit of Microsoft Access, and how can you overcome size limitations? How can you update databases in Microsoft Access? What are the different sections of a report in Microsoft Access? Why should you ‘compact and repair’ a database? How can you improve the performance of Microsoft Access databases? What is the advantage of Report Wizards over Auto Reports?

Microsoft Access Interview Questions for Freshers:

1. What is Microsoft Access?

Microsoft Access is a Relational Database system. It stores tables in a single file and other objects such as forms, macros, reports, and modules. You can add new data to a Microsoft Access database. Microsoft Access allows editing, such as modifying and deleting the existing data. Moreover, you can organise and view data as well as share data with external systems.

2.What are the parts or objects of a Microsoft Access database?

The following are the parts of a Microsoft Access database:

• Tables
• Forms
• Reports
• Queries
• Macros
• Modules

If you want to enrich your career and become a professional in Microsoft Access, then enroll in "Microsoft Access Online Training" - This course will help you to achieve excellence in this domain.

3. What are the key features of the Microsoft Access database?

• Database apps can be easily created in desired formats
• Users don’t need any technical knowledge to create and share apps
• Apps can be customised as per the changing needs of businesses
• Microsoft Access easily integrates with multiple data sources such as Microsoft Azure SQL, SharePoint, and Dataverse in Power Platform
• Microsoft Access automates advanced tasks with the support of Macros and VBA.

4. What file formats or extensions are used in Microsoft Access, and brief them a bit?

 The following two formats are extensively used in Microsoft Access.

• .accdb file format: It is the default Microsoft Access file format and supports calculated and attachment data types.
• .mdb file formats: It is the older version file format used in Microsoft Access earlier to the .accdb file format

5.What are the features of .accdb file formats?

• It supports multivalued fields so that you can store more than one value for a record. Multivalued fields are required while integrating Microsoft Access with SharePoint
• It supports attachment and calculated data types so that you can store all types of documents as well as binary files in Microsoft Access databases.
• Because of the .accdb file formats, Microsoft Access integrates with SharePoint and Outlook and satisfies the security requirements, whereas .mdb files don't support the same.
• Microsoft Access allows setting database passwords and encrypting databases. When we use the .accdb file format, Microsoft Access uses the Windows crypto API for encrypting data.
• It tracks long text field history.

6. What are the features of .mdb file formats?

The following are the features of .mdb file formats.

• Mixed-version environment: .accdb has the limitation of being unable to use in the Access versions before 2007. But, we can still open a few .mdb files in the latest versions of Microsoft Access and use them normally.
• Database Replication: We cannot use replication in .accdb files, whereas it can be done in .mdb files.     
• User-level Security: .accdb file doesn’t support user-level security. So, if we need to use user-level security in Access applications, we must use only the .mdb files.

7. What are the security features used in Microsoft Access?

• Database Password: It is the basic level security feature used in Microsoft Access and supports users to Microsoft Access objects in a database
• Workgroup Security: It is the next level security feature that requires both user name and password to Microsoft Access files in a database
• Encryption: Encryption techniques are applied in this level of protection
• MDE Version: The database is converted into an MDE file in this level of  protection

8. What are the Advantages of Microsoft Access?

Following are the advantages of Microsoft Access:

• Microsoft Access is one of the famous Relational Database systems widely used across many organisations
• Without any technical knowledge, you can easily install and use Microsoft Access
• Integration with other Microsoft products can be made quickly
• No matter whether you know about SQL queries or not – you can quickly analyse and query a large amount of data
• Unlike Oracle and SQL Server, Microsoft Microsoft Access is cost-effective in terms of implementation and maintenance.

Do You Want Know More About MicroSoft Access Then, Check out "Microsoft Access Tutorial"

9. What are the Different Queries used in ms Microsoft Access?

Following are the queries are used in ms Microsoft Access:

• Select query
• Parameter query
• Totals query
• Crosstab query
• Make table query
• Append query
• Update query
• Delete query

10. What is the use of Append Query?

Append query is used to add records in existing tables after copying the records from one or more data sources. This query reduces the burden of manual data entry. In other terms, it reduces the process the selecting and copying data multiple times and achieves copying data in a single pass.

11. What are the benefits of using Append queries?

• You can select data from multiple sources at once and copy it into the existing table, reducing selecting and copying data multiple times.
• You can view the data that you have selected through the datasheet. As a result, changes can be made in the selection if required.
• You can select data by setting criteria. For example, you can set criteria for selecting customers who live in a particular city.
• Although the number of rows in the source and destination table doesn’t match, you can still copy the records from the data source and copy them in the relevant rows of the destination table, leaving some rows empty.

12. How will you perform an append query in Microsoft Access?

The following steps must be followed to execute an append query:

• At first, perform the ‘select’ query multiple times to find whether you are selecting the correct records for copying
• Then, convert the ‘select query’ into ‘append query’
• Now, select destination fields either automatically or manually
• You can preview the selected data through ‘datasheet view’ and finally append the records.

13. What are the Different Cloud services that integrate with ms Microsoft Access for data storage and management?

Following are the cloud services integrated with Microsoft Access for data storage and management.

• Microsoft Azure SQL Server
• SharePoint
• Dataverse in Power Platform

14. Name one of the collections used in ms Microsoft Access forms?

 ‘AllForms’ is one of the collections used in ms Microsoft Access, which has all the names of forms used in a database.

15. Brief about the relationships between tables in databases?

Three types of relationships exist among database tables. They are one-to-one, one-to-many, and many-to-many.

• One-to-one: In this relationship, one item on a table will have a relationship with only one item on another.
• One-to-many: In this relationship, one item on a table will have a relationship with multiple items on another table
• Many-to-many: In this relationship, many items on a table will have relationships with many items on another.

16. What do you mean by a Lookup field in Microsoft Access?

Generally, the lookup field in a table will have values retrieved from another table or query. In other words, the lookup field in a table will have the data referenced by another table. Here, lookup Wizard is used to create lookup fields.

17. What is the use of Macros in Microsoft Access?

 Macros are the tools that allow automating tasks and adding functionalities to your forms, controls, and reports without writing codes. Macros provide the subset of commands available in VBA. That's why it is preferred mainly by developers to write codes in VBA.

18. What are the features of Macro in ms Microsoft Access?

• Embedded Macros: It is not usually visible in the navigation pane. But, it is embedded in a form, control, or report when they are created. Note that if you copy a form embedded with macros, then copied form will also have the embedded macros.
• Increased Security: The macro actions and RunCommand arguments don’t require trusted status to run.
• Error Handling and Debugging: The macro actions such as OnError and ClearMacroError do specific actions when errors occur while a macro is running.
• Temporary Variables: The macro actions such as SetTempVar, RemoveAllTempVars, and RemoveTempVar, allow creating and using temporary variables in macros.

19. What are the validation rules used in ms Microsoft Access?

• Field validation rules
• Record validation rules

20. What do you mean by the default value of a field in a Microsoft Access database table?

Microsoft Access can automatically add default values in table fields when creating a new record. For example, when creating a new record for new sales orders, the current date will automatically be added as a default value in the respective fields. Not only this, but we can also set default values such as text, hyperlink datatypes, number, yes/No, memo, or date/time in the table fields.

21. Mention the different data types used in ms Microsoft Access?

• Short text
• Long text
• Date/Time
• Date/Time ExtendedNumber
• Yes/No
• Large number
• Currency
• AutoNumber
• Attachment
• Hyperlink
• Lookup Wizard
• Calculated
• OLE Object

22.  What are the different ways used to create tables in Microsoft Access databases?

Creating tables in Access databases can be done in the following ways:

• Creating a new table in a new database
• Creating a table in an existing database
• Creating new tables by importing and linking to external data
• Creating new tables using the SharePoint site
• Creating new tables using web services

23.  What is the size limit of ms Microsoft Access, and how can you overcome size limitations?

2 GB is the size limit of an ms Microsoft Access database, including system objects. You can overcome size restrictions by integrating ms Microsoft Access with SharePoint, Azure SQL server, and Dataverse in Power Platform.

Microsoft Access Interview Questions for Experienced:

24.  How can you implement automation in Microsoft Access?

Automation can be implemented in Microsoft Access in two ways – using Macros and VPAs. Macros support automating tasks and adding functionalities to forms, reports, controls, etc., without using codes. With VPAs, on the other hand, you can create functions, call windows APIs, respond to events, and many more but using codes.

25.  Why do we need a Primary Key in tables?

We need Primary keys in tables for relating data of many tables and help collate them in a useful way. Also, a primary key is used to prevent duplicate records, which significantly increases data consistency among tables.

When primary keys in Microsoft Access are considered, Microsoft Access automatically creates indexes for primary keys, which helps increase the speed of queries and other operations. Furthermore, Microsoft Access also ensures that every field in the primary key column has values, and they are unique too. Note that a primary key of a table shouldn't be empty or Null, and the values of the primary key shouldn't change frequently.

26.  How can you update databases in Microsoft Access?

We can use ‘update queries’ to add, modify, or delete data in the existing records of databases. With update queries, we can update records of many tables in a single pass. Before using update queries, we need to apply select queries to select the fields you want to update. This process will help ensure that you are updating only the required fields.

27.  What are the basic steps in a simple 'select' query process in Microsoft Access?

We can process the ‘select’ query in Microsoft Access in two ways – using Query Wizard and Design View. While using Query Wizard, there are chances that some design elements will be missing, but they can be overcome by using Design View. However, the basic steps for making a select query are the same, even though the two methods differ.

The following are the basic steps in processing a simple select query in Microsoft Access.

• First, select tables from the data source
• Then select fields from the tables
• You can also specify criteria to limit the records that the query returns

28.  How would you Categorise Products in Inventory Management using Microsoft Access?

First, click' categories' on the products tab in the Microsoft Access Goods web database template. Now, you can customise the list of categories. After that, enter the category in the respective lines of products. This process will help to categorise products in forms and reports. What's more! If you want to delete a category, click the gray box that lies to the left of the product ID and then click 'delete'.

29.  How would you transfer data into Microsoft Access databases from external data sources?

We must follow the steps to transfer data into Microsoft Access databases from external sources.

• In Microsoft Access, on the external data tab, select ‘import & link group’, then click ‘New data source > from database > Microsoft Access.'
• Now, you can see ‘Get External Data – Microsoft Access database import and link wizard’ opens
• Type the name of the data source in the ‘file name’ text box. Then, click ‘browse’.
• Now, you can select import tables, queries, reports, forms, macros, and modules into the current database and then click ‘OK’.
• Then, the Import dialog box opens, where you can select the tables from the Tables tab, select queries from the queries tab, and so on.
• Click options to make additional settings and then click ‘OK’ to complete the process.

30.  How would you create a simple new Report in Microsoft Access?

Creating a simple new report in Microsoft Access is very simple. We can follow the below steps to achieve the same.

• At first, we need to create a record source
• At this stage, create a report using – The Report Tool, the Report Wizard, or Blank Report Tool
• Create labels using the label wizard
• Fine-tune the reports in – the layout view or design view
• Add controls to the reports
• Finally, you can save, view, and print the reports.

31.  How would you create a Report in Microsoft Access using the Report Tool?

Creating reports using the report tool is the faster method when compared with the report wizard. The report created using the report tool displays all the fields of a table or query. Although report tools don’t usually display the polished reports we need, we can still modify them by opening and editing them in the layout or design view.

To create reports using the report tool, you have to perform the below steps:

• In the navigation pane, you select the 'table or query'
• On the Create tab, select ‘Reports Group’. Then click ‘Report’.
• At this point, you will view the report in the layout view.

32.  How would you create a report in Microsoft Access using the Report Wizard?

Compared to the Report tool, the Report Wizard supports creating reports that will have the sorted and grouped data. Also, the Report Wizard allows using data from one or more tables.
To create reports with the help of the Report Wizard, you have to perform the below steps:

• On the ‘create’ tab, select the ‘Reports Group’. Then, click ‘Report Wizard’
• Then, follow the instructions mentioned in the Report Wizard pages. Finally, click ‘finish’ on the last page.

33.  What are the different sections of a report in Microsoft Access?

The following are the listicles of the sections of a report.

• Report Header: It lies at the beginning of a report, and it may contain the report title, date, logo, etc.
• Page Header: Every page will have a page header, and it is printed at the top of all pages.
• Group Header: It lies at the beginning of every new group of records.
• Detail: It will have the controls that make the main body of reports
• Group Footer: It lies at the end of every group of records. You can use this section to add the summary information of the group.
• Page Footer: It lies at the end of every page. You can use this section to add page numbers or information on the page.
• Report Footer: It lies at the end of the report. This section can add summary information of reports, report totals, etc.

34.  Why should you ‘compact and repair’ a database?

Generally, the volume of database files increases over time, increasing the complexity and decreasing the performance of databases. To overcome this setback, we use the ‘compact and repair’ command in Microsoft Access, which supports fixing errors arising from the increasing number of database files. Note that this command doesn't compress data but removes the unused spaces in the storage. This process decreases the database file size and, in return, increases the performance of databases.

35.  What modifications can you make to the databases created using templates in Microsoft Access?

Although Microsoft Access allows you to create databases using ready-to-use templates for forms, tables, reports, etc., you might need to modify the templates as per the requirements.
Let us know what modifications we can make to the databases.

• Adding and deleting fields in tables
• Renaming fields and tables
• Adding fields in forms and reports
• Adding fields that stores images
• Changing the appearance of fields

36.  How would you verify Trusted Publishers before Granting Permissions to use Microsoft Access Databases?

We can verify trusted publishers by using the following criteria:

• Publisher’s records must be signed by their digital signature
• The digital signature must be valid and updated. In other terms, the digital signature shouldn’t be expired.
• A reputed certifying authority must have issued the certificate of digital signature.

37.  How would you import data from another Microsoft Access Database?

With Microsoft Access, we can import data into another database. Besides this, we can control how to import tables and queries. Also, we can copy the relationships between tables if needed. Note that we cannot directly copy data into another database's existing table. Instead, we can import data into a new table and then add data into the existing table using an append query.

Following is the step-by-step approach to importing data into Microsoft Access databases:

• Preparing for the import operation
• Running the import wizard
• Lastly, saving the import settings for future use

38.  How can you improve the performance of Microsoft Access databases?

You can improve the performance of the Microsoft Access database by performing the actions given below:

• Creating primary keys for all the tables
• Setting the‘ compact and repair’ command to run automatically
• Turning off the ‘name autocorrect’ feature
• Opening databases in exclusive mode
• Turning off the ‘autocorrect’ options

39.  How would you add tables in the Microsoft Access database in different ways?

You can add tables to the Microsoft Access database in the following ways:

• Creating tables in a new database
• Creating tables in an existing database
• Creating tables by importing and linking to external data or using SharePoint site and web services

40.  How would you import a SharePoint list into a Microsoft Access database?

Microsoft Access imports a SharePoint list by creating a new table in the database to copy all the columns and fields from the SharePoint list or view. Microsoft Access specifies whether you want to copy an entire list or a specific view. Besides, you can save the import settings for future use.

Following is the step-by-step approach required to import a SharePoint list into a Microsoft Access database:

• At first, locate the SharePoint site that has the SharePoint list that you need to copy
• Select the list that needs to be copied
• Decide whether you want to copy the entire list or a view
• Review the columns and fields in the source list or view
• Identify the database where you want to import the SharePoint list
• Import the SharePoint list into the database
• Finally, you can review the newly created table in the database.       

41.  What are the scenarios in which you can prefer Microsoft Access rather than using SQL Server?

The following are the situations in which Microsoft Access can be preferred over SQL Servers.

• When the database size doesn’t exceed 2 GB
• When you don’t require all the SQL features for your operations
• When the number of concurrent users accessing the database doesn't exceed the limit of 255
• When the users are not technically sound

Advanced Level Microsoft Access Interview Questions 

42.  What is the use of enabling ‘cascade update related fields’ in table Relationships?

If you enable the ‘cascade update related fields' option while defining table relationships, when there is a change in the primary key of a master table, then the child tables will be updated automatically. For example, if a student's roll number is changed, it will be reflected in all other child tables, such as the fees table, marks table, and so on.

43.  What do you mean by Query Parameters?

You can query parameters instead of inserting conditions while executing queries. So, when the query is running, the values for the parameters will be asked, and subsequently, records in tables will be updated.

44.  What do you mean by Referential Integrity in Relationships?

Essentially, referential integrity is a rule which insists that tables shouldn’t have any unmatched foreign keys. For example, you cannot use 'fee record' as a foreign key for a student who hasn't registered for a course or program.

45.  How would you create a ‘Pivot table view’ in Microsoft Access?

Following are the steps required to create a ‘pivot table view’ in Microsoft Access.

• Creating a query as a first step
• Opening the query in a pivot table view
• Adding data fields to the pivot table view
• Adding calculated detail fields and total fields to the view
• Changing field captions and formatting data
• In the final step, you can make filtering, sorting, and grouping of data

46.  How can you sum up a column in a table in Microsoft Access?

We can add a total row by opening the table in a datasheet view and then adding a row. Then, we can select the aggregate function such as sum, max, min, and avg. This process can be achieved in the following steps:

• Creating a basic select query
• Opening the table in the datasheet view
• Adding a total row

47.  How can you sum up data across multiple groups in Microsoft Access?

We can use a crosstab query to sum the data across multiple groups. It will return a grid similar to an excel sheet. Here, the cross query summarises the values and creates two sets of headers – a set of row headers and a set of column headers. As a whole, this query creates three types of data – the data used as row headings, the data used as column headings, and the values you want to sum.

48.  What is the advantage of Report Wizards over Auto reports?

You can have more controls in returning final values in the Auto Reports though creating reports using the Report Wizard requires more effort than creating auto reports.

49.  What are the different options that you can use to filter records?

When it comes to filtering records, it can be done in the following ways:

• Filtering by form
• Filtering by selection
• Filtering by inputs
• Advanced filtering or sorting

50.  How would you differentiate between using filters and using queries to find records?

Using filters, we can quickly find records for the criteria from a datasheet or form that is already open. On the contrary, a query is used to find records from one or more tables. Besides, you can save queries for reuse in the future.

51.  What are the uses of different controls applied in Microsoft Access?

Controls in Microsoft Access are essentially objects used to display data and perform actions that enhance the user interface. Let’s see the listicles of the three types of controls below:

• Bound Control: This control type will have a data source. It supports to display of values from fields from the database. The values can be a text, number, picture, date, or graph.
• Unbound Control: This type of control doesn't have a data source. And it is used to display rectangles, lines, and pictures.
• Calculated Control: In this control type, expressions are the data source rather than fields or queries. Here, expressions can be a combination of operators, function field names, and constant values.

Conclusion:

Well! You might have gone through all the Microsoft Access questions and answers completely. We hope that you are ready for Microsoft Access interviews. But, always keep in mind that success cannot be made overnight. So, all you will need to do is that keep reading this blog, again and again, to make a solid base on the topic. Once you are thorough, cracking a Microsoft Access interview is no more – a big deal.