sql - vbscript insert array into access database -

i have simple task complex procedure. i'm creating tracking program tracks usage of applications, other pertinent info. i'm first recording data application in temporary text file deleted once data retrieved. data separated commas can stored in csv file. csv file used pull specific pieces of information review. data stored permanently in 2010 access database. i've been able store data in text file. i've been able create vbscript read data text file , copy csv file. need figure out why, when i've put message box above script inserts data database, can see info in message box, won't print database , i'm not receiving error messages.

here's vbscript code:

' set constants reading, writing, , appending files const forreading = 1, forwriting = 2, forappending = 8  ' sets object variables. dim objexcel, objfso, objtextfile, objcsvfile, objtrackingfolder  ' sets integer variables. dim intpathypos  ' sets string variables 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 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 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 date stamp in file name , sheet name todaysdate = date()  mymonth = month(todaysdate) if len(mymonth)=1 mymonth="0" & mymonth  myday = day(todaysdate) if len(myday)=1 myday="0" & myday  myyear = right(year(todaysdate), 2)  usagedate = mymonth & myday & myyear  ' set origin , 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 entire origin file until objtextfile.atendofstream     strtextline = objtextfile.readline loop  wscript.sleep 600  objtextfile.close  if (objfso.folderexists(strtrackingfolder))     if (objfso.fileexists(strcsvfile))         ' create object appending current txt file csv file         set objcsvfile = objfso.opentextfile(strcsvfile, forappending, true)     else         ' create csv file write today's date         set objcsvfile = objfso.createtextfile(strcsvfile, true)         wscript.sleep 1000         ' write initial header csv file         objcsvfile.writeline strheadline     end if else     set objtrackingfolder = objfso.createfolder(strtrackingfolder)     if (objfso.fileexists(strcsvfile))         ' create object appending current txt file csv file         set objcsvfile = objfso.opentextfile(strcsvfile, forappending, true)     else         ' create csv file write today's date         set objcsvfile = objfso.createtextfile(strcsvfile, true)         wscript.sleep 1000         ' write initial header csv file         objcsvfile.writeline strheadline     end if end if  ' write append line of data 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 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 table     if strappname = "hello application - version 1" strsql = "insert [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 file written wscript.sleep 600  ' delete origin file prevent user tampering objfso.deletefile(strtextfile)  end sub 

any appreciated. i've worked html databases, have idea of sql should like, i've never wrote 1 in vbscript , i've found on internet doesn't work.

in addition roland pointed out, vbscript not expand variables inside strings. strsplitdata(0) in "... values (strsplitdata(0), ...)" literal string "strsplitdata(0)", not value of first field of array strsplitdata. build query string concatenation this:

strsql = "insert [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 not idea, forget mentioned it. better use 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 [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 arguments of createparameter() calls needed.