Locked lesson.

Upgrade

About this lesson

Import objects from Access and manage Trust Center settings.

Quick reference

Import objects from Access, Trust Center

Application Terminology

ASCII

ASCII stands for American Standard Code for Information Interchange, and is a character-encoding scheme where each letter, number, and special character is assigned a unique number.  Capital A is 65. ASCII is based on the English alphabet, uses 7 bits, and limited to 128 characters.  Extended ASCII can encode 256 characters.

BCDM File

A BCDM File is a business data connectivity metadata store file in XML format that contains definition information for connecting to a data service. Flat data structures are the only supported types – at this point, hierarchical data structures are not.

CSV File

A CSV file is stored in plain text and stands for Comma-Separated Values.  When Excel is installed, it is chosen to be the default application for opening CSV files.  If you have long numbers in a CSV file, they may be converted to floating point by Excel, rendering them useless if they are key fields for matching data.  If you open a CSV file with Excel, don't save it unless you ensure all the data is good.

Data Service

You can link to an existing data service, or create your own.  The connection will be read-only and you can Refresh to update the information.

DNS File

DNS stands for Data Name Source.  A DNS file is a text files specifying DRIVER, UID, PWD, DATABASE, and SERVER and can be used to connect to data in a variety of formats.  For information on how to use a DNS file, watch the following presentation starting from 0:35:11

Microsoft MVP Virtual Conference 2015 -- Access Web Apps, linking to SQL Azure from Access desktop, Excel workbook, and SSMS (53 minutes)

https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

Excel

The data for many Access databases comes from Excel, which is the Microsoft Office spreadsheet application.  Data can be import from, and exported to, Excel.

Export

Access can convert data to a variety of formats.  This is the Export group on the EXTERNAL DATA tab.

External Data

The External Data ribbon enables you to import and link to data stored in other applications.  You can also export data stored in Access to other formats.

HTML Document

HTML, Hypertext Markup Language, is the markup language for building web pages.  It is a text file that uses angle brackets to specify definitions.  HTML files can use CSS (Cascading Style Sheet) files to define formatting, and JavaScript for programming.

When importing information from an HTML file into Access, the HTML file must be structured with cross-origin resource sharing (CORS).

Import

Importing is bringing data and objects into your database from another file.  Access supports a variety of formats. 

Link

Share data stored in another file.  When you link, the data can be refreshed when it changes, giving you the ability to see the latest information.

ODBC

ODBC is Open DataBase Connectivity. Many database management systems have ODBC drivers that enable Access to get and share data. ODBC is an interface written in C and was developed by Microsoft in 1992.  Some ODBC-compliant databases, and applications that have bridges to ODBC include:

  • dBASE
  • DB2
  • FileMaker Pro
  • Microsoft Excel
  • Microsoft SQL Server
  • MySQL
  • Paradox
  • Oracle
  • Quicken
  • Visual FoxPro

For more information on ODBC, refer to:

ODBC Programmer's Reference
https://msdn.microsoft.com/en-us/library/ms714177%28v=vs.85%29.aspx

ODBC Database

When you import from an ODBC database, you can specify a DNS file on the File Data Source tab

… or specify each of the choices using the New… command button on the Machine Data Source tab, then picking the source, clicking OK, then filling out necessary information in the wizard.

Outlook Folder

Access can import and link to mailboxes and address books in Outlook.

Share Data

An important strength of Access is how easy it is to import and share data with other applications … after all, this is how Access got its name!  Access is a landing pad for all types of information.  Look on the EXTERNAL DATA ribbon in the Import & Link group to bring data in, and in the EXPORT group to shuffle data out.

SharePoint

SharePoint is a web application platform.  SharePoint is needed to develop and run Access (Web) Apps and is included in Office 365 packages.

SharePoint List

A SharePoint List is a container for data that can be accessed using a web browser hosted on a SharePoint site. 

Text File

A text file is composed of ordinary characters and is readable by most applications.  Text files can be ASCII files and/or Unicode files.

Trust Center Settings

The Trust Center, accessible from File, Options, give you the ability to choose Trusted Locations, Trusted Document, Trusted Publishers, Add-ins, ActiveX Settings, Macro Settings, Message Bar, and Privacy Options.

Unicode

Unicode, like ASCII, is a way of encoding characters.  Unicode supports more characters than ASCII and takes more space to store.  UTF-8 is the official Unicode specification, uses one to four 8-bit bytes, and results in 1,112,064 combinations.  If it is desired to store Unicode characters, the Unicode Compression property of fields must be set to Yes.

Word

Word is the Microsoft Office word processing application.  Data can be exported to Word from Access.

XML File

XML is an eXtensible Markup File.  It is a text file (plain text or Unicode) that is structured using angle brackets <definition> to provide a schema.  Access cannot natively read hierarchical XML files.

Steps

Import from Access

  1. Click on the External Data ribbon
  2. Choose Access from the Import & Link group
  3. Browse to the database you want to import from
  4. When the wizard comes up, pick the objects that you want
  5. If you imported Modules, be sure to go to VBA and Debug, Compile

Trust Databases

  1. From the menu, choose File, Options, Trust Center
  2. Click the Trust Center Settings... button
  3. Choose Trusted Locations
  4. Check the box to "Allow Trusted Locations on my network" if you are on a LAN
  5. Click the Add new location... button
  6. Enter the drive you want to trust
  7. and check "Subfolders of this location are also trusted"
  8. Click OK on each menu to accept the changes.
Login to download
  • 00:04 This is Lesson 7 of Access 2013.
  • 00:09 Hi, I'm Crystal.
  • 00:11 An important strength of Access is how easy
  • 00:14 it is to share data with other applications
  • 00:17 Access can import and link.
  • 00:21 Importing is bringing things into
  • 00:23 your database from another file.
  • 00:26 Linking is sharing data stored in another file.
  • 00:29 The database we have been building
  • 00:31 in these lessons has 4 tables, 1 query,
  • 00:33 1 form, and 2 reports.
  • 00:36 The data is for Joe's handyman business
  • 00:39 and the services he provides.
  • 00:42 Joe has been busy developing his Access database and
  • 00:46 it is time for us to help him finish it.
  • 00:50 We will import what Joe has done into this database.
  • 00:54 Click on the External Data ribbon.
  • 00:56 Access supports a variety of formats.
  • 00:59 In the Import & Link group,
  • 01:01 you can get data from Excel, Access, an ODBC Database,
  • 01:05 a Text File, an XML File, a SharePoint List,
  • 01:09 a Data Service, an HTML document,
  • 01:13 or an Outlook Folder.
  • 01:15 ODBC stands for Open DataBase Connectivity.
  • 01:19 It was originally developed by Microsoft in 1992
  • 01:23 and translates data requests from one implementation
  • 01:27 of SQL to another.
  • 01:29 SQL, also pronounced "sequel" is Structured Query Language,
  • 01:34 and is a standard set of commands for managing data.
  • 01:37 You can think of ODBC like a multi-lingual person
  • 01:42 who speaks and translates English, French, German and more.
  • 01:46 Translators enable people who
  • 01:48 don't speak the same language to communicate.
  • 01:51 With ODBC, Access can connect
  • 01:53 to any data that is ODBC-compliant such as
  • 01:57 Microsoft SQL Server, Oracle, MySQL,
  • 02:01 DB2, FileMaker Pro, and many more.
  • 02:05 The use of ODBC is common.
  • 02:08 There are also bridges to ODBC
  • 02:10 opening even more doors to data.
  • 02:15 Because Access is RAD, Rapid Application Development,
  • 02:19 it can be used to link to data in a variety of formats
  • 02:22 for building quick to complex queries, forms and reports.
  • 02:28 The Export group of the External Data ribbon
  • 02:30 also has a variety of choices.
  • 02:34 To import what Joe has done,
  • 02:37 click on the Access icon in the Import & Link Group.
  • 02:41 When the wizard pops up, click Browse and Navigate to
  • 02:45 07_Import_FromJoe.accdb
  • 02:52 When you click OK in the wizard dialog box to select that file,
  • 02:56 Access shows you the objects that are in it.
  • 03:00 Across the top, you'll see a tab for Tables,
  • 03:03 Queries, Forms, Reports, Macros, and Modules.
  • 03:08 Click on each tab and click the Select All button.
  • 03:12 There are 4 tables, 5 queries, 4 forms,
  • 03:15 1 report, and 4 modules.
  • 03:20 When done selecting all the objects, click OK, and close.
  • 03:24 It is a good idea to put all the tables in your database
  • 03:28 on the Relationships diagram.
  • 03:30 We also have some relationships to create.
  • 03:33 Open the Relationships Diagram.
  • 03:36 Select the new tables in the Navigation Pane
  • 03:39 by clicking on one of them and Ctrl-clicking the others.
  • 03:43 Drag the selected tables to the Relationships Diagram.
  • 03:47 Stretch the fieldlists so everything shows.
  • 03:51 Rearrange the tables so that data flows as it must be entered.
  • 03:56 Usually this is with the "1" table on the left
  • 03:59 and the "many" table on the right.
  • 04:02 Drag a relationship from CustID in the Customers table
  • 04:06 to CustID in the Work table.
  • 04:09 When you let go of the mouse, a dialog box pops up.
  • 04:14 Check Enforce Referential Integrity.
  • 04:17 This ensures that no records can be in the Work table
  • 04:20 with an invalid customer ID (CustID).
  • 04:23 Click OK and now you see a new relationship line.
  • 04:27 Drag a relationship from ServiceID in the Services table
  • 04:31 to ServiceID in the WorkServices table,
  • 04:34 and Enforce Referential Integrity.
  • 04:38 Also drag a relationship from TimeTyID
  • 04:41 in the TimeTypes table
  • 04:43 to TimeTyID in the WorkServices table,
  • 04:46 and Enforce Referential Integrity.
  • 04:49 Rearrange the fieldlists.
  • 04:51 Since all of the tables have 4 tracking fields at the bottom,
  • 04:55 close up the fieldlists so they don't show.
  • 04:58 This will give us more space on the diagram.
  • 05:01 Save the diagram.
  • 05:03 Because VBA modules were imported,
  • 05:05 there are a couple more things to do.
  • 05:08 Press Alt-F11 to go to the Visual Basic Editor (VBA).
  • 05:13 From the menu, choose Debug, Compile.
  • 05:17 If there are no errors, nothing will appear to happen.
  • 05:20 This is good.
  • 05:21 Click on the Diskette icon to save the compiled code
  • 05:24 and close the Visual Basic Editor.
  • 05:27 Later, we will talk more about what is in here.
  • 05:30 Now that we have VBA in this application,
  • 05:33 we need to let Access know that it is trusted.
  • 05:37 When you open this database,
  • 05:39 you will get a Security Warning that content has been disabled.
  • 05:43 This means that the code we imported isn't going to work.
  • 05:47 You can click the Enable Content button
  • 05:49 each time you open the database,
  • 05:51 or you can change your Access defaults to trust the database.
  • 05:56 Click the FILE menu and choose Options
  • 05:59 In the left pane, choose Trust Center
  • 06:02 and then click on the Trust Center Settings... button
  • 06:06 Choose Trusted Locations from the left menu
  • 06:09 Check the box to Allow Trusted Locations
  • 06:12 on my network if you are on a LAN.
  • 06:15 Click the Add new location... button
  • 06:18 Enter the drive you want to trust
  • 06:20 and check "Subfolders of this location are also trusted"
  • 06:25 Click OK on each menu to accept the changes.
  • 06:28 One of the forms Joe has is to enter information for Invoices.
  • 06:33 This is a fairly simple form.
  • 06:35 The form that Joe uses to enter his Work is more complex
  • 06:40 Double-click on frm_Work in the Navigation Pane to look at it.
  • 06:47 There are some pretty cool things on this form
  • 06:49 that we will explore later.
  • 06:51 There are also some things that don't quite work right,
  • 06:54 which we will help Joe fix.
  • 06:56 In the next lesson, we will continue with importing
  • 06:58 and get data from Excel.

Lesson notes are only available for subscribers.

Gift this course