Dim con As New oledb.oledbconnection("PROVIDER=Microsoft.jet.oledb.4.0;Data Source = C:\Program Files (x86)\HomeSeer HS3\scripts\sample.mdb")

Const sRefMapFileName = "HS2_2_HS3_ref_map.ini"

Sub Main(ByVal parm As Object)
    Dim Cmd As OleDb.OleDbCommand
    Dim Reader As OleDb.OleDbDataReader

    Dim dv As Scheduler.Classes.DeviceClass
    Dim EN As Scheduler.Classes.clsDeviceEnumeration
    Dim sName As String
    Dim sLocation As String
    Dim sLocation2 As String
    Dim sql As String
    Dim s As String

    Try
        hs.writelog("HS2->HS3", "# Start Mapping Devices By Name #")

        con.open()

        EN = hs.GetDeviceEnumerator

        dv = EN.GetNext
        Do While Not EN.finished

            sName = trim(dv.name(Nothing))
            sLocation = trim(dv.Location(Nothing))
            sLocation2 = trim(dv.Location2(Nothing))

            ' Count 
            sql = "Select COUNT(*) AS Ant FROM Devices WHERE name = '" & sName & "' AND location='" & sLocation & "' AND location2='" & sLocation2 & "'"
            Cmd = New OleDb.OleDbCommand(sql, Con)
            Reader = Cmd.ExecuteReader()

            If Reader.Read() Then
                If Reader.Item("Ant") > 1 Then
                    hs.writelog("HS2->HS3 Error", "More than ONE device found with this Name: " & sName & " AND Location: " & sLocation & " AND Location2: " & sLocation2)
                ElseIf Reader.Item("Ant") = 1 Then
                    ' Get data
                    sql = "Select * FROM Devices WHERE name = '" & sName & "' AND Location='" & sLocation & "' AND Location2='" & sLocation2 & "'"
                    Cmd = New OleDb.OleDbCommand(sql, Con)
                    Reader = Cmd.ExecuteReader()

                    If Reader.Read() Then

                        ' Ref mapping
                        hs.writelog("HS2->HS3", "Mapping Device: " & sName & " AND Location: " & sLocation & " AND Location2: " & sLocation2)
                        s = Reader.Item("ref").ToString
                        hs.SaveINISetting("Ref HS2 to HS3", s, dv.Ref(Nothing), sRefMapFileName)
                        hs.SaveINISetting("Ref HS3 to HS2", dv.Ref(Nothing), s, sRefMapFileName)
                    End If
                Else
                    hs.writelog("HS2->HS3 Error", "Found Nr of devices = " & Reader.Item("Ant").tostring & " in HS2 database for this Name = '" & sName & "' AND Location='" & sLocation & "' AND Location2='" & sLocation2 & "'")
                End If
            Else
                hs.writelog("HS2->HS3 Error", "Cant find Device with this Name: " & sName & " AND Location: " & sLocation & " AND Location2: " & sLocation2)
            End If

            ' text
            dv = EN.GetNext
        Loop

        con.close()

        hs.writelog("HS2->HS3", "# Done Mapping Devices By Name #")


    Catch ex As Exception
        hs.WriteLog("HS2->HS3 Error", "Exception in script: " & ex.Message)
    End Try
End Sub