Test databse

Option Compare Database
Option Explicit

Sub a()

    On Error GoTo Catch

    Dim d As New Database, r As Object, i As Variant

    DoCmd.SetWarnings False

    Application.SetOption "ANSI Query Mode", True

    DoCmd.RunSQL "CREATE TABLE tblSample (" _
        & "SampleId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "LotNumber VARCHAR NOT NULL, " _
        & "ItemCode INTEGER NOT NULL, " _
        & "ItemNumber INTEGER NOT NULL, " _
        & "ProductName VARCHAR NOT NULL, " _
        & "SampleQuantity DECIMAL(8, 3) NOT NULL, " _
        & "SampleUnit VARCHAR NOT NULL, " _
        & "SamplingDate DATETIME NOT NULL, " _
        & "ExpiryDate DATETIME, " _
        & "LocationIdFk INTEGER, " _
        & "CONSTRAINT ItemNumberKey UNIQUE (ItemNumber), " _
        & "CONSTRAINT LotNumberItemCodeKey UNIQUE (LotNumber, ItemCode))"
'   tblErrorLog
'   Id|3010|A következő tábla már létezik: 'tblSample'.|Sub a|TestDatabase
'   NOW|Environ$("USERNAME")|Environ$("COMPUTERNAME")

    DoCmd.RunSQL "CREATE TABLE tblRole (" & _
        "RoleId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " & _
        "RoleName VARCHAR NOT NULL, " & _
        "Description VARCHAR, " & _
        "CONSTRAINT RoleNamekey UNIQUE (RoleName))"
'   tblErrorLog
'   Id|3010|A következő tábla már létezik: 'tblRole'.|Sub a|TestDatabase
'   NOW|Environ$("USERNAME")|Environ$("COMPUTERNAME")

    With d
        .Query = "INSERT INTO tblRole (RoleName) VALUES (:RoleName)"

        .Bind ":RoleName", Format$(Now, "yyyymmddHhNnSs")
        .Bind ":RoleName", Format$(Now, "yyyymmddHhNnSs")
'       tblErrorLog
'       Id|-2147220991|Parameter exists(:RoleName)|Sub Bind|TestDatabase
'       NOW|Environ$("USERNAME")|Environ$("COMPUTERNAME")

        Set r = .ResultSet

        If CBool(.AffectedRows) Then _
            Debug.Print .AffectedRows & " row inserted" & vbCrLf

'   Query:
'       INSERT INTO tblRole (RoleName) VALUES (:RoleName)
'   Parameters:
'       : RoleName = 20160510211028
'   Transaction initiated
'   Executed: True
'   RecordCount: 0
'   AffectedRows: 1
'   Transaction committed
'
'   1 row inserted

        .Query = "SELECT s.LotNumber FROM " & _
            "tblSample s " & _
            "WHERE s.SampleId = :SampleId"

        .Bind ":SampleId", 147

        Set r = .ResultSet

        If CBool(.RecordCount) Then Debug.Print r("LotNumber") & vbCrLf

'   Query:
'       SELECT s.LotNumber FROM tblSample s WHERE s.SampleId = :SampleId
'   Parameters:
'       : SampleId = 147
'   Transaction initiated
'   Executed: True
'   RecordCount: 1
'   AffectedRows: 0
'   Transaction committed
'
'   LotNumber

        .Query = "SELECT s.LotNumber FROM " & _
            "tblSample s " & _
            "WHERE s.ItemCode = :ItemCode AND s.SamplingDate > :SamplingDate"

        .Bind ":ItemCode", 10002430
        .Bind ":SamplingDate", CDate("2015/1/1")

        Set r = .ResultSet

        If Not CBool(.RecordCount) Then GoTo Finally

        For Each i In r

            Debug.Print r(i)("LotNumber")

            DoEvents
        Next i

    End With

'   Query:
'       SELECT s.LotNumber FROM tblSample s WHERE s.ItemCode = :ItemCode
'       AND s.SamplingDate > :SamplingDate
'   Parameters:
'       : ItemCode = 10002430
'       :SamplingDate = 2015. 01. 01.
'   Transaction initiated
'   Executed: True
'   RecordCount: 3
'   AffectedRows: 0
'   Transaction committed
'
'   LotNumber
'   ...

    GoTo Finally

Catch:
    Handle Err.Number, Err.Description, "", "Sub a", _
        VBE.ActiveCodePane.CodeModule.Name
    Resume Next

Finally:
    Set d = Nothing
    Set r = Nothing
    Application.SetOption "ANSI Query Mode", False
    DoCmd.SetWarnings True

End Sub