How to Link Data in Access

Link information to your Access report in a few steps.

Microsoft Access is a relational database management tool created by Microsoft to work seamlessly with the Microsoft Office suite. You can utilize it to create application software, and it's supported by Visual Basic for applications. Access tables render a variety of field types, indices and referential integrity. It also has a query interface to enter and construct data and create reports. Simultaneous users are also supported depending on the amount of data and users needed. Linking data in Access requires a few steps to configure.

Instructions

Link Text Data to Access

1. Open the file with the text data you'd like to link to Access. Delete any unneeded blank lines in the file, as well as any tabs, commas or line feeds. Check to ensure that the data in each line is the same type of data. Close the file.

2. Open the Access report to which you'll link your data. Decide if you wish to link your text to a new database or to an existing one. If you wish to link to a new database, create a new table. If you wish to link to an existing database, make sure the source file has information that will fit into the tables in Access without any blank spaces.

3. Click on "Import Group," then click on "Text File" under the "External Data" pane. Enter the name of the file within the "File Name" box. Click on either "Import the Source Data Into a New Table in the Current Database" or "Append a Copy of the Records to the Table." Hit "OK." If the file uses a delimiter to separate fields, click on the "Delimited" option. If it uses fixed-width fields, click on "Fixed Width."

4. Hit "Next." When going with the "Delimited" option, select the character that delimits the values. If it uses a text qualifier, under the "Text Qualifier," choose either a single "(')" or double "(")" quotation. Check "First Row Contains Field Names" if the first row of your source has field names. Click on "Next." Enter a primary key when prompted if adding your data to a new table, or select "Let Access Add Primary Key," then click "Next."

5. Click "Import to Table" if you're importing your information to a new table. Enter a name for the table. If a table already exists, click on "Yes" or "No" when prompted to specify if you wish to overwrite the table. Click "Finish" to proceed with linking.

Link Excel Data to Access

1. Open the Excel worksheet you wish to import to Access. Ensure that the cells are in tabular format. If you're linking to an existing Access worksheet, make sure that the formatting matches. Charts or graphical data will not by imported and hyperlinks will be imported as text files. Any rows beyond the first 25 that aren't formatted to match the Access data sheet will not be imported.

2. Open your Access database. Click on the "File" menu, then click "Get External Data" and hit "Import." Under the "Files of Type" dialog box, click "Microsoft Excel."

3. Hit the arrow to the right of the "Look In" box to locate the file you wish to link. Double-click on your Excel file. Enter information when prompted by the "Import" wizard to complete the link.

4. Review any error logs. Several issues can arise with improper formatting; you may have to review your Excel data source to ensure it's formatted to match the tables in Access.

References

• Microsoft Office: Import or Link to Data in a Text File
• Microsoft Office: Import, Export and Link Data Between Access and Excel