Read Tab Delimited Text file using a Schema.ini into an Access database (ADO)
Hi,
i am trying to read a Tab-Delimited Text file using a Schema.ini into an Access database (ADO). however, i dont know how to include the Schema.ini file in ADO.
the Schema.ini file has:
[Sample.txt]
Format=TabDelimited
code:
Sub ADOOpenTextFileImport1()
On Error GoTo ErrHandler
Dim adoCn As ADODB.Connection
Dim strCn As String
Dim strSQL As String
lblAction.Caption = "ADO Import 1..."
strCn = "Driver=" & txtDriverISAM.Text & ";" & _
"DBQ=" & App.Path & ";" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=Admin;Pwd=;Extensions=asc,csv,tab,txt;"
Set adoCn = New ADODB.Connection
adoCn.Open strCn
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
adoCn.Execute strSQL
Else
'Delete data before importing - use if necessary.
strSQL = "DELETE FROM [" & txtTable.Text & "] IN '" & App.Path & "" & txtDatabase.Text & "'"
adoCn.Execute strSQL
'Append data to Access table.
strSQL = "INSERT INTO [" & txtTable.Text & "] IN '" & App.Path & "" & txtDatabase.Text & "'"
strSQL = strSQL & "SELECT * FROM " & txtFile.Text
adoCn.Execute strSQL
End If
GoTo ExitSub
ErrHandler:
lblAction.Caption = "ADO Import 1 - Error."
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
ExitSub:
lblAction.Caption = "Complete..."
adoCn.Close
Set adoCn = Nothing
End Sub
Where do i include the Schema.ini file in this code?
right now it does not seem to read the text file correctly as some column headers are getting clubbed together along with their data.