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.

Roland Jansen · Accepted Answer · 2013-07-31 13:02:14Z

2

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

conn.Execute strSQL

after the strSQL = ... line

answered Jul 31, 2013 at 13:02

Roland Jansen

2,7811 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lou Over a year ago

Ok...just tried that, but now I'm receiving the following WScript Host error: 'Script: C:\Tracking App.vbs' 'Line: 114' 'Char: 2' 'Error: Command text was not set for the command object.' 'Code: 80040E0C' 'Source: Microsoft JET Database Engine' Any ideas how to fix this?

Roland Jansen Over a year ago

Add square brackets to the table and column name containing spaces: [Macro Tracking] [App Name] [User ID]...

Lou Over a year ago

Still receiving the same error message. Do I need to remove the commas and parenthesis? Line 114 is the strSQL = ..., so it's something wrong with that line.

Community · Accepted Answer · 2017-05-23 11:56:36Z

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.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jul 31, 2013 at 16:23

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

14 Comments

Lou Over a year ago

Ansgar, I tried what you said and it's saying I can't use this because of the provider I'm using. I'm using Microsoft.Jet.OLEDB.4.0...I would need to use Microsoft.Office.12.0.Access.Database.Engine, then recreate the database as Access 2010 doesn't allow you to change file types once you create a database (if it does, I haven't figured it out and already destroyed a .accdb database just to create a .mdb)

Ansgar Wiechers Over a year ago

I tested it with an Access 97 DB and the Microsoft.Jet.OLEDB.4.0 provider (using the connection string from here), and it worked just fine. Please update your question with the exact error message you're getting, and the line that raises the error.

Lou Over a year ago

The WSH error that I'm getting is on line 24: Set cmd.ActiveConnection = conn. The error code is 800A0E7D, the source is ADODB.Command, and the error is Requested operation requires and OLE DB Session object, which is not supported by the current provider. I added everything you sent above. The line for conn is Set conn = CreateObject("ADODB.Connection"). I even made sure everything has been Dim'd.

Ansgar Wiechers Over a year ago

@Lou You need to Open the connection first.

Lou Over a year ago

I have conn.Open strConnect, which is immediately after where I define strConnect with the provider and the path to the database. Should I change it to cmd.Open strConnect seeing as conn is consumed when setting cmd?

|

Collectives™ on Stack Overflow

vbscript insert array into access database

2 Answers 2

3 Comments

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related