Help .. i'm confused...

[iskyline]

hi there,

i'm very new to programming with databases ... i'm coding in vbscripting because i'm trying to make a user friendly HTA for the people at work.

so the basic process...
search recursively for txt files in a given folder - works fine
import first text file into a database table1 -works fine
--------i'm having a problem with the line below.
run a query in the same database on table1 and table 2 and write to text file in same location as first text file.
----
go to second text file... and so on.

the output i get is the first and second file of 3 files are not created but the third is created perfectly ... following is the code...

takes data from text file and puts the same data into two similar tables. first table is Folder_variable and second is temp_variable.

compare temp_variable table with WOrder_variable table and take the results and put in the text file - then clear the temp and repeat for next text file.

hope you get the gist of it.

really hoping someone can help. thanks in advance...

Set objFSO = Nothing
        Set objFile = Nothing
        Set objTextFile = Nothing
        Set arrServiceList = Nothing
 
        tab1 = "ParFolder_" & Form1.WorkOrderNum.value
        tab2 = "Folder_" & Form1.WorkOrderNum.value
        tabdb = "Work_Order_" & Form1.WorkOrderNum.value & ".mdb"
        tab36 = "WOrder_" & Form1.WorkOrderNum.value
        tab35 = "Temp_" & Form1.WorkOrderNum.value        'idTimer = window.setTimeout("Pause", 500, "VBScript")
 
        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordSet = CreateObject("ADODB.Recordset")
 
        Set varConnection = CreateObject("ADODB.Connection")
        Set varRecordSet = CreateObject("ADODB.Recordset")
 
        Set tmpConnection = CreateObject("ADODB.Connection")
        Set tmpRecordSet = CreateObject("ADODB.Recordset")
 
        Set txtConnection = CreateObject("ADODB.Connection")
        Set txtRecordSet = CreateObject("ADODB.Recordset")
        txtRecordset.CursorLocation = adUseClient
 
        objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
        varConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & VarPath & tabdb
        tmpConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
        txtConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
 
        objRecordset.Open "SELECT * FROM " & tab2 & "" , objConnection, adOpenStatic, adLockOptimistic
        varRecordset.Open "SELECT " & tab1 & ".PathtoParent FROM " & tab1 & "" , varConnection, adOpenStatic, adLockOptimistic
        tmpRecordset.Open "SELECT * FROM " & tab35 & "" , tmpConnection, adOpenStatic, adLockOptimistic
 
        Set objFSO = CreateObject ("Scripting.FileSystemObject")                
 
        sComputer = "."    ' use "." for local computer 
 
        Set oWMI = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2") 
 
        ThePath = varRecordset.Fields("PathtoParent")
 
        Set oDir = oWMI.Get("Win32_Directory='" & ThePath & "'") 
 
        'Set oDir = tab1.PathtoParent 
 
        ' Get the files into an array 
        aFiles = Array() 
 
 
        EnumFolders oDir, oWMI, aFiles 
        'writingarea7.innerHTML = "Total Number of Batch Index Files Found = " & UBound(aFiles) + 1 & vbCrLf 
        For i = 0 To UBound(aFiles) 
 
            Set objTextFile = objFSO.OpenTextFile(aFiles(i), ForReading)    
            Set objFile = objFSO.GetFile (aFiles(i))
 
            Do Until objTextFile.AtEndOfStream
 
                    strNextLine = objTextFile.Readline
                    arrServiceList = Split(strNextLine , "    ")
 
                    'msgbox arrServiceList(0)
                    objRecordset.AddNew
                    objRecordset("Prefix") = arrServiceList(0)
                    objRecordset.Update
 
                    tmpRecordset.AddNew
                    tmpRecordset("Prefix") = arrServiceList(0)
                    tmpRecordset.Update
 
                    z=2
                    on error resume next
                    For j = 1 to Ubound(arrServiceList)
 
                        objRecordset("Indexing") = arrServiceList(j)
                        objRecordset("Extension") = arrServiceList(z)
 
                        tmpRecordset("Indexing") = arrServiceList(j)
                        tmpRecordset("Extension") = arrServiceList(z)
 
 
                        z=z+1
                        j=j+1
                    Next
 
 
                    objRecordset.Update
                    tmpRecordset.Update
            Loop
 
            FinalTxt = objFSO.GetParentFolderName(objFile) & "\Upload.txt"
            textfilepath = FinalTxt
 
 
            'x=1
            'dim INsql
            'INsql = "SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & " INNER JOIN " & tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing"
            'txtconnection.execute(INsql)
            'txtRecordset.CursorLocation = 0
            txtRecordset.Open "SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & " INNER JOIN " & tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing", txtConnection, adOpenStatic, adLockOptimistic
 
            'set txtRecordset = BOF
            ' 
 
            'txtRecordset.MoveLast
            'txtRecordset.MoveFirst
 
            If txtRecordset.EOF Then
                 msgbox "No Match in database, please make sure you have selected the right files..."
                 '
                 'txtRecordset.MovePrevious
             Else
                'writingarea3.innerHTML = "Phase 3 of 4 - Starting to Make Upload Textfile"
 
                Set objectwriteFile = objFSO.OpenTextFile(textfilepath, ForAppending, True)
                While Not txtRecordset.EOF
 
                    efix = txtRecordset.Fields("prefix")
                    xing = txtRecordset.Fields("indexing")
                    sion = txtRecordset.Fields("extension")
                    itle = txtRecordset.Fields("title")
 
'                    if x=1 then
 
'msgbox "Prefix = " & efix
'msgbox "Index = " & xing
'msgbox "Extension = " & sion
'msgbox "Title = " & itle
'                    end if 
'                    x=x+1
                    objectwriteFile.Writeline(efix & vbTab & xing & vbTab & sion & vbTab & itle)
                     txtRecordset.MoveNext
                 Wend
            End If
 
            objectwriteFile.Close
            'msgbox "check the following table in client DB " & tab35
            txtRecordSet.Close        
            dim dsql
            dsql = "DELETE * FROM " & tab35
            tmpConnection.Execute(dsql)
 
        Next 
 
        tmpRecordSet.Close
 
        dim droptmptable
        droptmptable = "DROP Table " & tab35
        tmpConnection.Execute(droptmptable)
 
        objwriteFile.Close 
        objRecordSet.Close
        objConnection.Close
 
        varRecordSet.Close
        varConnection.Close
 
        tmpConnection.Close
 
 
        txtConnection.Close
 
    End Sub

[NeoPa]

Confused?
You and me both.
Please visit these two areas in the FAQ (Use a Good Thread Title & How to ask a question) and re-post the question (in here or in a new thread as you prefer) so that it is legible and easily understood. I couldn't even make enough sense of it to assign a better title :(

This question has no title; Very little punctuation; Masses of code. These issues at least, should be dealt with before expecting anyone to spend their valuable time trying to help.
Bear in mind also, that these forums are for dealing with particular issues, and are neither a proxy service for writing code, nor for debugging it.

MODERATOR.

PS If this seems harsh then please don't think it is personal. It is just that this type of thread can be so much more of a problem than you would probably realise unless you were answering them yourself.