Microsoft Access

Use the following functions to access data directly from a table in a Microsoft Access database:

• File | Link | Microsoft Access (See: Create Microsoft Access Link)
• File | Import | Microsoft Access (See below)

The difference between the Import Microsoft Access function and the Link Microsoft Access function, is that data is actually transferred from the external database to the application. 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:

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 or query you want to link to, and then click OK. Use the radio buttons to control which tables will appear. Choose: All tables, Compound, User or Single.

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.

Specify how the target file will be structured. There are three 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 target file.

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.

Use current structure

If this option is selected, the function will use the current structure of the target file. This option is disabled if the target file does not exist (i.e. you are creating a new file).

• In the case of the Determine structure and 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 for import can be mapped to fields in the target file which have the same Name , Width and Precision.
• 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 for import 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 the import. Click NO to abort the import process.

Some data types (such as LongVarChar, Memo, and Image) 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 and the default widths and precisions used during the Import process.

Auto open file for editing

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

The best workaround is to use the File | Import | ODBC and File | Link | ODBC functions when importing or linking to a Microsoft Access query that contains non-standard wildcard characters.