ImportText.exe Importing Text into Access with ADO/RDO/DAO/Filesys/Automation

ID: Q230265

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.0, 2.1, 2.5
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SUMMARY

ImportText.exe is a sample that demonstrates various ways to import text files into an Access database. There are many ways to import text data to an Access database and typically the best option is determined by the task requirements.

Filesys

Automation

The sample application attached details the above coding options.

MORE INFORMATION

The following files are available for download from the Microsoft Download Center. Click the file names below to download the files:

TextImport.exe

For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address

http://www.microsoft.com/downloads/search.asp

and then click How to use the Microsoft Download Center.

FileName	Size
ImportText.vbp	1,464
ImportText.vbw	56
Sample.out	3,346
Sample.txt	3,346
Sample_Header.txt	3,708
Schema.ini	422
Schema_Header.ini	420
TextImport.frm	28,678
TextImport.frx	84
TextImport.mdb	108,544

All files should reside in the same folder. Run the sample application ImportText.vbp and examine the different import/export options. The sample TextImport.mdb is used and should reside in the application path. The default sample text file is Sample.txt. A Sample_Header.txt file is included and contains the column header for the text file. An alternate schema file, Schema_Header.ini, may be used to demonstrate using the ColNameHeader=True option in the schema file corresponding to the Sample_Header.txt file.

Among the data import options demonstrated, DAO is probably the most efficient (fewest layers) or with the smallest memory footprint; especially if importing to an Access database.

Refer to the following list for an overview of the libraries loaded for each data access method.

The FileSys objects sample: Scripting Runtime + DAO libraries + Jet libraries

The RDO sample: RDO libraries + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driver

The ADO (the default example): ADO libraries (OLEDB + MSDASQL) + ODBC libraries + ODBC Jet library + Jet libraries + Text ISAM driver

The Automation sample: MSOffice Runtime library. For the DAO sample: DAO libraries + Jet libraries + Text ISAM driver

The following function is the DAO object sample in the TextImport.vbp application. This code is used in the application when you click the DAO radio button before importing. You can modify the DAO sample by adding a recordset and a loop for data manipulation just as in the FileSys objects example.


Sub DAOOpenTextFileImport()
On Error GoTo ErrHandler

lblAction.Caption = "DAO Import..."

Dim daoDB As DAO.Database
Dim strSQL As String
   
If chkCreateTbl.Value = 1 Then
    DBEngine.IniPath = App.Path & "\Schema_Header.ini"
Else
    DBEngine.IniPath = App.Path & "\Schema.ini"
End If

Set daoDB = OpenDatabase(App.Path, False, False, _
                    "Text;Database=" & App.Path & ";table=" & txtFile.Text)

If chkCreateTbl.Value = 1 Then
    'Use this if you do not already have a table created in Access.
    'Creates and appends the data in one step.
    strSQL = "SELECT * INTO [" & txtTable.Text & "] IN '" & _
                   App.Path & "\" & txtDatabase.Text & " '"
    strSQL = strSQL & "FROM " & txtFile.Text
    daoDB.Execute strSQL
Else
    'Delete data before importing - use if necessary.
    strSQL = "DELETE FROM [" & txtTable.Text & "] IN '" & _
                    App.Path & "\" & txtDatabase.Text & "'"
    daoDB.Execute strSQL
    'Append data to Access table.
    strSQL = "INSERT INTO [" & txtTable.Text & "] IN '" & _
                    App.Path & "\" & txtDatabase.Text & "'"
    strSQL = strSQL & "SELECT * FROM " & txtFile.Text
    daoDB.Execute strSQL
End If

GoTo ExitSub
   
ErrHandler:
    lblAction.Caption = "DAO Import - Error."
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    
ExitSub:
    lblAction.Caption = "Complete..."
    daoDB.Close
    Set daoDB = Nothing
End Sub

The following function is the FileSys object sample in the TextImport.vbp application. This code is used in the application when you select the FileSys radio button before importing. Notice in the sample code that to create the table layout in Access, based on the Schema_Header.ini file, there is no need to loop through the header file and create the table manually if you use the Text ISAM driver. Although, if you are using the Text ISAM driver then there is no need to use the FileSystemObject (and that is part of the point) unless you must use the FileSystemObject to import, then use DAO and do it in one as shown in the DAO sample code. Since you must use DAO anyway (to create the recordset object) even if you are doing data manipulation on import, then use DAO for the entire process since you already have it loaded in memory to create the recordset.


Private Sub FileSysImport()
On Error GoTo ErrHandler

lblAction.Caption = "FileSys Import..."

Dim daoDB As DAO.Database
Dim daoRs As DAO.Recordset
Dim fs As FileSystemObject
Dim ts As TextStream
Dim inLine As Variant
Dim strSQL As String
Dim i As Integer

If chkCreateTbl.Value = 1 Then
    'This is an eazy way to create the Table layout in Access based on the Schema_Header.ini file.
    DBEngine.IniPath = App.Path & "\Schema_Header.ini"
    Set daoDB = OpenDatabase(App.Path, False, False, "Text;Database=" & App.Path & ";table=" & txtFile.Text)
    strSQL = "SELECT * INTO [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & " '"
    strSQL = strSQL & "FROM " & txtFile.Text & " WHERE 1=0"
    daoDB.Execute strSQL
    Set daoDB = Nothing
    Set daoDB = OpenDatabase(App.Path & "\" & txtDatabase.Text, False, False)
Else
    DBEngine.IniPath = App.Path & "\Schema.ini"
    Set daoDB = OpenDatabase(App.Path & "\" & txtDatabase.Text, False, False)
    strSQL = "DELETE * FROM [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & "'"
    daoDB.Execute strSQL, dbFailOnError
End If

strSQL = "SELECT * FROM [" & txtTable.Text & "] WHERE 1=0"
Set daoRs = daoDB.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)

Set fs = New FileSystemObject
Set ts = fs.OpenTextFile(App.Path & "\" & txtFile.Text, ForReading, False, TristateUseDefault)

'This skips the column header.
If chkColHeader.Value = 1 Then
    inLine = Split(ts.ReadLine, ",")
End If

While Not ts.AtEndOfStream
    inLine = Split(ts.ReadLine, ",")
    daoRs.AddNew
    For i = 0 To UBound(inLine) - 1
        daoRs.Fields(i).Value = Left(inLine(i), daoRs.Fields(i).Size)
    Next i
    daoRs.Update
Wend

GoTo ExitSub

ErrHandler:
    lblAction.Caption = "FileSys Import - Error."
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    
ExitSub:
    lblAction.Caption = "Complete..."
    If Not ts Is Nothing Then ts.Close
    If Not daoRs Is Nothing Then daoRs.Close
    daoDB.Close
    Set daoRs = Nothing
    Set daoDB = Nothing
    Set ts = Nothing
    Set fs = Nothing
End Sub

The simplest example is the Automation sample. A sample TextImport.mdb is used and the example import/export specifications have been created in the sample .mdb file: Sample and sample w/columns. You can find the specification property setting on the Properties tab of the Tab control. To import with or without the column names in the first row create another import/export specification and put the name of that specification in the text box txtSpecName on the tab control. An example specification is included in the sample .mdb file: Sample w/columns. To import the text file with Access Automation you can simply execute the DoCmd.TransferText method of the Access object.


Private Sub AccessAutomateImport()
'Assumes table already exists.
On Error GoTo ErrHandler

lblAction.Caption = "Access Automation..."
   
Dim AccessApp As access.Application
Dim strDB As String
   
strDB = App.Path & "\" & txtDatabase.Text

Set AccessApp = New access.Application
AccessApp.OpenCurrentDatabase strDB
   
'To Import with/without Column names in first row create another Import/Export Specification
'and put the name of that specification in the Text box 'txtSpecName' on the Tab Control.
'An example Specification is included in the sample MDB - 'Sample w/columns'.
AccessApp.DoCmd.TransferText acImportDelim, txtSpecName.Text, txtTable.Text, App.Path & "\" & txtFile.Text

AccessApp.CloseCurrentDatabase

GoTo ExitSub

ErrHandler:
    lblAction.Caption = "Access Automation - Error."
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    
ExitSub:
    lblAction.Caption = "Complete..."
    Set appAccess = Nothing
End Sub

For additional details and code refer to the sample application TextImport.exe.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q187670 HOWTO: Use RDO and ODBC Text Driver to Open a Delimited Text

Q155512 ACC: How to Create a Schema.ini File Programmatically

Q149090 ACC: How to Use Schema.ini for Accessing Text Data

Additional query words: kbgrpvbdb, kbado, kbrdo, kbdao

Keywords : kbfile kbADO kbDAO kbGrpVBDB kbGrpMDAC kbDSupport kbDAO360
Version : WINDOWS:2.0,2.1,2.5,5.0,6.0
Platform : WINDOWS
Issue type :