Microsoft Access Link

On the File tab, in the Data File group: Select Link | Microsoft Access Link to establish a read-only link to a table in an external Microsoft Access database.

To import data from the external database to the application, where it can be edited and saved:

  • On the File tab, in the Import group: Click Microsoft Access to import data from a Microsoft Access database.

The Import/Link dialog is the same for both functions. The difference between the Link function and the Import Microsoft Access File function, is that data is actually transferred from the external database to the application in the case of an Import. Once the data is imported to a data file, it can be changed and saved.

Note: A message is displayed if an appropriate Access driver cannot be found. See: Choosing drivers for Microsoft Access compatibility

Once you have established the link, the data in the corresponding data file will only be refreshed when you specify the file as an input in a form and select Reload Microsoft Access Link from the right-click menu.

Alternatively, on the File tab, in the Data File group, select Microsoft Access Link Update to run a macro to refresh some or all of the Access links you have established.

A file containing data from a linked data source, will work in the same way as any other project file, with the exception that you cannot change the contents of that file.

When you establish the link to a table in an external Microsoft Access database you can:

  • Specify how the linked file will be structured.
  • Preview the table that will be linked and change the characteristics of the fields in the target file.
  • Control which fields will be included in the link.

The link is stored in the current project as a special type of file. The most common application of MDB Link is where data is contained in a central repository, such as a company DBMS. 

Select Database

Navigate to and select the Access database you want to link to and click OK. Microsoft Access 2007 (.accdb) files and earlier (.mdb) file formats are supported.

Select Table

In the Select Table dialog, select the table you want to link to, and then click OK. Use the radio buttons to control which tables will appear:

All Tables Displays all the tables/sheets in the selected database/file.
Compound Only the following tables will be displayed in the selection list: SYSVIEWAssay, SYSVIEWCollar; SYSVIEWGeoChem; SYSVIEWGeology and SYSVIEWSurvey. The SYSVIEW prefix will not appear.
User Displays only those tables that have a name starting with "USER". The USER prefix itself will not be displayed in the selection list.
Single

Displays all tables except those starting with any of the following words: Meta, USER or SYSVIEW.

Target file

Enter the name of the linked file. This is the file that will be used to store and access the linked data.

Either select the Select all check box or hold the Ctrl key down and click on the fields you want to include in the process.

Specify how the linked file will be structured. There are two options:

Determine structure. If this option is selected, the function will read the contents of the source table to determine the most appropriate width and format for each field in the linked file.

Use database structure. If this option is selected, the function will base the structure of the linked file on the structure of the source table.

  • If the linked file exists (i.e. you are not creating a new file) a check is made to ensure that all of the fields selected from the source table can be mapped to fields in the linked file which have the same Name , Width and Precision.
  • If one or more fields cannot be mapped (the linked file and the source table are incompatible) you will be prompted to overwrite the linked file using the structure of the source table. Click YES to continue the link process. Click NO to abort the process.

Some data types, such as Memo , Image and Binary data types, cannot be converted to a format that is compatible with Micromine file formats. Click on the following link for a list of Compatible Data Types used during the link process.

Auto open

  • Select this check box to open the file in the File Editor in read-only mode.

    • Fields to import

    Select the Select all check box to import all fields. Otherwise, select one or more fields from the list. To select multiple fields, use the Ctrl and Shift keys with the mouse.

    Preview

    To preview the results of the link, select Preview and enter the number or records you want to see. Click Import and the Preview will open. In this window you can see a sample of the records in the table you are linking to. You can also change the structure of the file that will be created.

    Number of records

    Enter the number of records that will be displayed in the Preview dialog. This feature can be important when you want to adjust the width of a field that contains data with a large range or precision. In such cases you will want to see a larger portion of the data to ensure that you do not truncate values. The default is 25 records.

    Click OK in the Preview window and a message confirming that the link has been established will appear.

    Create

    Click Create in the Microsoft Access Link dialog to complete the process.

    Microsoft Access database engine SQL is generally ANSI-89 Level 1 compliant. However, certain ANSI SQL features are not implemented in Microsoft® Access SQL.

    Queries which works fine in Microsoft Access, but which fails to return a result when used in this function, may contain non-standard wildcard characters. This is a driver-related problem.

    For more information on Microsoft Access SQL versus ANSI SQL, refer to: http://msdn2.microsoft.com/en-us/library/bb208890.aspx

    When importing or linking to a Microsoft Access query that contains non-standard wildcard characters, the best workaround is to use Import | ODBC (on the File tab, in the Import group) and Link | ODBC (on the File tab, in the Data File group).