Connection String Parameters for Saved Specifications

Access provides a second method of describing text files’ schema by using system tables MSysIMEXSpecs and MSysIMEXColumns to save the specifications. In the previous article, I covered how schema.ini can be used to describe a text file’s structure. The schema.ini involved an external text file implicitly referenced but with the system tables, we do not have any external dependencies. The structure is fairly different even though there are some overlaps.

MSysIMEXSpecs and MSysIMEXColumns system table

Instead of relying on an external schema.ini to be present in the same folder as the text file, it is possible to provide specifications for a text file in any path by referencing the saved specification within the Access database. When you use the Import or Export wizard, you can save or load the specifications through the Advanced button.

Clicking the advanced button will then display the specification dialog. In addition to loading or saving the specifications, you can also customize the specifications using the dialog shown.

Whenever a specification is saved, it is written to both the MSysIMEXSpecs and MSysIMEXColumns system tables. The tables do not exists in a new Access files and will be created only at the first time. Furthermore, you can edit or even delete data from those tables. Even so, they still are system tables so they are hidden by default. If you want see them in the navigation pane, you will need to go to the Navigation Options and enable visibility for both hidden and system objects.

If you compare the available options on the dialog with the options you have in the schema.ini, you can see some notable differences. For one example, the schema.ini allows more choices with regards to how the dates or currency amounts may be formatted. With the saved specifications, we can only control date formatting from a preset list of possible format. There are no direct options for currency amounts because we use the Data Type to indicate that a column should be imported as currency regardless of the formatting. Thus, the system tables’ options is simple compared to the schema.ini. We will review the tables’ structure next.

MSysIMEXSpecs structure

The table represents a set of saved specifications. For each row, the table describes the overall format of the file, similar to the 2nd section of the schema.ini. However, there are different options which needs to be considered.

• DateDelim: Identifies the delimiter for the dates. It is typically / or -. It may be one or zero character only.
• DateFourDigitYear: A Yes/No field indicating whether the dates are formatted with four digits for years. If set to yes, values like 10/10/20 would not be considered valid dates.
• DateLeadingZeros: Another Yes/No field indicating whether the dates has leading zeroes.
• DateOrder: Determines the date formatting we want to use. The following codes are valid:
  

  0: DMY 1: DYM

  2: MDY 3: MYD

  4: YDM 5: YMD

• DecimalPoint: Indicates the character to separate the integral and fractional part for a decimal number. It must be exactly one character only.
• FieldSeparator: Separates fields in the text file. For a CSV file, it would be , (a comma character). Also, it must be exactly one character only.
• FileType: Similar to schema.ini‘s CharacterSet. As mentioned in the previous article, you can locate all possible code pages supported on your Windows machine, look in the registry key Computer\HKEY_CLASSES_ROOT\MIME\Database\Codepage\. It identifies the encoding of the text file. You can use the same code page identifier. However, there is one problem. The FileType is defined as an Integer. Therefore, a code page 65000 (e.g. Unicode) would be reported as -536. To convert any code page identifier greater than 32767, you can use formula CInt("&H" & Hex(x)) and to reverse the operation, CLng("&H" & Hex(x)). For example, converting code page 65000 would go through CInt("&H" & Hex(65000)) to yield -536. To reverse, CLng("&H" & Hex(-536)) to get 65000 back again.
• SpecID: Autonumber for the table. Note that this is not the primary key of the table. This column is used with MSysIMEXColumns.SpecID to relate records. However, there is no foreign key constraint between the two tables, so orphaned deletions can occur.
• SpecName: The primary key of the table. It uniquely identifies the specification. Whenever you save a specification, the name you provide will be recorded in this field. When you import or export using wizards, Access may automatically create a new specification even if you did not use the Advanced and explicitly save it.
• SpecType: It can only be either 1 to signify delimited file or 2 to signify fixed-width file.
• StartRow: Determines the row on the text file to start scanning for import. That can be any number so it is possible to skip several lines at the top of the file. It can be useful for ill-formed text files that has non-conforming headers.
• TextDelim: A single character that delimits a text value. It can be omitted if text are not delimited. If you can control the format of the text files, it’s usually good idea to have text delimiters. A text file with text delimiters will remain well-formed even if the text contains field separators (e.g. a comma) within the text.
• TimeDelim: Indicates the delimiter character used for the time. It can be any single character or be blank and defaults to : (a colon character).

MSysIMEXColumns structure

The table describes the individual columns in the text file and any attributes. This roughly corresponds to the third section of the schema.ini. However, there are additional options such as indexing that doesn’t exist in schema.ini.

• Attributes: Unknown. In my tests I never got it to be anything besides 0. If you know what it is for and how it can be used, feel free to leave a comment.
• DataType: The column’s data type. The number should correspond to the DAO.DataTypeEnum.
• FieldName: The column’s name. Note that Access will default to FieldN where N is a positive integer.
• IndexType: May be 0 to indicate no indices, 1 to indicate regular index or 2 to indicate unique index.
• SkipColumns: A Boolean value indicates if the column should be skipped. If skipped, it will not be available for querying or reading.
• SpecID: Corresponds to the MSysIMEXSpecs.SpecID autonumber. Note there is no foreign key constraints between the two tables. If you’ve deleted records, there may be orphaned records from the MSysIMEXSpecs table.
• Start: An integer indicating when the column starts on the text file. It is significant for a fixed width file. For delimited files, Access will still use the column position of the first row where the field is found to determine the start. Furthermore, Access will use the column to imply the ordinal ordering of the columns.
• Width: Another integer to govern the size of the column. On a fixed width file, this is also significant. Be aware that Microsoft Access will use that information for variable length data types such as text or binary to size the column accordingly even on a delimited file.

Connection string using specifications: DSN parameter

In the previous article, you saw that there was no change to the connection string in order to use a schema.ini file. We implicitly referred to the schema.ini simply by ensuring that it exists in the same folder where the text file we are linking or opening is present. However, to use the saved specifications from the system requires that we supply the information in the connection. We do this by populating the DSN parameter. The value must refer to the saved specification’s name, as recorded in the MSysIMEXSpecs.SpecName column.

Here is a sample:

Set db = DBEngine.OpenDatabase(vbNullString, False, False, "Text;DSN=Products Link Specification;IMEX=1;DATABASE=C:\Links")

Access will require that both DSN parameter and IMEX parameters be supplied in the connection string. If you attempt to omit the IMEX parameter, you will get an error about Access being not able to find the specification, even though the specification exists in the MSysIMEXSpecs table. It also follows that the table must contain a record with SpecName containing the same value supplied in the DSN parameter.

As noted in the earlier article, the individual text files are treated as a “table” within the folder’s “database”. So though the connection string does not point to a specific text file, the connection string will use the specified specifications on any text files opened through the connection.

While it is possible to use other parameters as discussed in the same article, you should be aware that when in conflict, the saved specification will take precedence over the connection string. For that reason, it’s best to use minimal set of parameters required to successfully connect to a text file and let the saved specifications do the job of describing how to read the text file.

Conclusion

You learned an alternative way to describe the text file’s structure without using an external file. We looked at how Access uses two system tables to store the specifications. Lastly, you also learned how to specify the saved specifications using the DSN parameter. At this point, we’ve covered everything we need to know about linking or opening text files in Access. In the next article, we will put the information learned in the rest of the series all together. We will also look at using the connection strings in Access queries for a no-code approach.