No announcement yet.

Database Access ?

  • Filter
  • Time
  • Show
Clear All
new posts

    Database Access ?

    I am sure this is a very elementary question, but it's one that has me completely stumped...
    Here is what I am trying to do :
    I use a templinc to monitor temperatures in a room, and use that information to turn a X10 univeral module on/off (controlling a vented propane fireplace). I would like to store these event on/off times into a Access database that I already programmed (some time ago) , in order to use this database to calculate and display aggregate on/off times of this fireplace.
    In trying to write a script that would do this, I tried using techniques for opening databases and writing to them that I used in programming in Microsoft Accesse's VBasic, and the techniques for accomplishing this in ASP web pages. Neither technique worked properly, or I am just doing this wrong....
    I attempted to look up these techniques in these forums, however, the few referances that I came across here referred (and linked) to threads in the OLD forums pages, and the links are no longer valid.

    Any ideas that might help me ?
    Thanx !

    It's been a few years since I've done it thru an ASP page, but last time I did it was thru a presetup ODBC connection under SystemDSN, linked to the Access database. I would then do all my SQL calls thru that DSN name. In VB I do it thru ADOX (though not alway the best way depending on who you talk to).



      Here is a page I setup a long time ago for setting up an ODBC connection to an Access db.

      Also if you look in the updater for a package called "Include Scripts", there is a file in there named db_functions.asp. In that file are a couple of functions called RunSelectQuery and RunActionQuery. You can see how to run SQL statements to insert data to the db in the RunActionQuery. Also update and delete work with RunActionQuery. RunSelectQuery shows how to connect to the db and run select statements. It will return the db records as an Array.

      You are welcome to use the include file as is or use it for an example.
      Jeff Farmer
      HS 3, HSPhone
      My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
      Other Plugins In Use: APCUPSD, BLOnkyo, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

      Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2



        I posted an example, but it's gone. I was in a hurry be beat Rupp to it. I haven't time to be really fancy again, so I'll just post the script where I'm using access as a database. The only thing that's a bit out of the ordinary is that one of the column names is in 'strdevice' I have columns in the database like 'A1', 'J2', etc. strDevice contains the column name and is passed into the script by the event that calls it. This is not original, I swiped the database code from someone else.

        EDIT: Looking at it, it's kinda lame in a couple of places: that iPos = Instr(strDeviceString, " ") ... code is just to trim leading spaces off the string. I think I shoulda used 'Trim'. Also, I should have set objConn = Nothing at the end, just for completeness.
        Option Explicit
        Const bDEBUG = False
        Const bNOSTORE = False
        Const strDatabaseName = "Environment.mdb"
        Dim objConn
        Sub main (strDevice)
        	Dim strConnectionString
        	Dim strDatabase
        	Dim strSQL
        	Dim strDeviceString
        	Dim strDeviceValue
        	Dim iPos
        	If bDEBUG Then 
        		Call hs.WriteLog ("StoreTemperature", "Script Start")
        	End If
        	strDatabase = hs.GetAppPath & "/" & strDatabaseName
        	strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDatabase
        	Set objConn = CreateObject("ADODB.Connection")
        	If Not bNOSTORE Then
        		objConn.Open (strConnectionString)
        	End If
        	strDeviceString = hs.DeviceString(strDevice)
        	iPos = Instr(strDeviceString, " ")
        	If iPos > 1 Then
        		strDeviceValue = Mid(strDeviceString, 1, iPos-1)
        		strDeviceValue = strDeviceString
        	End If
        	If bDEBUG Then 
        		Call hs.WriteLog ("StoreTemperature", "MDB Open, Connect String = " & strConnectionString)
        		Call hs.WriteLog ("StoreTemperature", "strDeviceString = " & strDeviceString)
        		Call hs.WriteLog ("StoreTemperature", "strDeviceValue = " & strDeviceValue)
        	End If
        	strSQL = "INSERT INTO env (DT, " & strDevice & ") VALUES (" & _
        			 "#" & FormatDateTime(Now, vbGeneralDate) & "#, " & _
        			 strDeviceValue & ")"
        	Call ExecSQL (strSQL)
        	If bDEBUG Then 
        		Call hs.WriteLog ("StoreTemperature", "Script End")
        	End If
        End Sub
        Sub ExecSQL (strSQL)
        	If bDEBUG Then 
        		Call hs.WriteLog ("StoreTemperature", "strSQL = " & strSQL)
        	End If
        	If Not bNOSTORE Then
        		objConn.Execute strSQL
        	End If
        End Sub


          Damn, you guys are the BEST ! LOL.....Let me tinker with this this evening, and I will let you guys know how it works out for me................Once again, my hat is off to you guys !


            ...or you can do it DSN-less:

            connstring = "DBQ=" & Server.Mappath("../_database/database.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

            These two more spesific should in theory be a little bit faster....
            Access 97
            connstring = "Data Source=" & Server.Mappath("../_database/database.mdb") & ";Provider=Microsoft.Jet.OLEDB.3.51;"

            Access 2000
            connstring = "Data Source=" & Server.Mappath("../_database/database.mdb") & ";Provider=Microsoft.Jet.OLEDB.4.0;"


              Ok, opening the database works fine....

              now, how does one enter event time and date info into different fields of the record...?
              First, do you not have to (open) the record for input ?
              enter the data...ex: Four fields in record....
              IndexRec (autonum)
              thrmostatON (enter the time of event that fired if turned on)
              ThermostatOFF(enter the time event that fired if turned OFF)
              ThermostatDATE(Enter the Date of event that fired)
              Do youy not also have to (Update) the record ?
              and finally,
              Does one have to (close) the database ?


                One post is hard enough to keep up with so let's close this one and use the other. How about it?

                New answers here:

                💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products


                  Just wanted to thank you guys for your help.....I Finally got my little programming job working, thanks to your assistance. It was truly confusing trying to apply the MS Access rules for programming in VB ie: docmd.parent.update[databasename], that kind of thing....Some of it was exactly as in MS Access, some completely different....thats what lead to the confusion.

                  Once again, thank you, guys ! It was a very educational experience !