Microsoft Access

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

To establish a read-only link to a table in an external Microsoft Access database.

  • On the File tab, in the Data File group, select Link | Microsoft Access Link.

The Import/Link dialog is the same for both functions. The difference between the Import Microsoft Access function and the Microsoft Access Link function, however, 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.

Import Microsoft Access File

The following options are provided in the Import Microsoft Access form:

Source

Select Database

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

If the chosen database file is in a sub-folder of the project folder, the relative (rather than the full) path is shown.

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 target file. The imported data will be saved in this file. If the file exists, you will be asked if you want to overwrite it.

Type

Accept the default file type or select a file type.

Structure

Specify how the target file will be structured. There are three options:

  1. 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 target file.
  2. Use database structure. If this option is selected, the function will base the structure of the target file on the structure of the source table.
  3. Use current structure. If this option is selected, the function will use the current structure of the target file.

Notes:

  • In the case of the Determine structure and the Use database structure options, if the target file exists (i.e. you are not creating a new file) a check is made to ensure that all of the fields selected can be mapped to fields in the target file which have the same Name , Width and Precision.
  • The Use current structure option is disabled if the target file does not exist (i.e. you are creating a new file).
  • In the case of the Use current structure option, a check is made to ensure that all of the fields selected for import can be mapped to fields in the target file with the same Name.
  • If all of the fields selected can be mapped to fields in the target file, you will be prompted whether to append or overwrite data to the file.
  • If one or more fields cannot be mapped (the target file and the source table are incompatible) you will be prompted to overwrite the target file. Click YES to continue. Click NO to abort the import process.

Some data types, such as LongVarChar, 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.

Auto open

Select this option to open the file for editing in the File Editor once it has been imported .

 

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 import, select Preview and enter the number or records you want to see. Click Import and the Preview dialog will open. You can also change the structure of the table 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.

Import

Click Import to complete the process. A message will appear when the import is complete.

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).