To import data into Access data-base

• In the main menu, click on EXTERNAL DATA, in the Import and Link group
• Choose the type of the file you want to import
• To know which type of file can be import or link Access database, follow the instruction in the Get

External Data wizard

The Access 2.0 database has the size limitation of 1 gigabyte.

The table contains your data can build three types of relationship

• One-to-one relationships: A single data in one table is linked with a single data in another table.
• One-to-many relationship: A single data in a table is linked to several data’s in another table
• Many-to-many relationship: Where several data’s in one table is related to several other data’s in another
• table

When you establish a relationship between two data, the first table for your data is referred as the primary key
while the new field in another data is called a foreign key.

Some of the file extension for MS Access are

• Access database.accdb
• Access project.adp
• Access project.mdw
• Access blank project template.adn
• Access workgroup.mdw
• Protected access database.accde

Queries are the medium of manipulating the data to display in the form or report. Queries can join tables,
update data, delete data, sort, calculate, filter, etc.

Joins determines the relationship between tables in Access by stating the details of the relationship
between two tables. You can create joins on the database tools tab or by opening a query in design view.

• Open your database
• Go to -> create tab
• Click on the query wizard icon
• Select a query type
• From the pull-down menu choose the appropriate table
• Select the field you want in the query results
• To add information from additional tables, repeat steps 5 and 6
• Click on next
• JOBSMATE.COM
• Choose the type of results that you would like to produce
• Give your query a title
• Click on finish

• From the toolbar click on the External Data tab
• Click Excel on the Export group
• It will open the export excel spreadsheet dialog box
• Specify the destination of the file name and format
• When you click OK, it will export the data to Excel spreadsheet

To create a table using MAOL, you have to pronounce a variable of type object and then initialize it with
the CreateTableDef() method of the recent database object. This technique takes argument as the name of
the table.

Forms allow to insert data across multiple tables. There are different methods for creating forms but with
Form Wizard method, you can always modify the form later using Design View. To create a form

• From the main menu, click on CREATE and then choose the FORM option
• It will create a new form in Layout view, based on the fields in the Customer table
• Scroll the drop down arrow under the View icon and select Form View. This how the user will see the

form.

• On the form, you will see fields like CustomerId, First Name, Last Name and Date Created
• After filling of all details in the form, you can see the newly created record with data in the table
• Once you completed filling the field in the form, confirm that you click on the save button on the top of the

ribbon

Different methods you can enter data in MS Access are

• Datasheet View
• Form
• SQL View
• Import from External Data (XML, Data Services, HTML etc.)

• In the WHERE clause of a SELECT statement, you use query criteria. For example, you want to find the

address of a particular customer, but you only remember his last name that is Harrison. Instead of
searching all the fields in the table, you use where clause to retrieve the address of the customer.

WHERE [LASTNAME] = ‘Harrison’

• You cannot link or join fields that have dissimilar data types. To link data from two different data sources,

depending on values in fields that have different data types; you will create a WHERE clause that uses one
field as a criteria for the other field, by using LIKE keyword.

• With a small number of users, MS Access is easy to handle and suited for desktop use
• Easy compatibility and sharing compare to MS SQL Server
• SQL server is, usually, used by developers and in a bigger organization
• MS Access is cheaper than MS SQL
• MS Access requires less database administration

In Access, we can only join two tables, in order to join more tables you require to group the first join
together by using parentheses, as if it was a newly derived table. After that, you can join another table to
that group.

Select  * from ( ( Table 1 LEFT JOIN Table 2… ) LEFT JOIN Table3… ) LEFT JOIN Table4…

Look up fields: Ensure that you relate tables, links, etc. if you want to show look up values and not look
up ids. For the display of lookup values, related table should be linked to the destination field.

• With a small number of users, MS Access is easy to handle and suited for desktop use
• Easy compatibility and sharing compare to MS SQL Server
• SQL server is, usually, used by developers and in a bigger organization
• MS Access is cheaper than MS SQL
• MS Access requires less database administration

This can be accomplished by creating a form with customized buttons that point to macros. An easier way
is to use MS Access’ built in function called “Switchboard Manager”. This can be located in
Tools/AddIns.

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.

The process of importing data from another Access database follows these general steps they are

Preparing the document for import operation and make sure that extensions are not in .mde or .accde
because in that extension you can import only tables.

Source should be closed automatically when the process is completed

Make sure that you have permission for read and write.

The import operation present in MS Access does not overwrite or modify any of the existing tables or
objects. If both the source and destination are the same it makes sure that numbers are appended to the
name of the source file. For example a table having a name as info will be renamed as infol.

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.

Relationships are imported from the source to destination without any hindrance but once they land in the
destination they can never be altered or changed and change of extensions cannot be of much help.

Lookup fields: Make sure that you link tables, links, etc if you want to display look up values and not look
up ids. Related table should be linked to the destination field for display of lookup values.

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.

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 ignoringthe rest of them.
• Append can save time and labor when dealing with large database updation.

Importing a SharePoint list to an access database gives you a range of flexible convenient options first a
copy is made into the access database after which you can specify the lists which you would like to copy.
Also you have an option to copy an entire list or only a specific file. Also import operation creates a table
in access which copies the entire fields and records specified into the Access.

This error is caused by delete query and when the unique property is set to no.

Following are the steps which should be taken to solve the problem.

I) Delete query should be opened and if it is not opening then press F4

2) Clicking the query properties underneath the query designer will give you options.

3) In that options you need to locate unique records property and should set it to yes. These options are
present in query property sheet.

Report tool creates a report very fast because it will not ask you for much information for creating your
report. it displays the entire field but it may not be the polished data source you are opting for.

Report wizard allows you to create a table by giving you options on what to appear and what not to appear
You can specify the relations, grouping and sorting between the tables.

Microsoft access name has been changed to Microsoft office access. This software incorporates relational
database management system which combines GUI (graphical user interface) with Microsoft Jet database
engine. It can import and use data from Access, SQL, oracle, etc. This software is used to build application
software.

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
• Jetdata 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 provides a huge range of functions some of them are

• It is used by small business, departments of large corporations, and by amateurs to create applications on

their desktop for data applications.

• Access is very useful for small web based database applications hosted on IIS and using ASP.NET pages.
• It can be used as a RAD for building prototype and Stand alone applications.

Access can be used as a frontend while the backend can be a ODI3C compliant product.

If a programmer doesn’t wish to make changes to the document he can lock the document data and its
code from further changes by changing the extension of the file system to .MDE. Changes can be made
only to the file with an extension MDE. Tools are available in the market which can unlock and decompile
the code but some changes to VBA or VB scripts are irreversible.

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

Different methods you can enter data in MS Access are

• Datasheet View
• Form
• SQL View
• Import from External Data (XML, Data Services, HTML etc.)

Whenever a developer is signing into the code project you will have three options they are disable the
macro, enable the macro and explicitly trusting the publisher. You can trust the publisher by allowing from
the control panel of the windows. Always have an option left to enable or disable the macros because
sensitive information regarding your code can be leaked out within fraction of seconds.