By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

Help .. i'm confused...

P: 13
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...

Expand|Select|Wrap|Line Numbers
  1. Set objFSO = Nothing
  2.         Set objFile = Nothing
  3.         Set objTextFile = Nothing
  4.         Set arrServiceList = Nothing
  5.  
  6.         tab1 = "ParFolder_" & Form1.WorkOrderNum.value
  7.         tab2 = "Folder_" & Form1.WorkOrderNum.value
  8.         tabdb = "Work_Order_" & Form1.WorkOrderNum.value & ".mdb"
  9.         tab36 = "WOrder_" & Form1.WorkOrderNum.value
  10.         tab35 = "Temp_" & Form1.WorkOrderNum.value        'idTimer = window.setTimeout("Pause", 500, "VBScript")
  11.  
  12.         Set objConnection = CreateObject("ADODB.Connection")
  13.         Set objRecordSet = CreateObject("ADODB.Recordset")
  14.  
  15.         Set varConnection = CreateObject("ADODB.Connection")
  16.         Set varRecordSet = CreateObject("ADODB.Recordset")
  17.  
  18.         Set tmpConnection = CreateObject("ADODB.Connection")
  19.         Set tmpRecordSet = CreateObject("ADODB.Recordset")
  20.  
  21.         Set txtConnection = CreateObject("ADODB.Connection")
  22.         Set txtRecordSet = CreateObject("ADODB.Recordset")
  23.         txtRecordset.CursorLocation = adUseClient
  24.  
  25.         objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
  26.         varConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & VarPath & tabdb
  27.         tmpConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
  28.         txtConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & NetPath & dbfile 
  29.  
  30.         objRecordset.Open "SELECT * FROM " & tab2 & "" , objConnection, adOpenStatic, adLockOptimistic
  31.         varRecordset.Open "SELECT " & tab1 & ".PathtoParent FROM " & tab1 & "" , varConnection, adOpenStatic, adLockOptimistic
  32.         tmpRecordset.Open "SELECT * FROM " & tab35 & "" , tmpConnection, adOpenStatic, adLockOptimistic
  33.  
  34.         Set objFSO = CreateObject ("Scripting.FileSystemObject")                
  35.  
  36.         sComputer = "."    ' use "." for local computer 
  37.  
  38.         Set oWMI = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2") 
  39.  
  40.         ThePath = varRecordset.Fields("PathtoParent")
  41.  
  42.         Set oDir = oWMI.Get("Win32_Directory='" & ThePath & "'") 
  43.  
  44.         'Set oDir = tab1.PathtoParent 
  45.  
  46.         ' Get the files into an array 
  47.         aFiles = Array() 
  48.  
  49.  
  50.         EnumFolders oDir, oWMI, aFiles 
  51.         'writingarea7.innerHTML = "Total Number of Batch Index Files Found = " & UBound(aFiles) + 1 & vbCrLf 
  52.         For i = 0 To UBound(aFiles) 
  53.  
  54.             Set objTextFile = objFSO.OpenTextFile(aFiles(i), ForReading)    
  55.             Set objFile = objFSO.GetFile (aFiles(i))
  56.  
  57.             Do Until objTextFile.AtEndOfStream
  58.  
  59.                     strNextLine = objTextFile.Readline
  60.                     arrServiceList = Split(strNextLine , "    ")
  61.  
  62.                     'msgbox arrServiceList(0)
  63.                     objRecordset.AddNew
  64.                     objRecordset("Prefix") = arrServiceList(0)
  65.                     objRecordset.Update
  66.  
  67.                     tmpRecordset.AddNew
  68.                     tmpRecordset("Prefix") = arrServiceList(0)
  69.                     tmpRecordset.Update
  70.  
  71.                     z=2
  72.                     on error resume next
  73.                     For j = 1 to Ubound(arrServiceList)
  74.  
  75.                         objRecordset("Indexing") = arrServiceList(j)
  76.                         objRecordset("Extension") = arrServiceList(z)
  77.  
  78.                         tmpRecordset("Indexing") = arrServiceList(j)
  79.                         tmpRecordset("Extension") = arrServiceList(z)
  80.  
  81.  
  82.                         z=z+1
  83.                         j=j+1
  84.                     Next
  85.  
  86.  
  87.                     objRecordset.Update
  88.                     tmpRecordset.Update
  89.             Loop
  90.  
  91.             FinalTxt = objFSO.GetParentFolderName(objFile) & "\Upload.txt"
  92.             textfilepath = FinalTxt
  93.  
  94.  
  95.             'x=1
  96.             'dim INsql
  97.             'INsql = "SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & " INNER JOIN " & tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing"
  98.             'txtconnection.execute(INsql)
  99.             'txtRecordset.CursorLocation = 0
  100.             txtRecordset.Open "SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & " INNER JOIN " & tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing", txtConnection, adOpenStatic, adLockOptimistic
  101.  
  102.             'set txtRecordset = BOF
  103.             ' 
  104.  
  105.             'txtRecordset.MoveLast
  106.             'txtRecordset.MoveFirst
  107.  
  108.             If txtRecordset.EOF Then
  109.                  msgbox "No Match in database, please make sure you have selected the right files..."
  110.                  '
  111.                  'txtRecordset.MovePrevious
  112.              Else
  113.                 'writingarea3.innerHTML = "Phase 3 of 4 - Starting to Make Upload Textfile"
  114.  
  115.                 Set objectwriteFile = objFSO.OpenTextFile(textfilepath, ForAppending, True)
  116.                 While Not txtRecordset.EOF
  117.  
  118.                     efix = txtRecordset.Fields("prefix")
  119.                     xing = txtRecordset.Fields("indexing")
  120.                     sion = txtRecordset.Fields("extension")
  121.                     itle = txtRecordset.Fields("title")
  122.  
  123. '                    if x=1 then
  124.  
  125. 'msgbox "Prefix = " & efix
  126. 'msgbox "Index = " & xing
  127. 'msgbox "Extension = " & sion
  128. 'msgbox "Title = " & itle
  129. '                    end if 
  130. '                    x=x+1
  131.                     objectwriteFile.Writeline(efix & vbTab & xing & vbTab & sion & vbTab & itle)
  132.                      txtRecordset.MoveNext
  133.                  Wend
  134.             End If
  135.  
  136.             objectwriteFile.Close
  137.             'msgbox "check the following table in client DB " & tab35
  138.             txtRecordSet.Close        
  139.             dim dsql
  140.             dsql = "DELETE * FROM " & tab35
  141.             tmpConnection.Execute(dsql)
  142.  
  143.         Next 
  144.  
  145.         tmpRecordSet.Close
  146.  
  147.         dim droptmptable
  148.         droptmptable = "DROP Table " & tab35
  149.         tmpConnection.Execute(droptmptable)
  150.  
  151.         objwriteFile.Close 
  152.         objRecordSet.Close
  153.         objConnection.Close
  154.  
  155.         varRecordSet.Close
  156.         varConnection.Close
  157.  
  158.         tmpConnection.Close
  159.  
  160.  
  161.         txtConnection.Close
  162.  
  163.     End Sub
May 11 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,470
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.
May 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.