Microsoft KB Archive/262537

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 11:35, 21 July 2020 by X010 (talk | contribs) (Text replacement - "&" to "&")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Article ID: 262537

Article Last Modified on 7/1/2004



APPLIES TO

  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition



This article was previously published under Q262537

SUMMARY

This article discusses how to open a delimited text file by using ADO and the Microsoft Jet Provider's Text IIsam driver. In this article, the Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is used to create a table to accept the text file.

MORE INFORMATION

A form is created in the following steps. There are two command buttons on the form. The first button provides two methods for opening a text file; the second button demonstrates how to import a text file into an existing table.

Steps to Accomplish Behavior

  1. Create a text file called textfile.txt that contains code similar to the following:

    "F1","F2","F3"
    "one", "two", "three"
    "1a","2b", "3c"
    1,2,3
                        
  2. Create a new Standard EXE in Visual Basic. Form1 is created by default.
  3. On the Project menu, click to select References, and then select Microsoft Active Data Objects and Microsoft ADO Ext.2.x for DDL and Security.
  4. Add two CommandButton controls to the form, and name them CmdOpen and CmdInsert.
  5. Add the following code to the form. You need to change the paths to reflect locations on your machine.

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim PathtoTextFile As String
    Dim PathtoMDB As String
    Dim myarray() As Variant
    Private Sub Form_Load()
      PathtoTextFile = "C:\PathtoTextFile\"
      PathtoMDB = "C:\PathtoMDB\"
      CmdOpen.Caption = "Open textfile and display field value"
      CmdInsert.Caption = "Insert textfile values into MDB"
    End Sub
    Private Sub CmdOpen_Click()
      '============================method 1=================================
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & PathtoTextFile & ";" & _
              "Extended Properties=""text;HDR=YES;FMT=Delimited"""
      rs.Open "select * from TextFile.txt", _
              cn, adOpenStatic, adLockReadOnly, adCmdText
      '============================method 2=================================
      'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & PathtoMDB & "Northwind.mdb"
      'rs.Open "SELECT * FROM [Text;Database=" & PathtoTextFile & ";" & _
              "HDR=YES;FMT=Delimited].[textfile.txt]", _
              cn, adOpenStatic, adLockReadOnly, adCmdText
      '=====================================================================
      MsgBox rs(0)
      rs.Close
      cn.Close
    End Sub
    Private Sub CmdInsert_Click()
      Set Cat = New ADOX.Catalog
      Set objTable = New ADOX.Table
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & PathtoMDB & "Northwind.mdb"
      'Open the Catalog
      Set Cat.ActiveConnection = cn
      'delete the table if it exists
      On Error Resume Next
      Cat.Tables.Delete "table1"
      'Create the table
      objTable.Name = "Table1"
      'Create and Append a new fields to the "table1" Columns Collection
      objTable.Columns.Append "F1", adWChar
      objTable.Columns.Append "F2", adWChar
      objTable.Columns.Append "F3", adWChar
      
      Cat.Tables.Append objTable
      
      'Insert into table1 the contents of textfile.txt
      cn.Execute "INSERT INTO Table1 SELECT * FROM " & _
                 "[Text;Database=" & PathtoTextFile & ";HDR=YES].[TextFile.txt]"
      cn.Close
      MsgBox "Finished Inserting into MDB"
    End Sub
                        
  6. Run the code. The Cmdopen command opens the text file and displays one. The CmdInsert command creates table1 in the Northwind database and inserts the information in textfile.txt into table1. Comment the code in method 1, uncomment the code in method 2, and then run again the code.


REFERENCES

The Jet Database Engine Programmer's Guide (2nd Edition), Microsoft Press By Dan Haught, Jim Ferguson (ISBN 1-57231-342-0)

Keywords: kbhowto kbmdacnosweep KB262537