Microsoft Access Developer Interview Questions And Answers

The name property. I use this property to determine if my reference is correct, as I frequently tell my students, “If you can name it, than you have the right object”. For example, if there is a button called “cmdOpen” on the “frmMainMenu” form, I can reference it with Forms!frmMainMenu.cmOpen.Name. It should show up as cmdOpen on the immediate window.

Data can be managed by using the features of Import text wizard and export text wizard. Here you can save the operation for future use. First you should edit the specification name after clicking it you can make changes to the text box. Path can also be changed by clicking and modifying the changes in text box. Changes to the description of the file name can be made by making a change in the text box and then saving the changes to the path, specification name by pressing enter.

Append query can be used if you would like to add new rows of data to an already existing table.
The process of creating an append query follows these steps they are.
Basic step should be to create a select query
After selecting the query you need to append the query
Destination fields should be selected for each column in the query
Records can be appended by using the function to run.

Microsoft access has huge benefits for a programmer and end user. Some of the benefits are

☛ Relative compatibility with SQL and VBA.
☛ Microsoft SQL server desktop engine is embedded into the Access suite along with getdata base engine which can further help you in programming.
☛ MS Access allows forms to contain data which is altered as changes are made to the underlying table.
☛ It has features which support the creation of all objects in the underlying server.

MS Access is not backwards compatible. To share tables between different versions of MS Access, you can link the tables between two different databases. However, only the tables in the converted database would be available.

MS Access is not backwards compatible. A workaround to share tables between different versions of MS Access would be to link the tables between two different databases. All objects in the database would not be available.

Some developers occasionally rely on indexes to sort their data, but shouldn't. That's because an index is an internal Jet operation that relies on internal rules to speed sorting, which is not the same as actually performing the sort. Records are not stored differently or changed. The sort is not performed until an action is being taken against the data like a query. This is not the same as simply opening a table in its Datasheet View. Although an index often appears to sort data, those internal rules often conflict with normal sorting practices and can have unexpected results. Sorting can be accomplished through the use of a query where ascending or descending can be specified against any one of the fields chosen from the table.

You have probably not chosen to have referential integrity checked when you created the relationship. Delete the relationship (click on the line then choose delete) and try again, this time choosing to have referential integrity checked.

Referential integrity is important. For example, in the Chelmer Leisure database, for the 1:m relationship between the Category and the Membership Tables, referential integrity will ensure that all member records have a membership type that is already listed in the Category table.

When you open an Access database in a newer version of the software, Access will ask you if you want to convert it and then, if you reply 'yes' will do the conversion. When converting from Access 97 to Access 2000, it is recommended that you first compile all the code (see Debug-->Compile all Modules), then repair and compact it (see Tools-->Database Utilities). If you don't do this, you may get a message saying that the database is corrupt. This not does seem to be necessary when converting to XP.

If you reply 'No' to the conversion, then the database can be opened and data amended (I think), but no design changes can be made.

There is also information regarding conversions to/from different versions in Access Help. Use the keywords

convert, access
to find the relevant pages.

A group of objects held together by a class or custom grouping, (You can create your own collection of objects). You may not realize it, but every time you use the syntax Forms!MyForm to either make a form invisible or do other actions, you are using the Form collection in Access. Another useful collection is the Controls collection of the Form class.

Some of the uses of append query are as follows

☛ You will save time by appending data to an existing table rather than manual update.
☛ Appending files based on specific criteria is possible.
☛ If your destination file doesn’t have columns and rows you can add certain columns and rows thus ignoring the rest of them.
☛ Append can save time and labor when dealing with large database updation.

When you are doing initial testing or if you make mistakes entering data and delete a row, the AutoNumber field keeps incrementing from the last number used. You may wish to start from 1 again (perhaps with existing data or perhaps after deleting initial test data).

This is very simply done as follows:
☛ Make a copy of the table (just in case)
☛ Remove all relationships between this table and any others
☛ If the AutoNumber field is a primary key, click on the primary key icon to undesignate it as a key
☛ Delete the row for this field
☛ Insert the row again and recreate the field as an AutoNumber field
☛ Redesignate it as a primary key, if appropriate
☛ Look at the table in design view - it should now be renumbering from 1 again.
☛ Reinstate the table relationships.

You have probably got the field defined as a 'check box' in the table definition. If this field is actually destined for a form, then a check box may be appropriate. However, it can look odd in the table layout or on a report. If you want to change the display, simply look at the property box for the field on the table, form or report and change the display control from 'check box' to 'text'.

You may also like to think whether a Yes/No box really is the most appropriate data type to use. The user has to enter either 'Yes' or 'No' for the field. A 'Yes' (or tick in a check box) on a form for 'Smoker' may make sense, but seems (to me, at least) to be decidedly odd for the 'Sex' or 'member/class' fields for the Chelmer leisure Scenario (though it does save space). You may like to experiment with using a Look-Up Wizard for the data type here, and setting just the two values 'Male' and 'Female' or 'Member' and 'Class' for the list box that the Wizard will create. 'M' or 'F' etc would save space.

Design specs have changed in new releases of MS Access. Visit the on-line help option for "conversion and compatibility" that explains different portions of Access databases that will have difficulty converting. Some reprogramming may be required.

The Controls collection. I most often use this collection when I wish to make certain objects invisible/visible based on the user’s role in the system.

Some developers occasionally rely on indexes to sort their data, but shouldn’t. Thai’s because an index is an internal Jet operation that relies on internal rules to speed sorting, which is not the same as actually performing the sort. Records are not stored differently or changed. The sort is not performed until an action is being taken against the data like a query. This is not the same as simply opening a table in its Datasheet View. Although an index often appears to sort data, those internal rules often conflict with normal sorting practices and can have unexpected results. Sorting can be accomplished through the use of a query where ascending or descending can he specified against any one of the fields chosen from the table.

In the date field properties of the table, form, or properties manually input the format you would like. It is not required to choose one of the date formats in the drop down box. Example would be mm/dd/yyyy.

When you create a custom command bar that uses commands in the Built-In Menus category on the Commands tab of the Customize dialog box, Microsoft Access creates a pointer reference to the original built-in menu. Therefore, any modification you make to the Customs Command bar actually modifies the built-in menu bar. The solution is to split the database. Create a copy of the database, putting tables in one database and the query, forms, etc. in the other, then compile the query, forms, etc. When you need to make changes, you must make changes to the original databases, make a copy of it, then recompile the database.

When an autonumber format is selected, Access will assign a number to each new record that is entered to the table. The initial number default starts at 1, but utilizing the following steps can change this default. Follow the example 1. Create a table of addresses using field names ID, Fname, Lname, Address1, Address2, City, State, and Zip. Be sure to format ID as autonumber and all other fields as Text (or some other format that will accept your data. Save the table as TableA. 2. Add records to TableA. Watch Access automatically assign an incremental number for the ID field starting at 1. 3. Now create a second table using the same fields as above. This time format the ID field as number. Save this table as TableB 4. Add records to the table. This time you will have to assign an ID to each record. Start the first record with ID = 100, and increment from there up. 5. Create an Append query to populate the records of TableB to TableA. As long as you named the fields the same between the two tables, Access will automatically link the field names when you choose Append Query. See the QBE grid. Run the query. 6. Now go back to TableA and add another record. The field ID formatted as autonumber now starts at the next highest value of all the records within TableA. This is the only known way to defeat the autonumber procedure. To answer the question above, add records to TableB duplicating the ID numbers from TableA. Re-run the append query, then view TableA again. The ID field will be duplicated.

There are two common reasons for this:



1 - Existing data violates integrity (error message: "Microsoft Access can't create this relationship and enforce referential integrity" - this message is then followed by a nice clear example of what the problem might be).

The most likely reason that either or both of your tables already have data in them but that the data violates the integrity rule.

Example A: You have data in your membership table but not yet in your category table. The tables will be joined via the category number. As referential integrity requires that all category numbers in the membership table are also in the category table, you must now set data in your category table before creating the relationship.

Example B: You have data in both the membership and the category tables. The likely reason is that you have a category number in your membership table that is not in the category table. Correct your data and try again.

It is safest to set up all validations and rules before entering any data.

2 - Key data types do not match (error message: "relationship should be on the same number of fields with the same data type")

The relationship joins two keys - one in each table. These keys should be of the same data type and size. If the primary key of one table is an Autonumber field (LongInteger), then the corresponding foreign key in the other table should be a Number data type, also LongInteger. If one key is a text field, then so should the other one (and of the same size).

Size limitation does not pertain to number of records in a table within a database. Forms, reports, macros, and modules also contribute to database size, but is rather minimal compared to tables containing records. It is recommended that if large amounts of data will be maintained or constantly deleted and new data imported, put your tables in another database and link the tables back into the main database. The utilities for database compression and repair will nm much more quickly. In Access 2.0 databases the byte size limitation is 1 gigabyte. In general, use the 1gb rule and suggestions here to maintain performance of the database.

MS Access is not backwards compatible. A workaround to share tables between different versions of MS Access would be to link the tables between two different databases. All objects in the database would not be available.

Some of the file extensions present in MS Access are

☛ Access blank project template .adn
☛ Access database ,accdb
☛ Access project .adp
☛ Access Workgroup .mdw
☛ Protected access database .accde
☛ Access workgroup .mdw

Finding records that match criteria is a common database function. To accomplish this specific query you can use the Not operator. For instance, if you want to find all your customers outside California, you might use the expression Not "CA" as your criterion.

This can occur if...:

...you are joining two primary keys. A 1:∞ relationship requires that one key only is a primary key. Check to see that you are joining the correct fields.

...you are joining a primary and a foreign key and the foreign key is specified as 'no duplicates' in the Index property for that field in the table.
(a) If the foreign key can have duplicate values in the table (this is the normal situation) then change the Index property to show this.
(b) If the foreign key really is a unique value in the table then check to see if you need to use a separate primary key for this table; perhaps you can use the primary key from the main table in both tables (as in point 1 above).

You should also check your design carefully. Sometimes a 1:1 relationship is correct (e.g. to save space in the database for information that that doesn't apply to all records, or to put sensitive data in a separate table in order to restrict access). But you need to be able to justify each 1:1 relationship that you have, so that you know that it is correct.