Announcement

Collapse
No announcement yet.

HS3 write to MDB database?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • HS3 write to MDB database?

    Realizing that this may limit usage to Windows deployments only, I need this for a migration step..

    Question: Is it possible in HS3 to read-from and write-to MDB database files?

    In HS2 I used the CreateObject("ADODB.Connection"), create a recordset and query that.

    Cheers
    DJ

  • #2
    Yes look at System.Data.OleDb (there is some apparent Mono support also for a OleDB interface but never tried it) - there are some tutorials here http://www.visual-basic-tutorials.com/
    My Plugins:

    Pushover 3P | DoorBird 3P | Current Cost 3P | Velleman K8055 3P | LAMetric 3P | Garadget 3P | Hive 3P |
    Yeelight 3P | Nanoleaf 3P

    Comment


    • #3
      Really hope that someone can post an example.. I'm running into many issues & error messages.
      If someone has done this once it will save me a LOT of time.
      Of course I will document & share all my learnings afterwards!

      Comment


      • #4
        Post what you have so far and an example database and someone may be in a better position to help, it should not be overly difficult..
        My Plugins:

        Pushover 3P | DoorBird 3P | Current Cost 3P | Velleman K8055 3P | LAMetric 3P | Garadget 3P | Hive 3P |
        Yeelight 3P | Nanoleaf 3P

        Comment


        • #5
          It's perfectly possible to read and write to an Access mdb file in HS3.

          I assume you are doing this from within a script?

          The following bit of script code assumes that you have an mdb file located at \YourHS3RootFolder\Data\TestData\TestDb.mdb and will write some test data to the db each time its run.

          I have included a test mdb that works with the script which should be self explanatory.

          Let me know if you also need some help with the reading.

          Paul..

          Code:
          Sub Main(Optional ByVal Parms As String = "")
          
              Dim strDatabase As String = hs.GetAppPath & "\Data\TestData\TestDb.mdb"
              Dim strConnectionString As String
              Dim sql As String
              Dim objConn As Object
          
              strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDatabase
              objConn = CreateObject("ADODB.Connection")
              objConn.Open(strConnectionString)
          
              Try
                  sql = "insert into TestTable (TestField1, TestField2, TestField3) values ('" & Now.ToString & "','Some test data','Some more test data')"
                  objConn.execute(sql)
          
              Catch ex As Exception
                  hs.WriteLog("TestData", "Error " & ex.ToString)
          
              Finally
                  objConn.close()
                  objConn = Nothing
              End Try
          
          End Sub
          Attached Files

          Comment


          • #6
            Thank you for the Example!
            This is the stuff that takes me in the right direction!

            when running I see the following error message:

            Error Compiling script C:\ProgramFiles\etc\HS3\scripts\DJ_DBtest.vb: 'myConnection' is not declared. It may be inaccessible due to its protection level.
            Error Compiling script C:\ProgramFiles\etc\HS3\scripts\DJ_DBtest.vb: Type 'OleDBConnection' is not defined.
            Error Compiling script C:\ProgramFiles\etc\HS3\scripts\DJ_DBtest: End of statement expected.
            Error Compiling script C:\ProgramFiles\etc\HS3\scripts\DJ_DBtest.vb: Namespace or type specified in the Imports 'System.Core' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

            (file paths shortened for readability)


            My settings.ini:
            ScriptingReferences=System.Web;System.web.dll;System.Data.Ol edb;System.Core



            Any idea?

            Comment


            • #7
              Your references are a bit off...you don't have the names of the DLL's in there, System.Core should already be referenced.

              ScriptingReferences=System.Web;System.Web.dll;System.Data.Ol edb;System.Data.Oledb.dll

              however I am not sure with the version paul has posted that it uses .net methods to access the DB - what programming code are you using?
              My Plugins:

              Pushover 3P | DoorBird 3P | Current Cost 3P | Velleman K8055 3P | LAMetric 3P | Garadget 3P | Hive 3P |
              Yeelight 3P | Nanoleaf 3P

              Comment

              Working...
              X