Undeterred by the previous significantly buggy annoyance of docmd.transferspreadsheet, the Poorhouse was commanded to transfer some more facts 'n' figures; this time in the ultimate old-skool format, a flat text file. The info within was to be featured in a Microsoft Access table.

In order to transfer data from a text file - for example a comma separated values (csv) file or a fixed length file – Access VBA includes the command TransferText. The syntax is as follows:
TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

The SpecificationName parameter is what tells Access how to interpret the data file in terms of field names, data types and so. According to the help file:
"[If you're doing a fixed width file or a fancy delimited file] you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.". Which is fine, except it's wrong (in Access 2000 / XP anyway). However much you tempt it into sucking up the schema file, it seems that it takes no account of whether it exists or not and either fails or uses "default" options hence ruining your data.

A typical error message when this happens is "Run-time error '2511': The action or method requires a Specification Name argument"...even though the help file says otherwise.

The way round this, to ensure the schema is taken into account, is by using text ISAM driver DAO technology to import the file instead. This may sound mega-complex but assuming you’re a dab hand at copy and paste it's not too bad. Microsoft's official write up can be found here.

Basically, just copy the code snippet below that they provide and change the table names / data file locations to whatever you want.

If you haven’t already, you’ll need to set a reference to the DAO object library in your Access project so it understands what a "DAO.Database" object is. To do this, in the VBA code window, go to the Tools menu, then pick References, and in the resulting screen make sure something along the lines of "Microsoft DAO Object Library" is ticked.

In Windows (XP), a shortcut to writing a schema.ini file can be found in Control Panel, whereby if you go to set up a data source but don't complete the operation the schema file is still written out. Sometimes it doesn't seem to be quite right and needs editing but this can be manually fixed via editing in, say, Notepad (and may well be "user error" shall we say...")

Control Panel -> Administrative Tools -> Data Sources (ODBC)

On User DSN tab, click "Add..." button, then choose "Microsoft Text Driver". Put in any Name – it doesn’t matter what, we won’t be using it. Deselect "Use current directory" and use the "Select Directory" button to select the directory your file is in.

Then we need to define the column names, types and so on. Select the "Options >>" button. Click "Define Format...". Select the tables from the left hand side, and where necessary set the field options from the right hand side. When you're done, click OK which returns you to the previous screen. Get rid of this screen and you should find that a schema.ini file has been written in the directory that your data file is in. If you get errors and so on, just go in with Notepad or equivalent with the handy aid of Microsoft's trusty guide to what the file should look like.