Need help with a VB Script using WMI scanning text file list of PCs and writing to Access Database - Part 2

Hi experts

I have a VB Script which scans a list of PCs (obtained from text file) and writes the results into an access db.
The script works, but when it comes to listing the applications installed, it lists them in the table downwards which causes a problem with the indexing (see below - Access Table Issue) - I would like them to be listed from left to right and not top to bottom - (see Required) - this would thus allow me to have a PC per line.... I do not know how to do this as this would require each table colum to have a column heading generated for the script to write to...

Access Table Issue: Current

ID      PCID      UserName      OS      SP Level      S/N      PC Model      Application      

1      PC1      test01      W2k      SP 4            Dell GX1    QuickTime                        
2                                                                        Microsoft Office 2000
3                                          Messenger      
4      PC2      test02      XP      SP 2      9TVDM0J      Dell C640      Diskeeper
5                                                                        Office XP

Required:

ID      PCID      UserName      OS      SP Level      S/N      PC Model      App1        App2          App3

1      PC1      test01      W2k      SP 4            Dell GX1    QuickTime        Messenger   Microsoft Office 2000
2      PC2      test02      XP      SP 2      9TVDM0J      Dell C640      Diskeeper        Office XP

Any ideas? Also any help on getting the Memory to show up as 256 MB rather than the complicated KB version would also be appreciated!!!

'*** Script Start ***

Option Explicit

' Declare the constants for the database connection, etc.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const FOR_READING = 1

' Declare the variables
Dim sComputer,strComputer,WSHShell, ofso, Sys
Dim oNet,oSoftware,objWMIService,objComputer
Dim cnSoftInv, rsData,aComputers, oComputer, oService
Dim colSoftware,sComputerName,oFile
Dim INPUT_FILE_NAME, Inputfolder, inputfile
Dim colComputers, colRunningServices, colComputer, colItems, colServices
Dim colBIOS, colosSettings,      colSettings, colmemItems
Dim objItem, objBIOS, SMBIOSBIOSVersion, memory, memoryString

Inputfolder = "D:\Temp\Test\"
inputfile = "AD_PCS_Test.txt"
INPUT_FILE_NAME = inputfolder & inputfile

Set ofso = CreateObject("Scripting.FileSystemObject")
Set WSHShell = WScript.CreateObject("WScript.Shell")
Set oFile = ofso.OpenTextFile(INPUT_FILE_NAME, FOR_READING)
Set oNet = CreateObject("WScript.Network")
Set cnSoftInv = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

cnSoftInv.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   & "User ID=Admin;Data Source=" & Inputfolder & "Audit.mdb"

cnSoftInv.Open

sComputerName = oFile.ReadAll
oFile.Close
aComputers = Split(sComputerName, vbCrLf)

For Each sComputerName In aComputers
   If sComputerName <> "" Then
      Wscript.Echo "Now attempting to process computer = " & sComputerName
      Call ServStat
   End If
Next

Set rsData = Nothing
cnSoftInv.Close
Set cnSoftInv = Nothing

'WScript.Quit


'****** Declare Subs ******

Sub ServStat

   rsData.Open "software", cnSoftInv, adOpenStatic, adLockOptimistic

   strComputer = sComputerName

   On Error Resume Next
   Set objWMIService = GetObject("winmgmts:" _
       & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

   If Err.Number <> 0 Then
      WScript.Echo "Unable to connect to computer= " & strComputer  & _
                   ", Error= " & Err.Number & ",  Descr= " & Err.Description
      Err.Clear
      On Error GoTo 0
      Exit Sub
   End If
   On Error GoTo 0

               Set colSoftware = objWMIService.ExecQuery("SELECT * FROM Win32_Product")        
            Set colComputer = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
            Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
            Set colBIOS = objWMIService.ExecQuery("Select * from Win32_BIOS")
            Set colosSettings = objWMIService.ExecQuery("Select * FROM Win32_OperatingSystem")
            Set colSettings = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem")
            Set colmemItems = objWMIService.ExecQuery("Select * from Win32_PhysicalMemoryArray")

      For Each objComputer In colComputer
          
          rsData.AddNew
          
          rsData.Fields("PCID").Value = sComputerName
          rsData.Fields("UserName").Value = objComputer.UserName
          
          rsData.Update
          
      Next
      
'*****************************************************************************************************
      For Each objItem in colItems
            rsData.Fields("OS").Value = objItem.Caption
            rsData.Fields("SP Level").Value = objItem.CSDVersion
            rsData.Update      
       Next
       
       For Each objBIOS in colBIOS
            rsData.Fields("Serial Number").Value = objBIOS.SerialNumber
            rsData.Update
       Next
 
       For Each objItem in colSettings
             If objItem.Model = "2373A90" Then
            rsData.Fields("PC Model").Value = objItem.Manufacturer & " T41"             
             Elseif objItem.Model = "2672QG3" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " X31"
             Elseif objItem.Model = "2673QG4" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " X31"
             Elseif objItem.Model = "2723BG4" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " R40"
             Elseif objItem.Model = "2724BG3" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " R40"
             Elseif objItem.Model = "2722AJU" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " R40"
             Elseif objItem.Model = "1831BG8" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " R51"
             Elseif objItem.Model = "8183V6G" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " S50"
             Elseif objItem.Model = "8183SJG" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " S50"
             Elseif objItem.Model = "8183RGW" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " S50"
             Elseif objItem.Model = "6275460" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " 300GL 6275-460"
             Elseif objItem.Model = "8183TGS" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " S50"
             Elseif objItem.Model = "6275300" Then
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " 300GL 6275-300"
             Else
             rsData.Fields("PC Model").Value = objItem.Manufacturer & " " & objItem.Model
             End If
             rsData.Update                                    
        Next          
       
       For Each objitem In colmemItems
             rsData.Fields("Memory").Value = objItem.MemoryDevices
            rsData.Update
       Next
        
    For Each objComputer In colComputer
             memory = " MB"
            rsData.Fields("Memory2").Value = objComputer.TotalPhysicalMemory
            memoryString = Round((objComputer.TotalPhysicalMemory / 1048576),0) & memory '" MB"
         
             rsData.Update      
      Next

'*****************************************************************************************************

      For Each oSoftware In colSoftware

    rsData.AddNew
                
             WScript.Echo "ComputerName: " & strComputer      
             
              rsData.Fields("Application").Value = oSoftware.Name
            'rsData.Fields("Install_Location").Value = oSoftware.InstallLocation
            'rsData.Fields("Install_Date").Value = oSoftware.InstallDate
            'rsData.Fields("Supplier").Value = oSoftware.Vendor
            'rsData.Fields("Version").Value = oSoftware.Version

       rsData.Update
      
       Next


   rsData.Close

End Sub


'*** Script End ***
Start Free Trial