Welcome !
         

script that adds record to a locall Access 2010 database

Author Message
digifoss

  • Total Posts : 6
  • Scores: 0
  • Reward points : 0
  • Joined: 12/30/2016
  • Status: offline
script that adds record to a locall Access 2010 database Friday, December 30, 2016 6:36 AM (permalink)
0
[Helpful answer received] / [List Solutions Only]
Hello, this is my first post.  
I have a script that I would like to use to add a record to an existing Access 2010 accdb file.  The access file path and name is C:\ecs\appendRecord.accdb and I have admin access to the path and the file.
When I run the script, nothing happens except the message box at the end saying Done - there are no errors, but the data does not get written into the accdb file and I don't believe the connection to the db is even being made.  In the SQL statement, the three values to be inserted are variables that contain text data, and I have verified the data does exist in the variables.  For brevity I left that part out as it is working.
Here is the part of the script that should write to my Access db:
'********
Dim connStr, objConn, msql
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\ecs\appendRecord.accdb; Persist Security Info=False;"
Set objConn = CreateObject("ADODB.Connection")
msql = "INSERT INTO Temperature (NDate, NTime, DegreesF)" &  _    "VALUES ( 'strDate', 'strTime', 'strTemp')"
objConn.open connStr
objConn.execute msql 
objConn.close
set objConn = nothing
MsgBox "Done!"
WScript.Quit
 
'********
So I added a test to the connection after the statement that opens it to see if the connection was really open or not.  It always returns true, or connected, but it still doesn't write data to the file. and no errirs either.
'********
Dim connStr, objConn, msql
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\ecs\appendRecord.accdb; Persist Security Info=False;"
Set objConn = CreateObject("ADODB.Connection")
msql = "INSERT INTO Temperature (NDate, NTime, DegreesF)" &  _    "VALUES ( 'strDate', 'strTime', 'strTemp')"
objConn.open connStr
        If (objConn.open = True) then
         msgbox "connected"
        else
         msgbox "connection failed"
          end if
objConn.execute msql 
objConn.close
set objConn = nothing
WScript.Quit
'***************
So then I moved the connection test to before the connection is opened.  And even here, the test says the connection is open, or connected !  Never an error message
'***************
Dim connStr, objConn, msql
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\ecs\appendRecord.accdb; Persist Security Info=False;"
Set objConn = CreateObject("ADODB.Connection")
msql = "INSERT INTO Temperature (NDate, NTime, DegreesF)" &  _    "VALUES ( 'strDate', 'strTime', 'strTemp')"
         If (objConn.open = True) then
         msgbox "connected"
        else
         msgbox "connection failed"
          end if
 
objConn.open connStr
 
objConn.execute msql 
objConn.close
set objConn = nothing
WScript.Quit
'***********************
I have adapted the script to successfully write to an Excel file but really need it to write to my Access db.  
 
I am running Windows 10 64bit, Access 2010 32 bit.  The table name in the access file is Temperature and the three fields I want to write to are text type.
 
 
#1
    59cobalt

    • Total Posts : 2585
    • Scores: 253
    • Reward points : 0
    • Joined: 7/17/2011
    • Status: offline
    Re:script that adds record to a locall Access 2010 database Friday, December 30, 2016 3:50 PM (permalink)
    5
    [This post was marked as helpful]
    VBScript doesn't expand variables in string literals (how would it distinguish a variable "foo" from the text "foo"?), hence your code inserts the literal strings "strDate", "strTime", and "strTemp". You could use string concatenation like this:
    msql = "INSERT INTO Temperature (NDate, NTime, DegreesF) VALUES ('" & _
           strDate & "','" & strTime & "','" & strTemp & "')"
    However, that makes you vulnerable to SQL injection, so don't do it. A better approach is to use prepared statements (or parameterized queries as Microsoft calls them):
    Set cmd  = CreateObject("ADODB.Command")
    cmd.ActiveConnection = objConn
    
    Set p1 = cmd.CreateParameter("@date" , 200, 1, 255, strDate)
    cmd.Parameters.Append p1
    Set p2 = cmd.CreateParameter("@time" , 200, 1, 255, strTime)
    cmd.Parameters.Append p2
    Set p3 = cmd.CreateParameter("@temp" , 200, 1, 255, strTemp)
    cmd.Parameters.Append p3
    
    cmd.CommandText = "INSERT INTO Temperature (NDate, NTime, DegreesF) VALUES (?,?,?)"
    cmd.Execute
    Make sure the data types of your parameters (200 stands for VarChar) match the data types of the fields in your database.
     
    #2
      digifoss

      • Total Posts : 6
      • Scores: 0
      • Reward points : 0
      • Joined: 12/30/2016
      • Status: offline
      Re:script that adds record to a locall Access 2010 database Saturday, December 31, 2016 5:44 AM (permalink)
      0
      Thanks 59cobalt.  Your correction on my SQL will definitely help with that part.  My more immediate problem though is I don't seem to be connecting to my db file at all, or at-least the test I am using is not very helpful.
      Even if I delete the database file and it no longer exists, the test to see if the connection to the accdb file is open or not still always returns true and tells me  the connection to the file is open which is impossible if the file is not there.  I don't know if there is a better way to test if the connection is open or not.
      If (objConn.open = True) then 
               msgbox "connected" 
              else 
               msgbox "connection failed" 
                end if 
       
       
      #3
        59cobalt

        • Total Posts : 2585
        • Scores: 253
        • Reward points : 0
        • Joined: 7/17/2011
        • Status: offline
        Re:script that adds record to a locall Access 2010 database Saturday, December 31, 2016 10:49 AM (permalink)
        0
        Check the value of the State property:
        objConn.Open connStr
        Select Case objConn.State
          Case 0: WScript.Echo "Closed"
          Case 1: WScript.Echo "Open"
          Case 2: WScript.Echo "Connecting"
          Case 4: WScript.Echo "Executing"
          Case 8: WScript.Echo "Fetching"
          Case Else: WScript.Echo "Unknown state: " & objConn.State
        End Select

         
        #4
          digifoss

          • Total Posts : 6
          • Scores: 0
          • Reward points : 0
          • Joined: 12/30/2016
          • Status: offline
          Re:script that adds record to a locall Access 2010 database Saturday, December 31, 2016 1:52 PM (permalink)
          0
          Thanks again for the response 59cobalt.  Again you have given me something helpful.  Using your connection method, now I am consistently getting Closed which I am sure is correct.   So now, it looks like there may be a problem with my connection string, or the way I am implementing it.  The path and filename to my accdb file is correct.  There is no username or password to my accdb file and I have admin permissions to the path and file.  All of the connection string examples I can find for connecting to Access 2010 show the same string I am using.  
          connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\ecs\appendRecord.accdb; Persist Security Info=False;" 
          I've tried using double slashes at the drive letter C:\\, and in the full path C:\\ecs\\ without success.  I get no errors.  Is there any way to verify the correct connection string or should I look elsewhere for the problem ?
           
          #5
            59cobalt

            • Total Posts : 2585
            • Scores: 253
            • Reward points : 0
            • Joined: 7/17/2011
            • Status: offline
            Re:script that adds record to a locall Access 2010 database Sunday, January 01, 2017 12:27 AM (permalink)
            0
            The connection string looks OK to me. Is the database password-protected? Can you open the database directly? Did you try other connection methods, e.g. the ODBC driver? Are you running the code on a 64-bit system? You don't have an On Error Resume Next somewhere in your code that you didn't show?
             
            #6
              digifoss

              • Total Posts : 6
              • Scores: 0
              • Reward points : 0
              • Joined: 12/30/2016
              • Status: offline
              Re:script that adds record to a locall Access 2010 database Sunday, January 01, 2017 5:09 AM (permalink)
              0
              I have figured out that the problem is the part of the file that connects to Access doesn't run properly with 32bit Wscript.exe which is the default.  If I open a console and explicity execute c:\Windows\syswow64\wscript.exe c:\ecs\myscript.vbs then it completes perfectly, inserts the data correctly and no errors.
               
              I have tried to set the default wscript to 64 bit by right clicking on my vbs file, then selecting choose default application to open with, then browsing to the 64 bit wscript.exe and setting it to always open with that, but when I double click the vbs file, the connection to Access fails - If I run it from the CLI it works.   Ill have to figure out how to set wscript to the 64bit version.
               
              #7
                59cobalt

                • Total Posts : 2585
                • Scores: 253
                • Reward points : 0
                • Joined: 7/17/2011
                • Status: offline
                Re:script that adds record to a locall Access 2010 database Sunday, January 01, 2017 1:44 PM (permalink)
                5
                [This post was marked as helpful]
                It's the 64-bit version of wscript.exe that doesn't work. C:\Windows\syswow64\wscript.exe is the 32-bit version of the interpreter. On 64-bit systems you must run VBScript code making database connections with the 32-bit interpreter.
                 
                #8
                  digifoss

                  • Total Posts : 6
                  • Scores: 0
                  • Reward points : 0
                  • Joined: 12/30/2016
                  • Status: offline
                  Re:script that adds record to a locall Access 2010 database Sunday, January 01, 2017 2:55 PM (permalink)
                  0
                  I see.  I have done quite a bit of vbs over the years, but here and there usually as I need to monitor something, not using it regularly so I'm usually rusty when I start something like this and wasn't aware of having to use the 32 bit version for databases.   What this is for is I have a couple of  temperature watch dog devices in two MDF's in different parts of town and wanted to start building some historical data on the room temperatures at regular intervals and Access is perfect for that.  What I have done with this is create a .cmd file that explicitly calls the \syswow64\wscript.exe file to run my script, put it in a scheduled task and it works just fine now, I am all set.  And I can easily start pulling and recording other data to the same table with just a small tweak to my script here and there. 
                  I greatly appreciate all of your assitance with this, you have provided me with some valuable infomation and saved me a lot of work and time.
                  Have a happy new year !  Can mark as solved !
                  Mike
                  <message edited by digifoss on Sunday, January 01, 2017 2:59 PM>
                   
                  #9
                    59cobalt

                    • Total Posts : 2585
                    • Scores: 253
                    • Reward points : 0
                    • Joined: 7/17/2011
                    • Status: offline
                    Re:script that adds record to a locall Access 2010 database Sunday, January 01, 2017 11:37 PM (permalink)
                    0
                    You're welcome. Happy new year to you too.
                     
                    #10
                      Online Bookmarks Sharing: Share/Bookmark

                      Jump to:

                      Current active users

                      There are 0 members and 2 guests.

                      Icon Legend and Permission

                      • New Messages
                      • No New Messages
                      • Hot Topic w/ New Messages
                      • Hot Topic w/o New Messages
                      • Locked w/ New Messages
                      • Locked w/o New Messages
                      • Read Message
                      • Post New Thread
                      • Reply to message
                      • Post New Poll
                      • Submit Vote
                      • Post reward post
                      • Delete my own posts
                      • Delete my own threads
                      • Rate post

                      2000-2017 ASPPlayground.NET Forum Version 3.9