'Script to update MS Access DB using %username%.txt
'Author: Felipe Ferreira

Option Explicit
On Error Resume Next

Const adOpenDynamic = 1, ForReading = 1, ForWriting = 2, adLockOptimistic = 3, ForAppending = 8 
Dim inputfile, outputfile, ifile, ofile
Dim linein, strColumn, strValues
Dim arrValues, strFile,TRecordsCount, RecordsCount, TRCount, FilesCount, r
Dim f,f1,fc
Dim strSQL,iloop
Dim WshNetwork : Set WshNetwork = WScript.CreateObject("WScript.Network")


Dim strScriptFile : strScriptFile = WScript.ScriptFullname
Dim strScriptPath : strScriptPath = Left(strScriptFile, Len(strScriptFile) - Len(WScript.Scriptname)) 'used to define the path from where the script file is located

'@@@@@@@@@@@@@@  @@@@@@@@@@@@@@@
Dim strDB : strDB = strScriptPath & "OK.mdb"            'name and location of access file
Dim strDriveLetter : strDriveLetter = "X:"
Dim strRemotePath : strRemotePath = "\\DMC00\NETLOGON\l"    'MapNetworkDrive and Folder shuold be edited!
outputfile = strScriptPath & "\logs.txt"						'LOG FILE
'@@@@@@@@@@@@@@    @@@@@@@@@@@@@@@	

Dim oFSO : Set oFSO = CreateObject("Scripting.FilesyStemObject")
Set ofile = oFso.OpenTextFile(Outputfile, ForAppending, TRUE)

'DB FIELDS
'--- DB FORMAT: SERVERNAME, LOGDIRECTION, DATA, ORA, USERNAME, LOGONSERVER
Dim arrFields(5)
arrFields(0) = "LOGDIRECTION"
arrFields(1) = "SERVERNAME"
arrFields(2) = "DATA"
arrFields(3) = "ORA"
arrFields(4) = "USERNAME"
arrFields(5) = "LOGONSERVER"

Dim iFSO : Set iFSO = CreateObject("Scripting.FilesyStemObject")
Dim fs : Set fs = CreateObject("Scripting.FileSystemObject")

'Map Drive,
WshNetwork.MapNetworkDrive strDriveLetter, strRemotePath  

'get each file open it and then run
If (fs.FolderExists(strDriveLetter & "\")) Then
	Set f = fs.GetFolder(strDriveLetter)
	'Set oFileCollection = strDriveLetter.Files 
	Set fc = f.Files
	'Ripeto le operazione contenute tra FOR e NEXT per ogni file ricavato
 For Each f1 in fc
	'Ricavo il nome del file
	    strFile=f1.Name
  If "txt" = fs.GetExtensionName(strFile) Then 	  
	    inputfile = strDriveLetter + strFile
		ofile.WriteLine "Processing file : " & inputfile
	    Set ifile = iFSO.OpenTextFile(inputfile)  				
	   Do until ifile.AtEndOfLine 
	        linein = ifile.ReadLine
	        ReDim arrValues(5)
	        arrValues = Split(linein, "#", 6 ) 
	'Cleanup servername(5) and time (3) to 00:00:00			
			arrValues(3) = Left(arrValues(3), 8) 			'format 8 chars 00:00:00
			arrValues(3) = trim(arrValues(3))	
			arrValues(3) = Replace(arrValues(3), ".", ":")  'change 00.00.00 to 00:00:00
	       	arrValues(3) = trim(arrValues(3))               'remove empty space 
			
			arrValues(5) = Replace(arrValues(5), "\\", "")	
			arrValues(5) = trim(arrValues(5))
			
	'more cleanup if date is Thu 04/26/2007 need to be 26/04/2007
	        If Len(arrValues(2)) > 10 Then
	          Dim mm, dd, yy, strdate
	          arrValues(2)= Right(arrValues(2), 10 )  
	          mm = Left(arrValues(2), 2)
	          dd = Mid(arrValues(2), 4,2)
	          yy = Right(arrValues(2), 4) 
	          arrValues(2) = dd & "/" & mm & "/" & yy
			End If		
		'CALLS THE FUNCTION PASSING BOTH ARRAYS			
			 updateDB arrFields, arrValues		     
		     RecordsCount = RecordsCount + 1
	    Loop 'each line read			
			ifile.Close 'finish reading the file so close it
	        f1.delete 'delete the file
			FilesCount = FilesCount + 1
			TRecordsCount = RecordsCount + TRecordsCount
	end if 'file is .txt	
  Next 'go to next file
	
end if 'Drive Exists
ofile.WriteLine "Date: " & date() & " Time: " & time()
ofile.WriteLine "Total records inserted in DB: " & TRecordsCount
ofile.WriteLine "Total Files processed: " & FilesCount
ofile.WriteLine "----------------------------------------------"
ofile.close
WShNetwork.RemoveNetworkDrive strDriveLetter, true
wscript.quit

'@@@@@@@@@@@@@@@@@@@@@@@@@ FUNCTIONS@@@@@@@@@@@@@@@@@@@@@@@@@@@@

function UpdateDB(arr1,arr2)
	
	Dim objConn : Set objConn = CreateObject("ADODB.Connection")
	objConn.Provider="Microsoft.Jet.OLEDB.4.0"
	objConn.Open strDB
	Dim objRS : Set objRS = CreateObject("ADODB.Recordset")
	strSQL = "SELECT * FROM LOG"
	objRS.Open strSQL, objConn, 3, 3
	objRS.AddNew arr1, arr2
	objRS.Update
	objRS.Close
	'wscript.echo "DB Updated"
	If err.number <> 0 Then 
	 ofile.writeline "Error occurred connecting to the database: " & err.number
	 exit function
	end if
	objConn.Close
	set objRS = Nothing
	set objConn = Nothing
End Function