contactus

 

Our Portfolio

Data Management Service

Businesses by their nature are volatile. Personnel change, business direction changes, but data should be kept consistent as a tool for the business. Data can be a business’s lifeblood. It can be the making or breaking of processes, inter-departmental communication, inventory systems, website sales performance, and many other business functions.

At Phoenix Consultants Group, we have tremendous experience of understanding businesses.

We do more than just develop solutions, we understand the specific needs of each of our clients, and then we go about delivering a solution that is tailored for maximum benefit to the organization. Our experience allows us to sit on both the business side and the IT side of development and provide real and measureable added value to your operation.

We offer stability and expertise that ensures that everything will tick along smoothly.

Businesses these days are moving increasingly towards outsourced services so that they can focus on what they do best. We enable you to focus on what you do best while we do what we do best; maintaining the integrity and usability of your data and using technology to facilitate your success.

We often find that businesses have to double handle data, or configure and arrange data in order to run any sort of meaningful reports. We eliminate these requirements by using efficiency producing arrangements that provide clean, valid data that can be accessed and used for the purposes it was captured.

At Phoenix Consultants Group, we seek to provide you with flexibility, great value, and tailored database solutions… solutions grounded in the business world; designed in the IT world.

If you want to find out more about our data management services, feel free to contact us today.

 

Middleware isn’t part of your operating system, nor is it usually part of your main business system.  Sometimes it acts as an interface between the user, and the business system, by creating a web access for your users.  Sometimes it takes data and puts it in the correct tables, or retrieves it for analysis.  Maybe your middleware is gathering information from somewhere else in the world, and using that to populate your business orders or purchases.

Why do you need middleware, though?  To put it simply, not every software package can do everything for your business.  There is no such thing as a “Turn-key solution”, with programs that will solve all your business need “right out of the box.”  As complex as business applications are today, while they will handle most of your requirements, they can never meet all your wants.

That’s because every business is different.  Every company, no matter their end product, has different needs, different wants, different processes, and different forms of data.

Businesses, like people, have their own individual needs.  Middleware helps businesses maintain their individuality while still operating with maximum ease, speed, and efficiency.

Phoenix Consultants Group is one of the more experienced businesses in the middleware development field.  With over fifteen years in the area of software creation, Phoenix Consultants Group knows how to work with you to determine your needs--and we've got the expertise to create the design that will fulfill them. We have worked with everyone from Fortune Global 500 corporations to small businesses, as well as with private, government, and law enforcement sectors. We understand everything necessary to build the appropriate middleware for your business--from assessing your requirements all the way up to training employees and guarantying the operation and data security of the software.

We are here to ensure your business runs smoothly, because satisfaction is our business.

Phoenix Consultants Group.  For your middleware needs.  For your system needs.  For all your software business needs.

So what exactly is Microsoft Access and how will it help my business?  This article provides an overview of the components that exist inside of MS Access that Phoenix Consultants Group can program to your individual specifications:

There are several pieces that exist within a MS Access system.  You have tables, forms, queries, reports, and modules.  Each of the pieces is called an "object" by Microsoft.


What is a Microsoft Access Table:

A table in MS Access is quite different than a table in real life.  Instead of having wooden legs and being used for meals.  Access Tables are a grid made up of rows and columns.  Here's and example of a table in Access:

 

What is a Microsoft Access Form:

 A form is an object that you can use to create a user interface for a database application. A "bound" form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source. Alternatively, you can create an "unbound" form that does not link directly to a table, but which still contains command buttons, labels, or other controls that you need to operate your application.

This article focuses primarily on bound forms. You can use bound forms to control access to data, such as which fields or rows of data are displayed. For example, certain users might need to see only several fields in a table with many fields. Providing those users with a form that contains only those fields makes it easier for them to use the database. You can also add command buttons and other features to a form to automate frequently performed actions.

Think of bound forms as windows through which people see and reach your database. An effective form speeds the use of your database, because people don't have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient, and it can also help prevent incorrect data from being entered.


What is a Microsoft Access Query:

As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table. Queries were designed to combat this problem. With a query you can apply a filter to the table's data, so that you only get the information that you want. 

The tricky part of queries is that you must understand how to construct one before you can actually use them. This lesson will guide you through the basics of making a couple very simple Access queries.

 

 What is a Microsoft Access Report:

Having all your data stored in Access is great for maintaining a database, but it isn't the best when you want to share the data or view it away from a computer. The solution to this problem is to create an Access report that will let you design a ready-to-print document of your desired database information.


 

I need to gather information from many people – [Access] will facilitate data sharing or data entry. Because more people are more familiar with Excel they will go to their Excel file, enter the data, and when I open my database it's updated. [This article] showed me how to do that. GREAT!
~ Access customer, 

 

You can bring the data from an Excel workbook into Access 2010 in many ways. You can copy data from an open worksheet and paste it into an Access datasheet, import a worksheet into a new or existing table, or link to a worksheet from an Access database.

This topic explains in detail how to import or link to Excel data from Access.

 NOTES 

  • You cannot import data while running a Web database in a Web browser. If you want to import data into a Web database, you must first open the Web database by using Access.
  • Linked tables are not compatible with Web databases; to use external data in a Web database, you must import the data instead of linking to it. 

Understand importing data from Excel

If your goal is to store some or all of your data from one or more Excel worksheets in Access, you should import the contents of the worksheet into a new or existing Access database. When you import data, Access creates a copy of the data in a new or existing table without altering the source Excel worksheet.

COMMON SCENARIOS FOR IMPORTING EXCEL DATA INTO ACCESS


  • You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.
  • Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.
  • You use Access to manage your data, but the weekly reports you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

IF THIS IS THE FIRST TIME YOU ARE IMPORTING DATA FROM EXCEL


  • There is no way to save an Excel workbook as an Access database. Excel does not provide functionality to create an Access database from Excel data.
  • When you open an Excel workbook in Access (in the File Open dialog box, change the Files of Type list box to Microsoft Office Excel Files and select the file you want), Access creates a link to the workbook instead of importing its data. Linking to a workbook is fundamentally different from importing a worksheet into a database. For more information about linking, see the section Link to data in Excel, later in this article.


Import data from Excel

The steps in this section explain how to prepare for and run an import operation, and how to save the import settings as a specification for later reuse. As you proceed, remember that you can import data from only one worksheet at a time. You cannot import all the data from a whole workbook at the same time.

Prepare the worksheet

  1. Locate the source file and select the worksheet that contains the data that you want to import to Access. If you want to import only a portion of a worksheet, you can define a named range that includes only the cells that you want to import.

Define a named range (optional)

  1. Switch to Excel and open the worksheet that has data that you want to import.
  2. Select the range of cells that contain the data that you want to import.
  3. Right-click within the selected range and then click Name a Range orDefine Name.
  4. In the New Name dialog box, specify a name for the range in the Namebox and click OK.

Remember that you can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

  1. Review the source data and take action as described in this table.
ELEMENTDESCRIPTION
Number of columns The number of source columns that you want to import cannot exceed 255, because Access does not support more than 255 fields in a table.
Skipping columns and rows

It is a good practice to include only the rows and columns that you want to import in the source worksheet or named range.

Rows    You cannot filter or skip rows during the import operation.

Columns    You cannot skip columns during the operation if you choose to add the data to an existing table.

Tabular format Ensure that the cells are in tabular format. If the worksheet or named range includes merged cells, the contents of the cell are placed in the field that corresponds to the leftmost column, and the other fields are left blank.
Blank columns, rows, and cells Delete all unnecessary blank columns and blank rows in the worksheet or range. If the worksheet or range contains blank cells, try to add the missing data. If you are planning to append the records to an existing table, ensure that the corresponding field in the table accepts null (missing or unknown) values. A field will accept null values if its Required field property is set toNo and its ValidationRule property setting doesn't prevent null values.
Error values If one or more cells in the worksheet or range contain error values, such as#NUM and #DIV, correct them before you start the import operation. If a source worksheet or range contains error values, Access places a null value in the corresponding fields in the table. 
Data type

To avoid errors during importing, ensure that each source column contains the same type of data in every row. Access scans the first eight source rows to determine the data type of the fields in the table. We highly recommend that you ensure that the first eight source rows do not mix values of different data types in any of the columns. Otherwise, Access might not assign the correct data type to the column.

Also, it is a good practice to format each source column in Excel and assign a specific data format to each column before you start the import operation. Formatting is highly recommended if a column includes values of different data types. For example, the FlightNo column in a worksheet might contain numeric and text values, such as 871, AA90, and 171. To avoid missing or incorrect values, do the following:

  1. Right-click the column header and then click Format Cells.
  2. On the Number tab, under Category, select a format. For the FlightNo column, you would probably choose Text.
  3. Click OK.

If the source columns are formatted, but still contain mixed values in the rows following the eighth row, the import operation might still skip values or convert values incorrectly. For troubleshooting information, see the sectionTroubleshoot missing or incorrect values.

First row

If the first row in the worksheet or named range contains the names of the columns, you can specify that Access treat the data in the first row as field names during the import operation. If your source worksheet or range doesn't include the names, it is a good idea to add them to the source before you start the import operation.

 NOTE   If you plan to append the data to an existing table, ensure that the name of each column exactly matches the name of the corresponding field. If the name of a column is different from the name of the corresponding field in the table, the import operation will fail. To see the names of the fields, open the table in Design view in Access.

  1. Close the source workbook, if it is open. Keeping the source file open might result in data conversion errors during the import operation.

Prepare the destination database

  1. Open the Access database where the imported data will be stored. Ensure that the database is not read-only, and that you have permissions to make changes to the database.

-or-

If you don't want to store the data in any of your existing databases, create a blank database. To do so:

Click the File tab, click New, and then click Blank Database or Blank Web Database.

  1. Before you start the import operation, decide whether you want to store the data in a new or existing table.

Create a new table    If you choose to store the data in a new table, Access creates a table and adds the imported data to this table. If a table with the specified name already exists, Access overwrites the contents of the table with the imported data.

Append to an existing table    If you choose to add the data to an existing table, the rows in the Excel worksheet are appended to the specified table.

Remember that most failures during append operations occur because the source data does not match the structure and field settings of the destination table. To avoid this, open the destination table in Design view and review the following:


  • First row    If the first row of the source worksheet or named range does not contain column headings, ensure that the position and data type of each column in the source worksheet matches those of the corresponding field in the table. If the first row contains column headings, the order of columns and fields do not need to match, but the name and data type of each column must exactly match those of its corresponding field.
  • Missing or extra fields    If one or more fields in the source worksheet do not exist in the destination table, add them before you start the import operation. However, if the table contains fields that don't exist in the source, you do not need to delete those fields from the table if they accept null values.

 TIP   A field will accept null values if its Required property is set to No and itsValidationRule property setting doesn't prevent null values.


  • Primary key    If the table contains a primary key field, the source worksheet or range must have a column that contains values that are compatible with the primary key field, and the imported key values must be unique. If an imported record contains a primary key value that already exists in the destination table, the import operation displays an error message.
  • Indexed fields    If the Indexed property of a field in the table is set toYes (No Duplicates), the corresponding column in the source worksheet or range must contain unique values.

Go to the next steps to run the import operation.

Start the import operation

  1. On the External Data tab, in the Import & Link group, click Excel.

 NOTE   The External Data tab is not available unless a database is open.

  1. In the Get External Data - Excel Spreadsheet dialog box, in the File namebox, specify the name of the Excel file that contains the data that you want to import.

-or-

Click Browse and use the File Open dialog box to locate the file that you want to import.

  1. Specify how you want to store the imported data.

To store the data in a new table, select Import the source data into a new table in the current database. You will be prompted to name this table later.

To append the data to an existing table, select Append a copy of the records to the table and select a table from the drop-down list. This option is not available if the database has no tables.

  1. Click OK.

The Import Spreadsheet Wizard starts, and leads you through the import process. Go to the next set of steps.

Use the Import Spreadsheet wizard

  1. On the first page of the wizard, select the worksheet that contains the data that you want to import, and then click Next.
  2. On the second page of the wizard, click either Show Worksheets or Show Named Ranges, select either the worksheet or the named range that you want to import, and then click Next.
  3. If the first row of the source worksheet or range contains the field names, select First Row Contains Column Headings and click Next.

If you are importing the data into a new table, Access uses these column headings to name the fields in the table. You can change these names either during or after the import operation. If you are appending the data to an existing table, ensure that the column headings in the source worksheet exactly match the names of the fields in the destination table.

If you are appending data to an existing table, skip directly to step 6. If you are adding the data to a new table, follow the remaining steps.

  1. The wizard prompts you to review the field properties. Click a column in the lower half of the page to display the corresponding field's properties. Optionally, do any of the following:

    • Review and change, if you want, the name and data type of the destination field.

Access reviews the first eight rows in each column to suggest the data type for the corresponding field. If the column in the worksheet contains different types of values, such as text and numbers, in the first eight rows of a column, the wizard suggests a data type that is compatible with all the values in the column — most often, the text data type. Although you can choose a different data type, remember that values that are incompatible with the data type that you choose will be either ignored or converted incorrectly during the import process. For more information about how to correct missing or incorrect values, see the section Troubleshoot missing or incorrect values, later in this article.

  • To create an index on the field, set Indexed to Yes.
  • To completely skip a source column, select the Do not import field (Skip) check box.

Click Next after you finish selecting options.

  1. In the next screen, specify a primary key for the table. If you select Let Access add primary key, Access adds an AutoNumber field as the first field in the destination table, and automatically populates it with unique ID values, starting with 1. Click Next.
  2. In the final wizard screen, specify a name for the destination table. In theImport to Table box, type a name for the table. If the table already exists, Access displays a prompt that asks whether you want to overwrite the existing contents of the table. Click Yes to continue or No to specify a different name for the destination table, and then click Finish to import the data.

If Access was able to import some or all the data, the wizard displays a page that shows you the status of the import operation. In addition, you can save the details of the operation for future use as a specification. Conversely, if the operation completely failed, Access displays the message An error occurred trying to import file.

  1. Click Yes to save the details of the operation for future use. Saving the details helps you repeat the operation at a later time without having to step through the wizard each time.

Save the import settings as a specification

  1. In the Save as box, type a name for the import specification. Optionally, type a description in the Description box.
  2. If you want to perform the operation at fixed intervals, such as weekly or monthly, select the Create Outlook Task check box. This creates an Outlook task that lets you run the specification at a later date.
  3. Click Save Import.

If you did not select the Create Outlook Task check box in step 2, you are finished with this procedure. If you did select the check box, Access opens an Outlook Task. Continue with steps 4 and 5.

 NOTE   If Outlook is not installed, Access displays an error message. If Outlook is not configured properly, the Outlook Startup wizard starts. Follow the instructions in the wizard to configure Outlook.

  1. In Outlook, review and modify the task settings, such as Due date andReminder.

To make the task recur, click Recurrence, and set the options that you want.

  1. Click Save and Close.

Run a saved task

  1. In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
  2. On the Task tab, in the Microsoft Access group, click Run Import, and then click OK to confirm.
  3. Open the destination table in Datasheet view. Compare the data in the table with the source worksheet, and ensure that the data appears to be correct.

If you find that some data is either missing or incorrect, see the next section for troubleshooting information.

Run a saved import

  1. On the External Data tab, in the Import & Link group, click Saved Imports.
  2. In the Manage Data Tasks dialog box, click the saved import that you want to run, and then click Run.

Troubleshoot missing or incorrect values

If you receive the message An error occurred trying to import file, the import operation completely failed. Conversely, if the import operation displays a dialog box that prompts you to save the details of the operation, the operation was able to import all or some of the data. The status message also mentions the name of the error log table that contains the description of any errors that occurred during the import operation.

 IMPORTANT   Even if the status message indicates a completely successful operation, you should review the contents and structure of the table to ensure that everything looks correct before you start using the table.


  • Open the destination table in Datasheet view to see whether all data was added to the table.
  • Open the table in Design view to review the data type and other property settings of the fields.

The following table describes the steps that you can take to correct missing or incorrect values.

 TIP   While you are troubleshooting the results, if you find just a few missing values, you can add them to the table manually. Conversely, if you find that entire columns or a large number of values are either missing or were not imported properly, you should correct the problem in the source file. After you have corrected all known problems, repeat the import operation.

ISSUERESOLUTION
Graphical elements Graphical elements, such as logos, charts, and pictures cannot be imported. Manually add them to the database after completing the import operation.
Calculated values The results of a calculated column or cells are imported, but not the underlying formula. During the import operation, you can specify a data type that is compatible with the formula results, such as Number.
TRUE or FALSE and -1 or 0 values If the source worksheet or range includes a column that contains only TRUE or FALSE values, Access creates a Yes/No field for the column and inserts -1 or 0 values in the field. However, if the source worksheet or range includes a column that contains only -1 or 0 values, Access, by default, creates a numeric field for the column. You can change the data type of the field to Yes/No during the import operation to avoid this problem.
Multivalued fields When you import data to a new table or append data to an existing table, Access does not enable support for multiple values in a field, even if the source column contains a list of values separated by semicolon (;). The list of values is treated as a single value and is placed in a text field.
Truncated data If data appears truncated in a column in the Access table, try increasing the width of the column in Datasheet view. If that doesn't resolve the issue, the data in a numeric column in Excel is too large for the field size of the destination field in Access. For example, the destination field might have theFieldSize property set to Byte in an Access database but the source data contains a value greater than 255. Correct the values in the source file and try importing again.
Display format

You might have to set the Format property of certain fields in design view to ensure that the values are displayed correctly in Datasheet view. For example:

  • A Yes/No field displays -1 and 0 in Datasheet view after the import operation is completed. To fix this, after the import operation is complete, set the field's Format property to Yes/No to display check boxes instead.
  • Long and medium dates might appear as short dates in Access. To fix this, open the destination table in Design view in Access and set the date field's Format property to Long Date or Medium Date.

 NOTE   If the source worksheet contains rich text formatting such as bold, underline, or italics, the text is imported, but the formatting is lost.

Duplicate values (key violation error) Records that you are importing might contain duplicate values that cannot be stored in the primary key field of the destination table or in a field that has the Indexed property set to Yes (No Duplicates). Eliminate the duplicate values in the source file and try importing again.
Date values off by 4 years

The date fields that are imported from an Excel worksheet might be off by four years. Excel for Windows uses the 1900 Date System (in which serial numbers range from 1 to 65,380), which correspond to the dates January 1, 1900 through December 31, 2078. However, Excel for the Macintosh uses the 1904 Date System (in which serial numbers range from 0 to 63,918), which correspond to the dates January 1, 1904 through December 31, 2078.

Before you import the data, change the date system for the Excel workbook or, after appending the data, perform an update query that uses the expression [date field name] + 1462 to correct the dates.

Null values

You might see an error message at the end of the import operation about data that was deleted or lost during the operation, or when you open the table in Datasheet view, you might see that some field values are blank. If the source columns in Excel are not formatted, or the first eight source rows contain values of different data types, open the source worksheet and do the following:

  • Format the source columns.
  • Move the rows so that the first eight rows in each column do not contain values of different data types.
  • During the import operation, select the appropriate data type for each field. If the data type is incorrect, you might see null values or incorrect values in the entire column after the import operation has completed.

The preceding steps can help minimize the appearance of null values. The following table lists cases in which you will still see null values:

THE VALUES THAT ARE MISSING ARE OF TYPE...WHEN IMPORTING TO...AND THE DESTINATION FIELD TYPE IS...TO RESOLVE...
Text A new table Date Replace all text values with date values and then try importing again.
Text An existing table Numeric or Date Replace all text values with values that match the data type of the destination field and then try importing again.
Date values replaced by numeric values

You will see seemingly random five-digit numbers instead of the actual date values in the following situations:


  • The source column in the worksheet contains only numeric values in the first eight rows, but contains some date values in the subsequent rows. These date values will be converted incorrectly.
  • The source column contains date values in some of the first eight rows, and you attempted to import it into a numeric field. These date values will be converted incorrectly.

To avoid this, replace the date values with numeric values in the source column and try importing again.

Sometimes, if a column that contains mostly date values also contains several text values, all the date values might appear as seemingly random five-digit numbers. To avoid this, replace the text values with date values and then try importing again.

Numeric values replaced by date values

You will see seemingly random date values instead of the actual numeric values in the following situations:


  • The source column contains only date values in the first eight rows, but contains some numeric values in the subsequent rows. These numeric values will be converted incorrectly.
  • The source column contains numeric values in some of the first eight rows, and you attempted to import it into a date field. These numeric values will be converted incorrectly.

To avoid this, replace the numeric values with date values in the source column and then try importing again.

In addition, you might want to review the error log table (mentioned in the last page of the wizard) in Datasheet view. The table has three fields — Error, Field, and Row. Each row contains information about a specific error, and the contents of the Error field should help you troubleshoot the problem.

ERROR STRINGS AND TROUBLESHOOTING HINTS
ERRORDESCRIPTION
Field Truncation A value in the file is too large for the FieldSize property setting for this field.
Type Conversion Failure A value in the worksheet is the wrong data type for this field. The value might be missing or might appear incorrect in the destination field. See the previous table for more information how to troubleshoot this issue.
Key Violation This record's primary key value is a duplicate — it already exists in the table.
Validation Rule Failure A value breaks the rule set by using the ValidationRule property for this field or for the table.
Null in Required Field A null value isn't allowed in this field because the Required property for the field is set to Yes.
Null value in AutoNumber field The data that you are importing contains a Null value that you attempted to append to an AutoNumber field.
Unparsable Record

A text value contains the text delimiter character (usually double quotation marks). Whenever a value contains the delimiter character, the character must be repeated twice in the text file; for example:

4 1/2"" diameter

 

Link to data in Excel

By linking an Access database to data in another program, you can use the querying and reporting tools that Access provides without having to maintain a copy of the Excel data in your database.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel appear in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

COMMON SCENARIOS FOR LINKING TO AN EXCEL WORKSHEET FROM WITHIN ACCESS

Typically, you link to an Excel worksheet (instead of importing) for the following reasons:

  • You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.
  • Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but want to be able to work with it in Access.

IF THIS IS THE FIRST TIME YOU ARE LINKING TO AN EXCEL WORKSHEET

  • You cannot create a link to an Access database from within Excel.
  • When you link to an Excel file, Access creates a new table, often referred to as a linked table. The table shows the data in the source worksheet or named range, but it doesn't actually store the data in the database.
  • You cannot link Excel data to an existing table in the database. This means that you cannot append data to an existing table by performing a linking operation.
  • A database can contain multiple linked tables.
  • Any changes that you make to the data in Excel are automatically reflected in the linked table. However, the contents and structure of a linked table in Access are read-only.
  • When you open an Excel workbook in Access (in the File Open dialog box, change the Files of Type list box to Microsoft Excel, and select the file you want), Access creates a blank database and automatically starts the Link Spreadsheet Wizard.

Prepare the Excel data

  1. Locate the Excel file and the worksheet or range that has the data you want to link to. If you don't want to link to the entire worksheet, consider defining a named range that includes only the cells you want to link to.
CREATE A NAMED RANGE IN EXCEL (OPTIONAL – USEFUL IF YOU ONLY WANT TO LINK TO SOME OF THE WORKSHEET DATA)
  1. Switch to Excel and display the worksheet in which you want to define a named range.
  2. Select the range of cells that contain the data you want to link to.
  3. Right-click within the selected range and click Name a Range or Define Name.
  4. In the New Name dialog box, specify a name for the range in the Namebox and then click OK.

Note that you can link to only one worksheet or range at a time during a link operation. To link to data in multiple places in a workbook, repeat the link operation for each worksheet or range.

  1. Review the source data, and take action as described in the following table:
ELEMENTDESCRIPTION
Tabular format Ensure that the cells are in tabular format. If the range includes merged cells, the contents of the cell are placed in the field that corresponds to the leftmost column and the other fields are left blank.
Skipping columns and rows You cannot skip source columns and rows during the linking operation. However, you can hide fields and filter records by opening the linked table in Datasheet view after you have imported them into Access.
Number of columns The number of source columns cannot exceed 255, because Access does not support more than 255 fields in a table.
Blank columns, rows, and cells Delete all unnecessary blank columns and blank rows in the Excel worksheet or range. If there are blank cells, try to add the missing data.
Error values If one or more cells in a worksheet or range contain error values, correct them before you start the import operation. Note that if a source worksheet or range contains error values, Access inserts a null value in the corresponding fields in the table.
Data type

You cannot change the data type or size of the fields in the linked table. Before you start the linking operation, you must verify that each column contains data of a specific type.

We highly recommend that you format a column if it includes values of different data types. For example, the FlightNo column in a worksheet might contain numeric and text values, such as 871, AA90, and 171. To avoid missing or incorrect values, do the following:

  1. Right-click the column and then click Format Cells.
  2. On the Number tab, under Category, select a format.
  3. Click OK.
First row If the first row in the worksheet or named range contains the names of the columns, you can specify that Access should treat the data in the first row as field names during the link operation. If there are no column names in the worksheet, or if a specific column name violates the field naming rules in Access, Access assigns a valid name to each corresponding field.
  1. Close the source file, if it is open.

Prepare the destination database

  1. Open the database in which you want to create the link. Ensure that the database is not read-only and that you have the necessary permissions to make changes to it.
  2. If you don't want to store the link in any of your existing databases, create a blank database: Click the File tab, click New, and then click Blank Database or Blank Web Database.

You are now ready to start the linking operation.

Create the link

  1. On the External Data tab, in the Import & Link group, click Excel.

 NOTE   The External Data tab is not available unless a database is open.

  1. In the Get External Data - Excel Spreadsheet dialog box, in the File namebox, specify the name of the Excel source file.
  2. Select Link to the data source by creating a linked table, and then clickOK.

The Link Spreadsheet Wizard starts and guides you through the linking process.

  1. On the first page of the wizard, select a worksheet or a named range and click Next.
  2. If the first row of the source worksheet or range contains the field names, select First row contains column headings. Access uses these column headings to name the fields in the table. If a column name includes certain special characters, it cannot be used as a field name in Access. In such cases, an error message is displayed that tells you that Access will assign a valid name for the field. Click OK to continue.
  3. On the final page of the wizard, specify a name for the linked table and then click Finish. If the table with the name you specify already exists, you are asked if you want to overwrite the existing table or query. Click Yes if you want to overwrite the table or query, or click No to specify a different name.

Access tries to create the linked table. If the operation succeeds, Access displays the Finished linking table message. Open the linked table and review the fields and data to ensure that you see the correct data in all the fields.

If you see error values or incorrect data, you must troubleshoot the source data. For more information about how to troubleshoot error values or incorrect values, see the next section.

 

Troubleshoot #Num! and other incorrect values in a linked table

Even if you receive the message Finished linking table, you should open the table in Datasheet view to ensure that the rows and columns show the correct data.

If you see errors or incorrect data anywhere in the table, take correct action as described in the following table, and then try linking again. Remember that you cannot add the values directly to the linked table, because the table is read-only.

ISSUERESOLUTION
Graphical elements Graphical elements in an Excel worksheet, such as logos, charts, and pictures, cannot be linked to in Access.
Display format You might have to set the Format property of certain fields in Design view to ensure that the values are displayed correctly in Datasheet view.
Calculated values The results of a calculated column or cells are displayed in the corresponding field, but you cannot view the formula (or expression) in Access.
Truncated text values Increase the width of the column in Datasheet view. If you still don't see the entire value, it could be because the value is longer than 255 characters. Access can only link to the first 255 characters, so you should import the data instead of linking to it.
Numeric field overflow error message The linked table might appear to be correct, but later, when you run a query against the table, you might see a Numeric Field Overflow error message. This can happen because of a conflict between the data type of a field in the linked table and the type of data that is stored in that field.
TRUE or FALSE and -1 or 0 values If the source worksheet or range includes a column that contains only TRUE or FALSE values, Access creates a Yes/No field for the column in the linked table. However, if the source worksheet or range includes a column that contains only -1 or 0 values, Access, by default, creates a numeric field for the column, and you will not be able to change the data type of the corresponding field in the table. If you want a Yes/No field in the linked table, ensure that the source column includes TRUE and FALSE values.
Multivalued fields Access does not enable support for multiple values in a field, even if the source column contains a list of values separated by semicolon (;). The list of values will be treated as a single value, and placed in a text field.
#Num!

Access displays the #Num! error value instead of the actual data in a field in the following situations:

  • If a source column contains a few numeric or date values in a column that contains mostly text values, the numeric and date values are not imported.
  • If a source column contains a few text values in a column that contains mostly numeric values, the text values are not imported.
  • If a source column contains a few text values in a column that contains mostly date values, the text values are not imported.

Do the following to minimize the instances of null values in the table:

  1. Ensure that the source column does not contain values of different data types.
  2. Format the columns in the Excel worksheet.
  3. During the linking operation, select the right data type for each field. If the data type is incorrect, the resulting column might contain only #Num! values for all the rows of data.
Numeric values instead of date values If you see a seemingly random five-digit number in a field, check to see if the source column contains mostly numeric values but also includes a few date values. Date values that appear in numeric columns get incorrectly converted to a number. Replace the date values with numeric values and then try linking again.
Date values instead of numeric values If you see a seemingly random date value in a field, check to see if the source column contains mostly date values but also includes a few numeric values. Numeric values that appear in date columns get incorrectly converted to a date. Replace the numeric values with date values and then try linking again.

 

 

This article shows you what kinds of data you can import and export by using Access, and shows you the basic steps to get started with an import or export operation.

One of the most useful features of Access is its ability to interface with data from many other programs. In fact, it’s difficult to summarize in a single article all the ways in which you can move data into and out of Access. For example, here are just a few ways in which you might use the data-exchange features of Access:

  • To combine data that was created in other programs.
  • To transfer data between two other programs.
  • To accumulate and store data over the long term, occasionally exporting data to other programs such as Excel for analysis.


Overview of external data operations in Access

 

In many programs, you use the Save As command to save a document in another format, so that you can open it in another program. In Access, however, the Save As command is not used in the same way. You can save Access objects as other Access objects, and you can save Access databases as earlier versions of Access databases, but you cannot save an Access database as, say, a spreadsheet file. Likewise, you cannot save a spreadsheet file as an Access file (.accdb). Instead, you use the commands on the External Data tab in Access to import or export data between other file formats.

 NOTE    You can also write macros or Visual Basic for Applications (VBA) code to automate the import and export operations that are available on the External Data tab.


Types of data that Access can import, link to, or export

 

A quick way to learn about the data formats that Access can import or export is to open a database and then explore the External Data tab on the ribbon.

Access Ribbon Image

Callout 1 The Import & Link group displays icons for the data formats that Access can import from or link to.
Callout 2 The Export group displays icons for all the formats that Access can export data to.
Callout 3 In each group, you can click More to see more formats that Access can work with.

If you don’t see the exact program or data type that you need, chances are your data can be exported by the other program into a format that Access understands. For example, most programs can export columnar data as delimited text, which is then easily imported into Access.

The following table shows which formats can be imported into, linked to, or exported out of Access:

PROGRAM OR FORMATIMPORT ALLOWED?LINKING ALLOWED?EXPORTING ALLOWED?
Microsoft Office Excel Yes Yes Yes
Microsoft Office Access Yes Yes Yes
ODBC Databases (For example, SQL Server) Yes Yes Yes
Text files (delimited or fixed-width) Yes Yes Yes
XML Files Yes No Yes
PDF or XPS files No No Yes
E-mail (file attachments) No No Yes
Microsoft Office Word No, but you can save a Word file as a text file and then import the text file. No, but you can save a Word file as a text file and then link to the text file. Yes (you can export as Word Merge or as Rich Text)
SharePoint List Yes Yes Yes
Data Services (see note) No Yes No
HTML Documents Yes Yes Yes
Outlook Folders Yes Yes No, but you can export as a text file, and then import the text file into Outlook.
dBase files Yes Yes Yes

 NOTE    To enable the Data Services button, Microsoft .NET 3.5 or later must be installed.

 

 

Import or link to data in another format

 

The general process for importing or linking data is as follows:

  1. Open the database that you want to import or link data into.
  2. On the External Data tab, click the type of data that you want to import or link to. For example, if your source data is in a Microsoft Excel workbook, clickExcel.

    Access Ribbon Image 
  3. In most cases, Access starts the Get External Data wizard. In the wizard, you may be asked for some or all of the information in the following list:

  • Specify the source of the data (its location on disk).
  • Choose whether to import or link to the data.
  • If importing, choose whether to append the data to an existing table, or to create a new table.
  • Specify exactly which data in the document you want to import or link.
  • Indicate whether the first row contains column headings, or whether it should be treated as data.
  • Specify the data type of each column.
  • Choose whether to import the structure only, or the structure and the data together.
  • If importing, specify whether you want Access to add a new primary key to the new table, or use an existing key.

  • Specify a name for the new table. 

     NOTE    It’s a good idea to look at your source data ahead of time so that you know the correct answers to these questions when the wizard asks for them.

  1. On the last page of the wizard, Access usually asks you if you want to save the details of the import or link operation. If you think you’ll need to perform the same operation on a recurring basis, select the Save import stepscheck box, fill in the information, and then click Close. Then, you can clickSaved Imports on the External Data tab to re-run the operation.

After you have completed the wizard, Access notifies you of any problems that might have occurred during the import process. In some cases, Access might create a new table called ImportErrors, which contains any data that it was unable to import successfully. You can examine the data in this table to try to find out why the data did not import correctly.

For more information about importing or linking to data in a specific format, search the Access Help system for articles and videos that cover that format.

 

 

Export data to another format

 

The general process for exporting data from Access is as follows:

  1. Open the database that you want to export data from.
  2. In the Navigation Pane, select the object that you want to export the data from. You can export data from table, query, form, and report objects, although not all export options are available for all object types.
  3. On the External Data tab, click the type of data that you want to export to. For example, to export data in a format that can be opened by Microsoft Excel, click Excel.

    Access Ribbon image 

    In most cases, Access starts the Export wizard. In the wizard, you may be asked for information such as the destination file name and format, whether to include formatting and layout, which records to export, and so on.
  1. On the last page of the wizard, Access usually asks you if you want to save the details of the export operation. If you think you will need to perform the same operation on a recurring basis, select the Save export steps check box, fill in the information, and then click Close. Then, you can click Saved Exports on the External Data tab to re-run the operation.

For more information about exporting to a specific format, search the Access Help system for articles and videos that cover that format.

 

Source:  http://office.microsoft.com/en-us/access-help/introduction-to-importing-and-exporting-data-HA101790599.aspx

Request Follow Up

Or Give Us a Call

contact us

609.423.6979