Announcement

Collapse
No announcement yet.

script to access a Access mdb

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

    script to access a Access mdb

    I've used VB6 to access a mdb but am not sure on the proper way to do it in Homeseer.
    I would like to open a mdb, issue a SQl query to return results in a recordset, then pull the values from the recordset for use in some logic.

    Could someone post a few snippets of the best way to do this?

    How and where do you normally define the database name, path etc.

    Thanks to all for your help.

    #2
    This is what I use to pull data from a recipe database:

    <pre class="ip-ubbcode-code-pre">
    EZrecipesupport_DB = "C:\Program Files\Easy Recipe Deluxe\support.mdb"

    'Create DSN Less connection to Access Database
    'Create DBConnection Object
    Set DBConnection1a = CreateObject("adodb.connection")
    Set DBConnection2a = CreateObject("adodb.connection")
    Set DBConnection3a = CreateObject("adodb.connection")
    DSN = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source='" & EZrecipesupport_DB & "'"
    DBConnection1a.Open DSN
    DBConnection2a.Open DSN
    DBConnection3a.Open DSN

    'Create a Query
    SQL1a = "SELECT Categories.Cat FROM Categories ORDER BY Categories.Cat"
    SQL2a = "SELECT Main.Main FROM Main ORDER BY Main.Main"
    SQL3a = "SELECT Style.Style FROM Style ORDER BY Style.Style"
    'Get a Record Set
    Set RS1a = DBConnection1a.Execute( SQL1a )
    Set RS2a = DBConnection2a.Execute( SQL2a )
    Set RS3a = DBConnection3a.Execute( SQL3a )

    response.send (vbCrLf)
    response.send ("&lt;h3&gt;Search for Recipes&lt;/h3&gt;")
    response.send (vbCrLf)

    response.send ("&lt;form action='ezrecipe1.asp' method='get'&gt;By Category&lt;BR&gt;&lt;select class='formdropdown' name='category' size='1'&gt;&lt;option value=''&gt;&nbsp&lt;/option&gt;")
    response.send (vbCrLf)

    Do While Not RS1a.EOF 'Check for end of recordset.
    Response.send "&lt;option value=""" & RS1a(0) & """&gt;" & RS1a(0) & "&lt;/option&gt;"
    response.send (vbCrLf)
    RS1a.MoveNext
    Loop
    response.send ("&lt;/select&gt;&lt;input type='submit' value='Go' class='formbutton'&gt;&lt;br&gt;&lt;/form&gt;" & vbCrLf)
    response.send (vbCrLf)

    response.send ("&lt;form action='ezrecipe1.asp' method='get'&gt;by Main Ingredient&lt;BR&gt;&lt;select class='formdropdown' name='main' size='1'&gt;&lt;option value=''&gt;&nbsp&lt;/option&gt;")
    response.send (vbCrLf)
    Do While Not RS2a.EOF 'Check for end of recordset.
    Response.send "&lt;option value=""" & RS2a(0) & """&gt;" & RS2a(0) & "&lt;/option&gt;"
    response.send (vbCrLf)
    RS2a.MoveNext
    Loop
    response.send ("&lt;/select&gt;&lt;input type='submit' value='Go' class='formbutton'&gt;&lt;br&gt;&lt;/form&gt;" & vbCrLf)
    response.send (vbCrLf)

    response.send ("&lt;form action='ezrecipe1.asp' method='get'&gt;by Style&lt;BR&gt;&lt;select class='formdropdown' name='style' size='1'&gt;&lt;option value=''&gt;&nbsp&lt;/option&gt;")
    response.send (vbCrLf)
    Do While Not RS3a.EOF 'Check for end of recordset.
    Response.send "&lt;option value=""" & RS3a(0) & """&gt;" & RS3a(0) & "&lt;/option&gt;"
    response.send (vbCrLf)
    RS3a.MoveNext
    Loop
    response.send ("&lt;/select&gt;&lt;input type='submit' value='Go' class='formbutton'&gt;&lt;br&gt;&lt;/form&gt;" & vbCrLf)
    response.send (vbCrLf)

    Set RS1a = nothing
    DBConnection1a.Close

    Set RS2a = nothing
    DBConnection2a.Close

    Set RS3a = nothing
    DBConnection3a.Close
    </pre>

    Hope that helps.

    Cheers
    Al
    HS 4.2.8.0: 2134 Devices 1252 Events
    Z-Wave 3.0.10.0: 133 Nodes on one Z-Net

    Comment


      #3
      You can get buy with just one ADODB.Connection object instead of the three you use.
      Plug-ins: UltraMon, UltraM1G, UltraCID, Ultra1Wire, UltraLog, UltraWeatherBug, UltraPioneerAVR, UltraGCIR

      Comment


        #4
        Thanks for clarifying that. Made the code a bit faster too [img]/infopop/emoticons/icon_biggrin.gif[/img]

        Cheers
        Al
        HS 4.2.8.0: 2134 Devices 1252 Events
        Z-Wave 3.0.10.0: 133 Nodes on one Z-Net

        Comment

        Working...
        X