Microsoft KB Archive/230265

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

Article ID: 230265

Article Last Modified on 8/5/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q230265



SUMMARY
The ImportText.exe sample demonstrates various ways to import text files into a Microsoft 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.


 * ADO
 * RDO
 * DAO
 * 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:

TextImport.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

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..." appAccess.Quit Set appAccess = Nothing End Sub For additional details and code refer to the sample application TextImport.exe.

