sample database

Option Compare Database
Option Explicit

Public Sub CreateRelations()

    On Error GoTo Catch

    DoCmd.SetWarnings False

    Application.SetOption "ANSI Query Mode", True

    DoCmd.RunSQL "CREATE TABLE tblErrorLog (" _
        & "ErrorId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "ErrorNumber INTEGER NOT NULL, " _
        & "ErrorDescription LONGTEXT NOT NULL, " _
        & "ProcedureName VARCHAR NOT NULL, " _
        & "ModuleName VARCHAR NOT NULL, " _
        & "ErrorTimeStamp DATETIME NOT NULL, " _
        & "UserName VARCHAR NOT NULL, " _
        & "ComputerName VARCHAR NOT NULL)"

    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))"

    DoCmd.RunSQL "CREATE TABLE tblLocation (" _
        & "LocationId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "LocationName VARCHAR NOT NULL, " _
        & "Closed YESNO DEFAULT 0, " _
        & "CONSTRAINT LocationKey UNIQUE (LocationName))"

    DoCmd.RunSQL "CREATE TABLE tblComplaint (" _
        & "ComplaintId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "TrackWiseNumber INTEGER NOT NULL, " _
        & "Title VARCHAR NOT NULL, " _
        & "Description TEXT, " _
        & "Task VARCHAR NOT NULL, " _
        & "Complainer VARCHAR NOT NULL, " _
        & "ComingDate DATETIME NOT NULL DEFAULT NOW, " _
        & "ExaminationDate DATETIME, " _
        & "LastExaminationDate DATETIME, " _
        & "Done DATETIME, " _
        & "CONSTRAINT TrackWiseNumberKey UNIQUE (TrackWiseNumber))"

    DoCmd.RunSQL "CREATE TABLE tblComplaintSample (" _
        & "SampleIdFk INTEGER NOT NULL, " _
        & "ComplaintIdFk INTEGER NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (SampleIdFk, ComplaintIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblRequest (" _
        & "RequestId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "ComingDate DATETIME NOT NULL, " _
        & "Title VARCHAR NOT NULL, " _
        & "Description TEXT, " _
        & "RequireDate DATETIME NOT NULL, " _
        & "AuthorIdFk INTEGER NOT NULL, " _
        & "CreatedAt DATETIME NOT NULL DEFAULT NOW, " _
        & "Done DATETIME)"

    DoCmd.RunSQL "CREATE TABLE tblUser (" _
        & "UserId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "UserName VARCHAR NOT NULL, " _
        & "UserPassword VARCHAR NOT NULL, " _
        & "FirstName VARCHAR NOT NULL, " _
        & "LastName VARCHAR NOT NULL, " _
        & "LoggedIn DATETIME, " _
        & "Email VARCHAR NOT NULL, " _
        & "CONSTRAINT UserNameKey UNIQUE (UserName))"

    DoCmd.RunSQL "CREATE TABLE tblRequestSample (" _
        & "RequestIdFk INTEGER NOT NULL, " _
        & "SampleIdFk INTEGER NOT NULL, " _
        & "RequestQuantity DECIMAL(8, 3) NOT NULL, " _
        & "RequestUnit VARCHAR NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (SampleIdFk, RequestIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblComplaintRequest (" _
        & "ComplaintIdFk INTEGER NOT NULL, " _
        & "RequestIdFk INTEGER NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (ComplaintIdFk, RequestIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblRole (" _
        & "RoleId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "RoleName VARCHAR NOT NULL, " _
        & "Description VARCHAR, " _
        & "CONSTRAINT RoleNamekey UNIQUE (RoleName))"

    DoCmd.RunSQL "CREATE TABLE tblPermission (" _
        & "PermissionId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "PermissionName VARCHAR NOT NULL, " _
        & "Description VARCHAR, " _
        & "CONSTRAINT PermissionNameKey UNIQUE (PermissionName))"

    DoCmd.RunSQL "CREATE TABLE tblRolePersmission (" _
        & "RoleIdFk INTEGER NOT NULL, " _
        & "PermissionIdFk INTEGER NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (RoleIdFk, PermissionIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblProperty (" _
        & "PropertyId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "PropertyName VARCHAR NOT NULL, " _
        & "CONSTRAINT PropertyNameKey UNIQUE (PropertyName))"

    DoCmd.RunSQL "CREATE TABLE tblProductProperty (" _
        & "ProductIdFk INTEGER NOT NULL, " _
        & "PropertyIdFk INTEGER NOT NULL, " _
        & "PropertyValue DECIMAL(8, 3), " _
        & "Unit VARCHAR, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (ProductIdFk, PropertyIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblUserRole (" _
        & "UserIdFk INTEGER NOT NULL, " _
        & "RoleIdFk INTEGER NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (UserIdFk, RoleIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblProduct (" _
        & "ProductId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "ItemCode INTEGER NOT NULL, " _
        & "ProductName VARCHAR NOT NULL, " _
        & "ProductTypeIdFk INTEGER NOT NULL, " _
        & "SupplierCode VARCHAR NOT NULL, " _
        & "SupplierName VARCHAR NOT NULL, " _
        & "SupplierStatusIdFk INTEGER NOT NULL, " _
        & "SamplingMethodIdFk INTEGER NOT NULL, " _
        & "TestingLevelIdFk INTEGER NOT NULL, " _
        & "SamplePlan VARCHAR NOT NULL, " _
        & "IdentificationIdFk INTEGER NOT NULL DEFAULT 1, " _
        & "CONSTRAINT ItemCodeAndSupplierCodeKey UNIQUE (ItemCode, SupplierCode))"

    DoCmd.RunSQL "CREATE TABLE tblProductType (" _
        & "TypeId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "TypeName VARCHAR NOT NULL, " _
        & "CONSTRAINT TypeNamekey UNIQUE (TypeName))"

    DoCmd.RunSQL "CREATE TABLE tblSupplierStatus (" _
        & "StatusId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "StatusName VARCHAR NOT NULL, " _
        & "CONSTRAINT StatusNamekey UNIQUE (StatusName))"

    DoCmd.RunSQL "CREATE TABLE tblSamplingMethod (" _
        & "MethodId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "MethodName VARCHAR NOT NULL, " _
        & "CONSTRAINT MethodNamekey UNIQUE (MethodName))"

    DoCmd.RunSQL "CREATE TABLE tblImage (" _
        & "ImageId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "ImageName VARCHAR NOT NULL, " _
        & "ImagePath VARCHAR NOT NULL)"

    DoCmd.RunSQL "CREATE TABLE tblProductImage (" _
        & "ProductIdFk INTEGER NOT NULL, " _
        & "ImageIdFk INTEGER NOT NULL, " _
        & "CONSTRAINT PrimaryKey PRIMARY KEY (ProductIdFk, ImageIdFk))"

    DoCmd.RunSQL "CREATE TABLE tblTestingLevel (" _
        & "LevelId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "LevelName VARCHAR NOT NULL, " _
        & "CONSTRAINT LevelNamekey UNIQUE (LevelName))"

    DoCmd.RunSQL "CREATE TABLE tblIdentification (" _
        & "IdentificationId AUTOINCREMENT(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY, " _
        & "InstrumentName VARCHAR NOT NULL, " _
        & "CONSTRAINT InstrumentNamekey UNIQUE (InstrumentName))"
'   ****************************************************************************
    DoCmd.RunSQL "CREATE INDEX ItemCodeKey ON tblSample (ItemCode)"

    DoCmd.RunSQL "CREATE INDEX DoneKey ON tblComplaint (Done)"

    DoCmd.RunSQL "CREATE INDEX ComingDateKey ON tblRequest (ComingDate)"

    DoCmd.RunSQL "CREATE INDEX TitleKey ON tblRequest (Title)"

    DoCmd.RunSQL "CREATE INDEX DoneKey ON tblRequest (Done)"
'   ****************************************************************************
    DoCmd.RunSQL "ALTER TABLE tblSample " _
        & "ADD CONSTRAINT fkIdOfLocationForSample " _
        & "FOREIGN KEY (LocationIdFk) REFERENCES " _
        & "tblLocation (LocationId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblComplaintSample " _
        & "ADD CONSTRAINT fkIdOfsampleForComplaintSample " _
        & "FOREIGN KEY (SampleIdFk) REFERENCES " _
        & "tblSample (SampleId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblComplaintSample " _
        & "ADD CONSTRAINT fkIdOfComplaintForComplaintSample " _
        & "FOREIGN KEY (ComplaintIdFk) REFERENCES " _
        & "tblComplaint (ComplaintId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblRequest " _
        & "ADD CONSTRAINT fkIdOfUserForRequest " _
        & "FOREIGN KEY (AuthorIdFk) REFERENCES " _
        & "tblUser (UserId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblRequestSample " _
        & "ADD CONSTRAINT fkIdOfSampleForRequestSample " _
        & "FOREIGN KEY (SampleIdFk) REFERENCES " _
        & "tblSample (SampleId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblRequestSample " _
        & "ADD CONSTRAINT fkIdOfRequestForRequestSample " _
        & "FOREIGN KEY (RequestIdFk) REFERENCES " _
        & "tblRequest (RequestId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblComplaintRequest " _
        & "ADD CONSTRAINT fkIdOfComplaintForComplaintRequest " _
        & "FOREIGN KEY (ComplaintIdFk) REFERENCES " _
        & "tblComplaint (ComplaintId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblComplaintRequest " _
        & "ADD CONSTRAINT fkIdOfRequestForComplaintRequest " _
        & "FOREIGN KEY (RequestIdFk) REFERENCES " _
        & "tblRequest (RequestId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblRolePersmission " _
        & "ADD CONSTRAINT fkIdOfRoleForRolePermission " _
        & "FOREIGN KEY (RoleIdFk) REFERENCES " _
        & "tblRole (RoleId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblRolePersmission " _
        & "ADD CONSTRAINT fkIdOfPermissioForRolePermission " _
        & "FOREIGN KEY (PermissionIdFk) REFERENCES " _
        & "tblPermission (PermissionId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblUserRole " _
        & "ADD CONSTRAINT fkIdOfUserForUserRole " _
        & "FOREIGN KEY (UserIdFk) REFERENCES " _
        & "tblUser (UserId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblUserRole " _
        & "ADD CONSTRAINT fkIdOfRoleForUserRole " _
        & "FOREIGN KEY (RoleIdFk) REFERENCES " _
        & "tblRole (RoleId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblProduct " _
        & "ADD CONSTRAINT fkIdOfProductTypeForProduct " _
        & "FOREIGN KEY (ProductTypeIdFk) REFERENCES " _
        & "tblProductType (TypeId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblProduct " _
        & "ADD CONSTRAINT fkIdOfSupplierStatusForProduct " _
        & "FOREIGN KEY (SupplierStatusIdFk) REFERENCES " _
        & "tblSupplierStatus (StatusId))"

    DoCmd.RunSQL "ALTER TABLE tblProduct " _
        & "ADD CONSTRAINT fkIdOfSamplingMethodForProduct " _
        & "FOREIGN KEY (SamplingMethodIdFk) REFERENCES " _
        & "tblSamplingMethod (MethodId)"

    DoCmd.RunSQL "ALTER TABLE tblProduct " _
        & "ADD CONSTRAINT fkIdOfTestingLevelForProduct " _
        & "FOREIGN KEY (TestingLevelIdFk) REFERENCES " _
        & "tblTestingLevel (LevelId)"

    DoCmd.RunSQL "ALTER TABLE tblProduct " _
        & "ADD CONSTRAINT fkIdOfIdentificationForProduct " _
        & "FOREIGN KEY (IdentificationIdFk) REFERENCES " _
        & "tblIdentification (IdentificationId)"

    DoCmd.RunSQL "ALTER TABLE tblProductImage " _
        & "ADD CONSTRAINT fkIdOfProductForProductImage " _
        & "FOREIGN KEY (ProductIdFk) REFERENCES " _
        & "tblProduct (ProductId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblProductImage " _
        & "ADD CONSTRAINT fkIdOfImageForProductImage " _
        & "FOREIGN KEY (ImageIdFk) REFERENCES " _
        & "tblImage (ImageId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblProductProperty " _
        & "ADD CONSTRAINT fkIdOfProductForProductProperty " _
        & "FOREIGN KEY (ProductIdFk) REFERENCES " _
        & "tblProduct (ProductId) ON UPDATE CASCADE ON DELETE CASCADE"

    DoCmd.RunSQL "ALTER TABLE tblProductProperty " _
        & "ADD CONSTRAINT fkIdOfPropertyForProductProperty " _
        & "FOREIGN KEY (PropertyIdFk) REFERENCES " _
        & "tblProperty (PropertyId) ON UPDATE CASCADE ON DELETE CASCADE"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblRole (RoleName) VALUES ('Administrator')"

    DoCmd.RunSQL "INSERT INTO tblRole (RoleName) VALUES ('Quester')"

    DoCmd.RunSQL "INSERT INTO tblRole (RoleName) VALUES ('Complainer')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('CreateRequest')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('ViewRequest')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('UpdateRequest')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('DeleteRequest')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('CreateComplaint')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('ViewComplaint')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('UpdateComplaint')"

    DoCmd.RunSQL "INSERT INTO tblPermission (PermissionName) VALUES ('DeleteComplaint')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Quester') AND ((p.PermissionName) = 'CreateRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Quester') AND ((p.PermissionName) = 'ViewRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Quester') AND ((p.PermissionName) = 'UpdateRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Quester') AND ((p.PermissionName) = 'DeleteRequest'))"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Complainter') AND ((p.PermissionName) = 'CreateComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Complainter') AND ((p.PermissionName) = 'ViewComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Complainter') AND ((p.PermissionName) = 'UpdateComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Complainter') AND ((p.PermissionName) = 'DeleteComplaint'))"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'CreateRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'ViewRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'UpdateRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'DeleteRequest'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'CreateComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'ViewComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'UpdateComplaint'))"

    DoCmd.RunSQL "INSERT INTO tblRolePersmission (RoleIdFk, PermissionIdFk) " _
        & "SELECT r.RoleId, p.PermissionId " _
        & "FROM tblRole AS r, tblPermission AS p " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((p.PermissionName) = 'DeleteComplaint'))"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblUser (UserName, UserPassword, FirstName, LastName, Email) " _
        & "VALUES ('DFSFDF', 'SDFSDFFSFSDF', 'SDFSDF', 'SDFSF', 'sdfsf.sdfsdf@sdf.sdf')"

    DoCmd.RunSQL "INSERT INTO tblUser (UserName, UserPassword, FirstName, LastName, Email) " _
        & "VALUES ('DFSDF', 'SDFSDFSFSF', 'SDFSFD', 'SDFSFSF', 'dfgdgdf.ertet@ert.ert')"

    DoCmd.RunSQL "INSERT INTO tblUser (UserName, UserPassword, FirstName, LastName, Email) " _
        & "VALUES ('DFGDFG', 'DFGDGDG', 'DFGDGDG', 'DFGDGFDG', 'dfgdg.dfgdfg@dfgd.dfg')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblUserRole (UserIdFk, RoleIdFk) " _
        & "SELECT u.UserId, r.RoleId " _
        & "FROM tblUser AS u, tblRole AS r " _
        & "WHERE (((r.RoleName) = 'Administrator') AND ((u.UserName) = 'DFSFDF'))"

    DoCmd.RunSQL "INSERT INTO tblUserRole (UserIdFk, RoleIdFk) " _
        & "SELECT u.UserId, r.RoleId " _
        & "FROM tblUser AS u, tblRole AS r " _
        & "WHERE (((r.RoleName) = 'Complainer') AND ((u.UserName) = 'DFSDF'))"

    DoCmd.RunSQL "INSERT INTO tblUserRole (UserIdFk, RoleIdFk) " _
        & "SELECT u.UserId, r.RoleId " _
        & "FROM tblUser AS u, tblRole AS r " _
        & "WHERE (((r.RoleName) = 'Quester') AND ((u.UserName) = 'DFGDFG'))"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblProductType (TypeName) VALUES ('FP')"

    DoCmd.RunSQL "INSERT INTO tblProductType (TypeName) VALUES ('PKGP')"

    DoCmd.RunSQL "INSERT INTO tblProductType (TypeName) VALUES ('PKGS')"

    DoCmd.RunSQL "INSERT INTO tblProductType (TypeName) VALUES ('RM')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('ALL')"

    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('FIVE')"

    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('NIL')"

    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('ONE')"

    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('QUAN')"

    DoCmd.RunSQL "INSERT INTO tblSamplingMethod (MethodName) VALUES ('ROOTN')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblSupplierStatus (StatusName) VALUES ('E')"

    DoCmd.RunSQL "INSERT INTO tblSupplierStatus (StatusName) VALUES ('K')"

    DoCmd.RunSQL "INSERT INTO tblSupplierStatus (StatusName) VALUES ('M')"

    DoCmd.RunSQL "INSERT INTO tblSupplierStatus (StatusName) VALUES ('NIL')"
'   ****************************************************************************
    DoCmd.RunSQL "INSERT INTO tblTestingLevel (LevelName) VALUES ('FULL')"

    DoCmd.RunSQL "INSERT INTO tblTestingLevel (LevelName) VALUES ('PARTIAL')"

    DoCmd.RunSQL "INSERT INTO tblTestingLevel (LevelName) VALUES ('NIL')"

    DoCmd.RunSQL "INSERT INTO tblTestingLevel (LevelName) VALUES ('REDUCED')"
'   ****************************************************************************
    CreateLocations

    GoTo Finally

Catch:
'   3010: table exists
    If (3010 = Err.Number) Then Resume Next

    Handle Err.Number, Err.Description, "", "Sub CreateRelations", _
        VBE.ActiveCodePane.CodeModule.Name

Finally:
    DoCmd.SetWarnings True
    Application.SetOption "ANSI Query Mode", False

End Sub

Public Sub DropRelations()

    Dim rel As DAO.Relation, tbl As DAO.TableDef

    For Each rel In CurrentDb.Relations

        If CBool(InStr(rel.ForeignTable, "tbl")) Then

            DoCmd.Close acTable, rel.ForeignTable
            DoCmd.Close acTable, rel.Table

            DoCmd.RunSQL "ALTER TABLE " & rel.ForeignTable & "  DROP CONSTRAINT " & _
                rel.Name

        End If
        DoEvents
    Next

    For Each tbl In CurrentDb.TableDefs

        If CBool(InStr(tbl.Name, "tbl")) Then
            DoCmd.Close acTable, tbl.Name
            DoCmd.RunSQL "DROP TABLE " & tbl.Name
        End If
        DoEvents
    Next

    Set rel = Nothing
    Set tbl = Nothing

End Sub

Public Sub CreateStructure()

    DropRelations
    CreateRelations

End Sub