Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44

    Running VBA code before security warning

    Hello guys.So I have this code, which is executed on load on my main form when user opens database.


    It looks up in the table "Korisnici" (Users), and if the username is not found it closes the database.
    The database file is on the company server. This code meant to protect the database from unathorized access,but it won't load due to security warning, until user clicks "Enable content" on his computer.
    Up until then user is free to do whatever he wants and I want to stop that.

    Do you have an idea what can I do?
    Here is the code below.

    Code:
    Option Compare Database
    
    Private Sub Form_Load()
    
    
        Username = fOSUserName()
        Set dbs = CurrentDb()
        strSQL = "SELECT access_level FROM Korisnici WHERE Username = '" & Username & "';"
        Set rst = dbs.OpenRecordset(strSQL)
    
    
        If rst.RecordCount = 0 Then
        strMsg = Username &", nemate pristup bazi podataka!"
        MsgBox strMsg
        DoCmd.closeDatabase
        Return
        End if
    
    
        access_level = rst![access_level]
        rst.Close
        dbs.Close
        If access_level < 5 Then
            DoCmd.NavigateTo "acNavigationCategoryObjectType"
            DoCmd.RunCommand acCmdWindowHide
            DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
    
        Else
            DoCmd.SelectObject acTable, , True
            DoCmd.ShowToolbar "Ribbon", acToolbarYes
        End If
    
    
    
    
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    On each PC, you must set TRUSTED LOCATIONS.
    open access, create a new db. Or any db.
    file, options, trust.
    in trusted locations,add the root folder. Either c:\. Or network UNC: \\server\folder.
    Check include sub folders.

    this will prevent access from asking the user to ENABLE button.
    Unfortunately it has to be done manually on each PC.

  3. #3
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by ranman256 View Post
    Unfortunately it has to be done manually on each PC.
    That's the main problem, that's impossible for me. I can't take everyone's computer in my company and do that.
    Is there something else I can do? Some option to hide everything before user confirms "Enable content" ?
    Otherwise anybody can open that database and delete everything.

    Do you reccommend some backup solution, something that can help me? Maybe there's a different way to restrict the access.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes, it IS possible to do this
    Trusted locations are set in the registry.
    In order to ensure an application is trusted when it is first opened the registry needs to be modified beforehand
    This can be done in several ways:
    1. Run a .reg file to set the trusted location before running the Access app
    2. Run a vb script to call a .reg file as above
    3. Distribute your applications as .exe files packaged using a professional installer application. Include script to set the registry as part of the install routine
    Of course, users may ignore the first step in 1 & 2.
    Also network admins may block the first two methods to prevent anyone directly editing the registry

    I recommend option 3 and use this method with all my commercial apps sold via my website.

    However, if network admins also prevent standard users running .exe files then even that is impossible.
    In that case, I suggested adding code to check if the location is trusted at startup. If not, the app should automatically close (possibly with an explanatory message to users)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    However, if network admins also prevent standard users running .exe files then even that is impossible.
    If they're that tight, I would think they would routinely install applications remotely by some IT person or "power user".
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by santon View Post
    ......The database file is on the company server.
    Does this mean the dB is split and each user has a copy of the FE on their local computer?





    Looking at your code, it has many issues.
    The top two lines of EVERY module should be
    Code:
    Option Compare Database
    Option Explicit
    Other issues:
    Variable "dbs" has not been declared
    Variable "rst" has not been declared
    Variable "strSQL" has not been declared
    Variable "strMsg" has not been declared
    Variable "access_level" has not been declared

    If you want to quit Access, instead of
    DoCmd.closeDatabase
    you should use
    Application.Quit or DoCmd.Quit


    Access VBA does not have a "Return" command (like VB does).


    The rule I was taught (ages ago) is
    If you create it, destroy it, if you open it, close it.

    In this line
    Code:
    Set rst = dbs.OpenRecordset(strSQL)
    , you create rst (SET command) AND you open it (Openrecordset).
    So, at some point, you must close the recordset (rst.Close) AND destroy it (SET rst = Nothing)

    For
    Code:
    Set dbs = CurrentDb()
    you create it (SET command), BUT you do not OPEN it. Therefore you SHOULD NOT have "dbs.Close". You did create it (SET dbs command), so you must destroy it (SET dbs = Nothing)

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Access VBA does not have a "Return" command (like VB does).
    Interestingly, VBA does support a GOSUB....RETURN statement. It has to be contained wholly within a sub or function's procedure. It acts like a Call to external code, but requires an explicit Return.
    But why not just use a regular Call to an external sub or function? Well, it has the advantage of not needing to pass arguments or dim its own variables, since all the data elements and variables are already present within the procedure containing the Gosub.
    I've only used it once in 20 years, and then just to see if it worked. It did.

    GoSub...Return statement (VBA) | Microsoft Docs

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Umm, I think that hails from BASIC from at least back to 1981? M$ "borrowed" it?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    Quote Originally Posted by ssanfu View Post
    Does this mean the dB is split and each user has a copy of the FE on their local computer?
    No, it's not divided into FE and BE, there's only one file.
    It contains pretty basic stuff, data that every worker at the end of the shift has to type in.
    We are divided into line structures, and I don't want other workers messing with our database so that's the reason I need to find a way to lock it from unathorized access, as soon as someone tries to open the database file.



    Quote Originally Posted by ssanfu View Post
    Looking at your code, it has many issues.
    You'll excuse me for my bad code, I'm an ms access amateur, only recently started using it.
    It was important for me that code works, and I haven't paid much attention to details.
    Thank you for you detailed explanation, I will fix everything



    Also thank you guys for your ideas, but neither one works, company strictly forbidden installing software on our computers, it is all controlled by admin.

    Do you maybe have tricky idea, to "force" the user to press the "Enable content" on the startup?
    Maybe displaying blank page or something until security warning is off.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by santon View Post
    No, it's not divided into FE and BE, there's only one file.
    It contains pretty basic stuff, data that every worker at the end of the shift has to type in.
    We are divided into line structures, and I don't want other workers messing with our database so that's the reason I need to find a way to lock it from unathorized access, as soon as someone tries to open the database file.
    The fact that multiple users have to enter data at the end of each shift makes it even more important that the file is split...no matter how basic.
    Otherwise you will get corruption at some point. If not split, you will lose everything
    You say there is only one copy of the database. Do all users run it from the same PC?

    Quote Originally Posted by santon View Post
    Also thank you guys for your ideas, but neither one works, company strictly forbidden installing software on our computers, it is all controlled by admin.

    Do you maybe have tricky idea, to "force" the user to press the "Enable content" on the startup?
    Maybe displaying blank page or something until security warning is off.
    Neither of those are possible for the reasons explained earlier
    The only solutions I can suggest are to get admin to
    a) install the app(s) and run a registry file to set the location
    OR
    b) add a shortcut on each user's desktop. The shortcut needs to be a vb script to first edit the registry and then open your Access file (now trusted)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I believe there is one location which is a default trusted location - Office 14 (which is access 2010) will change depending on the access version, and no doubt the program files may change depending on whether access is installed as 32bit or 64bit

    C:\Program Files (x86)\Microsoft Office\Office14\ACCWIZ\

    No idea how you would ensure the app gets installed there without using an installer of some sort i.e. the same installer that would set a trusted location.

    Agree with Colin, db must be split, tables only in the backend on a server, everything else in a front end, copies of which are located on each users local drive.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Whilst the ACCWIZ folder is a default trusted location, you cannot save files to that without having elevated permission levels.
    As only admins will have elevated permissions in your situation, I believe that idea is unfortunately a non-starter.

    Frustrating though this may appear, the logic behind it is sound for helping to protect workstations from malicious code
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One more note: If you have multiple users using a single (unsplit) dB, be warned that this is a known cause of dB corruption. It is not a matter if IF, just a matter if WHEN.
    If you do not split the dB, then you should (must?) make backups at least daily, if not twice a day.
    Or make the dB open Exclusively, only one user can be in the dB at a time.


    Good luck.....

  14. #14
    santon's Avatar
    santon is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    44
    I am thankful for all your comments and for your help.

    About that BE and FE, let me try to explain my case
    I work as a student in a factory, we have machines numbered (1,2,3,4...etc).
    All those machines work in 3 shifts (A, B, C), and they need to put the data into the computer for every shift. It was originally meant that there is one database for all the data.
    But when I started to work on this project I suggested that we split the database into one file for every machine, so there is database called SD 01, SD 02, SD 03... etc
    Every machine has its own personal database for entering the data. I am making backup files after every work day.

    The computers in factory (located on machines) are all logged in with the same windows account (one id), and database is set for legacy encryption.

    Click image for larger version. 

Name:	snip.JPG 
Views:	38 
Size:	15.6 KB 
ID:	43579

    You think that's okay or do you still think I need to split the BE and FE?
    Because it's all working just fine at the moment, and considering I am still learning ms access, I don't want to mess everything up.

    Thank you again for all your advices.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes you should still split it putting all data in the BE.
    Ideally have one copy of the FE on each workstation each linked to a common BE on the network.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. loosing exclusivity after running code
    By arti in forum Modules
    Replies: 1
    Last Post: 04-24-2018, 01:42 AM
  2. Stop code running.
    By Homegrownandy in forum Programming
    Replies: 11
    Last Post: 05-22-2017, 02:11 PM
  3. Issue with VBA code running on my system
    By matt704 in forum Access
    Replies: 9
    Last Post: 01-24-2017, 07:02 PM
  4. Running code in the background
    By LostInTheTrees in forum Programming
    Replies: 3
    Last Post: 04-16-2015, 06:00 PM
  5. Running Code from a Module in a Macro
    By ReadyReckoners in forum Modules
    Replies: 2
    Last Post: 05-08-2013, 04:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums