Microsoft KB Archive/262537: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
Line 83: | Line 83: | ||
Private Sub CmdOpen_Click() | Private Sub CmdOpen_Click() | ||
'============================method 1================================= | '============================method 1================================= | ||
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & | cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ | ||
"Data Source=" & | "Data Source=" & PathtoTextFile & ";" & _ | ||
"Extended Properties=""text;HDR=YES;FMT=Delimited""" | "Extended Properties=""text;HDR=YES;FMT=Delimited""" | ||
rs.Open "select * from TextFile.txt", _ | rs.Open "select * from TextFile.txt", _ | ||
cn, adOpenStatic, adLockReadOnly, adCmdText | cn, adOpenStatic, adLockReadOnly, adCmdText | ||
'============================method 2================================= | '============================method 2================================= | ||
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & | 'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ | ||
"Data Source=" & | "Data Source=" & PathtoMDB & "Northwind.mdb" | ||
'rs.Open "SELECT * FROM [Text;Database=" & | 'rs.Open "SELECT * FROM [Text;Database=" & PathtoTextFile & ";" & _ | ||
"HDR=YES;FMT=Delimited].[textfile.txt]", _ | "HDR=YES;FMT=Delimited].[textfile.txt]", _ | ||
cn, adOpenStatic, adLockReadOnly, adCmdText | cn, adOpenStatic, adLockReadOnly, adCmdText | ||
Line 102: | Line 102: | ||
Set Cat = New ADOX.Catalog | Set Cat = New ADOX.Catalog | ||
Set objTable = New ADOX.Table | Set objTable = New ADOX.Table | ||
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & | cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ | ||
"Data Source=" & | "Data Source=" & PathtoMDB & "Northwind.mdb" | ||
'Open the Catalog | 'Open the Catalog | ||
Set Cat.ActiveConnection = cn | Set Cat.ActiveConnection = cn | ||
Line 119: | Line 119: | ||
'Insert into table1 the contents of textfile.txt | 'Insert into table1 the contents of textfile.txt | ||
cn.Execute "INSERT INTO Table1 SELECT * FROM " & | cn.Execute "INSERT INTO Table1 SELECT * FROM " & _ | ||
"[Text;Database=" & | "[Text;Database=" & PathtoTextFile & ";HDR=YES].[TextFile.txt]" | ||
cn.Close | cn.Close | ||
MsgBox "Finished Inserting into MDB" | MsgBox "Finished Inserting into MDB" |
Latest revision as of 12:35, 21 July 2020
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
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
- Create a new Standard EXE in Visual Basic. Form1 is created by default.
- 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.
- Add two CommandButton controls to the form, and name them CmdOpen and CmdInsert.
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
- 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