vbscript insert array into access database

Question

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.

Here's the VBScript 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%") & "\" & "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" & myMonth

myDay = Day(todaysDate)
If Len(myDay)=1 Then myDay="0" & myDay

myYear = Right(Year(todaysDate), 2)

usageDate = myMonth & myDay & myYear

' Set up the origin and destination files
strTextFile = Desktop & "\MacroTracker.txt"
strTrackingFolder = "E:\My Storage Files\" & UserID
strCSVFile = strTrackingFolder & "\TrackingTesting" & usageDate & ".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 ([" & strAppName & "], [" & strUserID & "], [" & strRanAt & "], [" & strData2 & "], [" & strData6 & "])"
    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

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.

Answer 1

In addition to what roland already pointed out, VBScript does not expand variables inside strings. strSplitData(0) in "... VALUES (strSplitData(0), ...)" is just the literal string "strSplitData(0)", not the value of the first field of the array strSplitData. You could build the query string by concatenation like this:

strSQL = "INSERT INTO [Macro Tracking] " & _
         "([App Name], [User ID], [Ran At], [Data 1], [Data 2], " & _
         "[Data 3], [Data 4], [Data 5], [Data 6], [Data 7], [Data 8]) " & _
         "VALUES (" & _
         strSplitData(0) & ", " & _
         strSplitData(1) & ", " & _
         strSplitData(2) & ", " & _
         strSplitData(3) & ", " & _
         strSplitData(4) & ", " & _
         strSplitData(5) & ", " & _
         strSplitData(6) & ", " & _
         strSplitData(7) & ", " & _
         strSplitData(8) & ", " & _
         strSplitData(9) & ", " & _
         strSplitData(10) & ")"

However, doing this is not a good idea, so just forget I mentioned it. Better use a parameterized query (AKA prepared statement) instead:

db = "E:\My Storage Files\TrackingApps.mdb"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db & _
             ";User Id=admin;Password=;"

Set conn = CreateObject("ADODB.Connection")
conn.Open strConnect

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO [Macro Tracking] ([App Name], [User ID], " & _
  "[Ran At], [Data 1], [Data 2], [Data 3], [Data 4], [Data 5], [Data 6], " & _
  "[Data 7], [Data 8]) VALUES (?,?,?,?,?,?,?,?,?,?,?)"

Set p1 = cmd.CreateParameter("@p1", 3, 1, 0, 0)
cmd.Parameters("@p1") = strSplitData(0)
cmd.Parameters.Append p1

Set p2 = cmd.CreateParameter("@p2", 3, 1, 0, 0)
cmd.Parameters("@p2") = strSplitData(1)
cmd.Parameters.Append p2

...

Set p11 = cmd.CreateParameter("@p11", 3, 1, 0, 0)
cmd.Parameters("@p11") = strSplitData(10)
cmd.Parameters.Append p11

cmd.Execute

Adjust the arguments of the CreateParameter() calls as needed.

Answer 2

You define strSQL, but don't execute the SQL statement, add

conn.Execute strSQL

after the strSQL = ... line