Answer to Text IISAM ADO Connectvitiy Q's

[Jaguar]

I have had a lot of questions about connecting to a Text IISAM driver using Schema.ini files and here is a sample of the text to a common question.

The SCHEMA.INI file using a Tab Delimited format:
[people.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=NAME Char Width 255
Col2=ADDRESS Char Width 255
Col3=CITY Char Width 255
Col4=STATE Char Width 255
Col5=ZIP Char Width 255
Please refer to my previous posting for information about creating a SCHEMA.INI at thread http://forums.vb-world.net/showthread.php?threadid=12737

Here is the content of the text file, All entry are to be Tab Delimited (one tab) Name -> Address -> City -> State -> Zip:
Name Address City State Zip
Jaguar Smith 3000 West 400 North West Hampton VX 32880
Jaguar Smith 3000 West 400 North West Hampton VX 32880
Jaguar Smith 3000 West 400 North West Hampton VX 32880
Jaguar Smith 3000 West 400 North West Hampton VX 32880
Jaguar Smith 3000 West 400 North West Hampton VX 32880
Here is the code to put in a VB6 module:
Public Sub Main()
Dim TextConn As ADODB.Connection ' Text file connection
Dim Textrs As ADODB.Recordset ' Text file recordset
Dim strAccessConn As String ' Store Access .MDB connection info
Dim AccessConn As ADODB.Connection ' Access .MDB connection

'-------------------------------
' Create a new connection
'-------------------------------
Set TextConn = New ADODB.Connection

'-------------------------------
' Set its properties
' MSDASQL is the ODBC Provider required to
' access the Text Files DSN
'-------------------------------
TextConn.Provider = "MSDASQL"
TextConn.ConnectionString = _
"DSN=Text Files;" & _
"Driver=Microsoft Text Driver (*.txt; *.csv)"

'-------------------------------
' Open the Connection
'-------------------------------
TextConn.Open

'-------------------------------
' Designate what the default database is
' In this case it's a directory
' For a network it would be -> \\Server\Dir\Dir\Dir\
'-------------------------------
TextConn.DefaultDatabase = "c:\"

'-------------------------------
' Create a new Recordset
'-------------------------------
Set Textrs = New ADODB.Recordset

'-------------------------------
' Open the recordset using the Schema.ini and database
'-------------------------------
Textrs.Open "select Name, Address, City, State, Zip from People.txt", TextConn

'-------------------------------
' Create a connection to Access database
'-------------------------------
Set AccessConn = New ADODB.Connection

'-------------------------------
' Open connection to Access database
'-------------------------------
strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\people.mdb;"
AccessConn.Open strAccessConn

'-------------------------------
' Delete all Records from Table
' adExecuteNoRecords is required to increase efficiency, only to be
' used when you execute a Select query via .Execute and would like
' to return records to the recordset, leave the parameter vacant.
'-------------------------------
AccessConn.Execute "Delete * from tblPeople", , adExecuteNoRecords

'-------------------------------
' Declarations to faciliate refrerences to values, and
' to increase efficiency.
'-------------------------------
Dim ofldName As Variant
Dim ofldAddress As Variant
Dim ofldCity As Variant
Dim ofldState As Variant
Dim ofldZip As Variant

'-------------------------------
' Set the fields to the associated ordinal field positions in RS
' This will always work because of using
' <Select Field1, Field2, Field3 From tblTable> instead of ' This increases efficiency
' <Select * from tblTable> ' This is less efficient
'-------------------------------
Set ofldName = Textrs.Fields(0)
Set ofldAddress = Textrs.Fields(1)
Set ofldCity = Textrs.Fields(2)
Set ofldState = Textrs.Fields(3)
Set ofldZip = Textrs.Fields(4)

'-------------------------------
' Cycle through all the reocords in the text file
'-------------------------------
Do While (Not Textrs.EOF)
' I think this is what is causing my problem. I am not sure how to
'correctly write the syntax to load the data into a new table.
Dim ssql As String
Dim blnSuccess As Boolean

'-------------------------------
' Create a SQL which will insert the record into
' the Table
'-------------------------------
ssql = ""
ssql = ssql & "Insert Into tblPeople "
ssql = ssql & " (Name, Address, City, State, Zip) "
ssql = ssql & "VALUES "
ssql = ssql & " ('" & ofldName & "','" & ofldAddress & "','" & _
ofldCity & "','" & ofldState & "','" & ofldZip & "')"

'-------------------------------
' Execute the SQL
'-------------------------------
AccessConn.Execute ssql, blnSuccess, adExecuteNoRecords

'-------------------------------
' Check for success/fail
'-------------------------------
If Not blnSuccess Then
MsgBox "Insert failure.", vbInformation
End If

'-------------------------------
' Move to the next text file record
'-------------------------------
Textrs.MoveNext
Loop

'-------------------------------
' Clean up gracefully
'-------------------------------
If Not Textrs Is Nothing Then Textrs.Close
If Not TextConn Is Nothing Then TextConn.Close
If Not AccessConn Is Nothing Then AccessConn.Close

'-------------------------------
' Kill the variables
'-------------------------------
Set Textrs = Nothing
Set TextConn = Nothing
Set AccessConn = Nothing
End Sub

Hope this helps people.