Answer to Text IISAM ADO Connectvitiy Q's

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:

Code:

---

[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/showthrea...threadid=12737

Here is the content of the text file, All entry are to be Tab Delimited (one tab) Name -> Address -> City -> State -> Zip:

Code:

---

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:

Code:

---

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.