title: vbscript insert array into access database
body: <p>I have a simple task with a complex procedure. I'm creating a tracking program that tracks the usage of applications, as well as other pertinent info. I'm first recording the data from the application in a temporary text file that will be deleted once the data is retrieved. The data is separated by commas so that it can be stored in a CSV file. This CSV file will be used to quickly pull specific pieces of information for review. The data will also be stored permanently in a 2010 Access database. I've been able to store the data in the text file. I've been able to create a VBScript to read the data from the text file and copy it to the CSV file. What I need to do is figure out why, when I've put a message box above the script that inserts the data to the database, I can see the info in the message box, but it won't print to the database and I'm not receiving any error messages.</p>
  
  <p>Here's the VBScript code:</p>
  
  <pre><code>' Set constants for reading, writing, and appending files
  Const ForReading = 1, ForWriting = 2, ForAppending = 8
  
  ' Sets up the object variables.
  Dim objExcel, objFSO, objTextFile, objCSVFile, objTrackingFolder
  
  ' Sets up the integer variables.
  Dim intPathYPos
  
  ' Sets up the all the string variables for the program.
  Dim Desktop, todaysDate, usageDate, myDay, myMonth, myYear, UserIDPath, myMessage
  Dim strTextFile, strHeadLine, strTextLine, strCSVFile, UserID, strTrackingFolder, strConnect, strSQL, strSplitData, testDisplay
  Dim message
  
  'This creates the required Objects
  Set objExcel = CreateObject("Excel.application")
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set conn = CreateObject("ADODB.Connection")
  Set WshShell = WScript.CreateObject("WScript.Shell")
  Desktop = WshShell.ExpandEnvironmentStrings("%USERPROFILE%") &amp; "\" &amp; "Desktop"
  UserIDPath = WshShell.ExpandEnvironmentStrings("%USERPROFILE%")
  
  '------------------Calls up the Process Tracking Submission sub-------------------
  
  call ProcessTrackingSubmission
  
  sub ProcessTrackingSubmission()
  
  intPathYPos = InStr(4,UserIDPath,"\")
  intPathYPos = intPathYPos + 1
  UserID = Mid(UserIDPath, intPathYPos, 10)
  
  'msgbox(RTrim(UserID))
  'exit sub
  
  ' Set date for date stamp in file name and sheet name
  todaysDate = Date()
  
  myMonth = Month(todaysDate)
  If Len(myMonth)=1 Then myMonth="0" &amp; myMonth
  
  myDay = Day(todaysDate)
  If Len(myDay)=1 Then myDay="0" &amp; myDay
  
  myYear = Right(Year(todaysDate), 2)
  
  usageDate = myMonth &amp; myDay &amp; myYear
  
  ' Set up the origin and destination files
  strTextFile = Desktop &amp; "\MacroTracker.txt"
  strTrackingFolder = "E:\My Storage Files\" &amp; UserID
  strCSVFile = strTrackingFolder &amp; "\TrackingTesting" &amp; usageDate &amp; ".csv"
  strHeadLine = "App Name,User ID,Ran At,Data 1,Data 2,Data 3,Data 4,Data 5,Data 6,Data 7,Data 8"
  
  Set objTextFile = objFSO.OpenTextFile(strTextFile)
  
  Wscript.Sleep 600
  
  ' Read the entire origin file
  Do Until objTextFile.AtEndOfStream
      strTextLine = objTextFile.ReadLine
  Loop
  
  Wscript.Sleep 600
  
  objTextFile.Close
  
  If (objFSO.FolderExists(strTrackingFolder)) Then
      If (objFSO.FileExists(strCSVFile)) Then
          ' Create object for appending current TXT file to CSV file
          Set objCSVFile = objFSO.OpenTextFile(strCSVFile, ForAppending, True)
      Else
          ' Create CSV file to write to with today's date
          Set objCSVFile = objFSO.CreateTextFile(strCSVFile, True)
          Wscript.Sleep 1000
          ' Write initial header for the CSV file
          objCSVFile.WriteLine strHeadLine
      End If
  Else
      Set objTrackingFolder = objFSO.CreateFolder(strTrackingFolder)
      If (objFSO.FileExists(strCSVFile)) Then
          ' Create object for appending current TXT file to CSV file
          Set objCSVFile = objFSO.OpenTextFile(strCSVFile, ForAppending, True)
      Else
          ' Create CSV file to write to with today's date
          Set objCSVFile = objFSO.CreateTextFile(strCSVFile, True)
          Wscript.Sleep 1000
          ' Write initial header for the CSV file
          objCSVFile.WriteLine strHeadLine
      End If
  End If
  
  ' Write an append line of data to the CSV file
  objCSVFile.WriteLine strTextLine
  
   Wscript.Sleep 600
  
  strDataLine = Split(strTextLine, ",")
  strAppName = strDataLine(0)
  strUserID = strDataLine(1)
  strRanAt = strDataLine(2)
  strData1 = strDataLine(3)
  strData2 = strDataLine(4)
  strData3 = strDataLine(5)
  strData4 = strDataLine(6)
  strData5 = strDataLine(7)
  strData6 = strDataLine(8)
  strData7 = strDataLine(9)
  strData8 = strDataLine(10)
  
  ' Connect to the database
  strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Storage Files\Tracking Apps.mdb"
  conn.Open strConnect
  
  Wscript.Sleep 600
  
  ' Write data to table
      if strAppName = "Hello Application - version 1" Then
  strSQL = "INSERT INTO [Macro Tracking] ([App Name], [User ID], [Ran At], [Data 2], [Data 6]) VALUES ([" &amp; strAppName &amp; "], [" &amp; strUserID &amp; "], [" &amp; strRanAt &amp; "], [" &amp; strData2 &amp; "], [" &amp; strData6 &amp; "])"
      end if
  
  Wscript.Sleep 600
  
  objCSVFile.Close
  conn.Close
  
  ' Wait for file to be written to
  Wscript.Sleep 600
  
  ' Delete origin file to prevent user tampering
  objFSO.DeleteFile(strTextFile)
  
  end sub
  </code></pre>
  
  <p>Any help would be greatly appreciated. I've worked with HTML databases, so I have an idea of what the SQL should look like, but I've never wrote one in VBScript and everything I've found on the Internet doesn't work.</p>