Building Import/Export Specs in VBA

Public domain.
(c) Shamil Salakhetdinov
DARTS Ltd., St.Petersburg, Russia
e-mail: shamil@marta.darts.spb.ru

Abstract

This database shows how to bypass MS Access 2.0 <-> MS Access 7.0/8.0 text files import/export interface incompatibilities and BTW solves the problem of MS Access 7.0/8.0 Import/Export Wizard "excessive shrewdness".

Where MS Access stores Import/Export specifications

MS Access stores Import/Export specifications in two MSys* tables:

• MSysIMEXSpecs and
• MSysIMEXColumns

First contains Import/Export spec's general information and the second - fields definitions.

It is well(?)-known that the structure and the data stored in these tables depend on MS Access version.

What MS Access components use Import/Export specs

I know two MS Access components which create/update/use Import/Export specs:

• Import/Export Wizard and
• DoCmd TransferText ... - action interpreter

Big(?) problem you meet with converting MS Access 2.0 -> MS Access 7.0/8.0

MS Access 2.0 does not use fields' definitions to import/export delimited text files but MS Access 7.0/8.0 uses them - therefore in MS Access 7.0/8.0 you need to have Import/Export specs for every table/query you use as a source/ destination of DoCmd TransferText... action. And this is a big(?) problem to convert from MS Access 2.0 -> MS Access 7.0/8.0 a general tables' backup/restore/ export/import procedure. In MS Access 2.0 you can (and usually) use one general import/export spec to backup/restore/import/export *all* tables. In MS Access 7.0/8.0 you can't do that.

If you have in your database dozens of tables you'll find it very annoying to create import/export spec for every table using Import/Export Wizard.

Solution

You'll find it in these databases:

• Download Access 2.0 sample database
• Download Access 95 sample database
• Download Access 97 sample database

Usage

Create a directory c:\imex and store this imex20.mdb in it.

Use one of the following global constants from module "$bas MS Access Version" for corresponding MS Access version (comment another two):

Global Const gintAccVersion = 2 ' MS Access 2.0
Global Const gintAccVersion = 7 ' MS Access 7.0
Global Const gintAccVersion = 8 ' MS Access 8.0

In module "bas Examples" you'll find a test sub named "an_ExportImportTest" - run it and trace by debugger to see what it does -

In MS Access 2.0 it:

• Exports Delimited Text from Company table to c:\imex\CompDlmT.txt using predefined import/export spec "IMEXSpec_DelimitedText_General"
• Exports Fixed Text from Company table to c:\imex\CompFixT.txt using predefined import/export spec "IMEXSpec_FixedText_Company"
• Imports Delimited Text from c:\imex\CompDlmT.txt to Company_ImportFromDelimitedTextFile table using predefined import/export spec "IMEXSpec_DelimitedText_General"
• Imports Fixed Text from c:\imex\CompFixT.txt to Company_ImportFromFixedTextFile table using predefined import/export spec "IMEXSpec_FixedText_General"

All these procedures are well-known to professional MS Access developers. More interesting IMHO to trace this test under MS Access 7.0/8.0.

In MS Access 7.0/8.0 it (don't forget to comment/uncomment version dependent constant gintAccVersion after conversion of imex20.mdb to MS access 7.0/8.0):

• *Generates* Import/Export spec "IMEXSpec_Company" and Exports Delimited Text from Company table to c:\imex\CompDlmT.txt using generated spec "IMEXSpec_Company"
• Exports Fixed Text from Company table to c:\imex\CompFixT.txt using predefined import/export spec "IMEXSpec_FixedText_Company"
• *Generates* Import/Export spec "IMEXSpec_Company_ImportFromDelimitedTextFile" and Imports Delimited Text from c:\imex\CompDlmT.txt to Company_ImportFromDelimitedTextFile table using generated import/export spec "IMEXSpec_Company_ImportFromDelimitedTextFile"
• *Generates* Import/Export spec "IMEXSpec_Company_ImportFromFixedTextFile" and Imports Fixed Text from c:\imex\CompFixT.txt to Company_ImportFromFixedTextFile table using generated import/export spec "IMEXSpec_Company_ImportFromFixedTextFile"

That's it. Enjoy!

P.S. I don't know why MS dropped the menu entry File->Imp/Exp Setup in MS Access 7.0/8.0 and substituted *very* flexible TransferText action interpretation we have in MS Access 2.0 with more restrictive one. This was a big surprise for me. I guess (and this is MHO) they think (or force us to think) that Replication should be used to exchange/replicate database contents but standard TransferText action we have in MS Access 2.0 sometimes is more than enough to replicate database data...

P.P.S Some of fields of Import/Export spec aren't parameterized in these examples, e.g. a pipe character - '|' - used here as field delimiter but you can easily substitute them with global variables and/or functions arguments being defined on run-time...