importing text into access2k

[dwhawley]

does anyone know how to import fixed width text files into access 2000 without using the import wizard or adusting the registry. these are the only options that i have been able to find in documentation. i am successfully importing, but there is way too much room for an end-user to create a lot of havoc on the process. if i could pass the parameters to the wizard through code, i would be really happy, but i'm not seeing anything that will allow me to do that.

[Clunietp]

The only other way to do it is to write your own import app.....they usually aren't too hard to do, just need to do some file/string manipulations....

[Jaguar]

One of the techniques not frequently talked about is using the Text IISAM driver with ODBC. The purpose is to create a Schema.ini file for a directory that contains the fixed width files which defines the files (as if the directory were a database and the files were the tables). More information about Schema.ini can be found in Microsoft's Visual Studio Helps or at MS site.

1) Goto Control Panel|ODBC|Add User DSN (Don't save when done)
2) Choose Microsoft Text Driver
3) Don't put a name or description because you will only be using the dialog interface.
4) Uncheck the Current Directory box and selcec the directory that contains the .TXT files.
5) Click the Options button to extend the dialog.
6) Define format.
7) If you don't see any files, then you must go back to step 4
8) Choose a file (that you consider your first table) NOTE: a lot of testing and trials runs go along with different scenarios!!
9) Uncheck Column Name Header
10) For Format, choose Fixed Length
11) Avoid using the Scan, just leave it alone (expert stuff)
12) Characters, leave as default
Now to the right side of the Dialog
13) Enter the information that defines the first field in your table (text file)
14) Choose the add button
15) Continue 13-14 until all fields are defined.
16) Continue 8-15 until all tables are defined.
17) Choose OK to close the dialog
18) Choose cancel and exit out of ODBC not saving

** This has just now saved a file in that directory called Schema.ini that defines the structure of all the tables in that directory, and primarily created a Schema DDF file. You cold have writtent this file yourself, but which way is easier?

19) Open Access or VB
20) Assuming you know how to connect to a table write the following code

Private Sub AppendTableNamed(sName As String)
    Dim db As Database
    Dim tblDef As TableDef

    ' Open the current Database
    Set db = CurrentDb

    ' Create a new table in the database; this is a name
    ' that you will see in the Tables list of the db
    Set tblDef = db.CreateTableDef(sName)

    ' State the name of the Source Table; this is the name
    ' of the text file/section name of the schema.ini
    ' NOTE: the Schema.ini section must match to this EXACTLY
    ' For example. [Filename.txt] would be the name of the section
    ' if sName = "Filename.txt"  THIS IS USUALLY WHERE PEOPLE GET HUNG UP
    tblDef.SourceTableName = sName

    ' Use this string to connect to the directory and find
    ' the tables (files)
    tblDef.Connect = "Text;Database=\\Directory\PathNotIncluding\LastBackSlash"

    On Error Resume Next
    ' If the table already exists, then remove it
    db.TableDefs.Delete sName
    Err.Clear ' Clear the error that may occur if the sName doesn't
              ' exist for the first time, just continue to the TableDefs.Append
    db.TableDefs.Append tblDef
    If Err.Number > 0 Then ' If there was some type of error on append
        MsgBox (sName & ": " & Err.Description)
    End If
End Sub

This bit of code (to me) is priceless because you can do so much with a Schema.ini and text files and the Text IISAM driver.

Good Luck.

P.S. I did "whip" the step-by-step together (but the code is fine), so if there are some errors in the Step-by-Step code, I apologize, but should get you where you're going.

[Edited by Jaguar on 03-29-2000 at 06:54 PM]