Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    data808 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    487

    Frontend Backend Database Questions

    I have a situation here. Right now I am about to roll out my database but found an extremely big problem that I have overlooked and hope that there is something that can be done without changing my setup.

    Frontend files will be distributed on each computer in the office. One backend file will remain in a network drive. I have a login form that validates by VBA, NOT from a table. In other words, all the usernames and passwords are in the VBA code. I did not make a table for this. Here is an example:

    Private Sub cmdLogin_Click()
    txtUsername.SetFocus
    If txtUsername = "user1" And txtPassword = "user1" Then
    DoCmd.OpenForm "form1"


    Else
    MsgBox "Username or Password invalid. Please re-enter Username and Password.", vbExclamation, "Invalid Criteria"
    End If
    End Sub

    My question is, I will be updating this from time to time with new users and removing users that are no longer working in the department. How will I be able to update all the frontend files without having to go to each computer to do it? Is there a simple way of doing this? I can't believe I didn't think about this till I was almost done with my database.

    Please help!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Have you thought at all about changing your login scheme to use tables? It is far more efficient and flexible.

    What if some of your users need to be able to add other users? Usually an office manager wants access to do this and currently with your setup they cannot.

  4. #4
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    I would love to use tables to store usernames and passwords. At the time when I made it, I did not know how fully lock down the database to prevent users from accessing the tables. Now that I know how, it would be great to do the table way. Can you assist me with the code I need to validate the username and password on my login form? My username and password text boxes are called txtUsername and txtPassword. Login form is called Login. Login button is called cmdLogin.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Common topic. Here is one discussion https://www.accessforums.net/access/...gin-23585.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    Do you have something easier to understand. I tried looking on Youtube for a video that also provides the code but I can't seem to find any. The videos that are good don't have any code to go along with it. Some even want you to buy it. I'm just looking for something simple. User types in username and password. I would want the table to probably hold 3 fields. The username, password, and employee name so I know who its for. Do you have anything like that?

  7. #7
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    Why did you provide two links? Is one better than the other?

    Also in the .bat file code, that was suggested by rankhornjp, does the batch file work on a local setup? In other words if I wanted to test it on my own computer on the C drive? I put the master copy somewhere on the C drive then also put the batch file in the same folder. Then create a shortcut to that batch file on my desktop and then replace the master with a new one and update the batch file with the new version? Will that work?

  8. #8
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    I just tried the batch file and I'm pretty sure I'm not editing the paths correctly. I need your assistance. I get this message when I try to open the shortcut to the batch file which is in a folder on my C drive. I wanted to see if the FE file on my desktop would auto update but instead I get this message:

    The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
    Exit and restart Microsoft Office Access using valid command-line options.

    Then I have to click OK for about 5-10 times because the message keeps popping up. Then it tries to open up Access and then I get this message:

    can't find the database file 'Desktop/Master.mdb.'
    Make sure you entered the correct path and file name.

    This is what I changed the batch file code too:

    @echo off
    REM file used to automatically update the Database when updates are available.
    if not exist Desktop\Master Clearance Form Updated 02-11-2014.accdb echo Database needs to update to newer version.
    if not exist Desktop\Master Clearance Form Updated 02-11-2014.accdb pause
    @echo off
    if not exist Desktop\Master Clearance Form Updated 02-11-2014.accdb if exist Desktop\Master Clearance Form Updated*.accdb del Desktop\Master Clearance Form Updated*.accdb


    if not exist Desktop\Master Clearance Form Updated 02-11-2014.accdb Robocopy C:\Test Desktop\ Master Clearance Form Updated 02-11-2014.accdb


    Start MSAccess.exe /runtime Desktop\Master Clearance Form Updated 02-11-2014.accdb


    Exit

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Two discussions on same topic. One might have a tidbit of info the other doesn't.

    That batch file code isn't anything like what I used. I used a VBScript.

    The batch file is supposed to be on each user's local drive along with the frontend.

    I don't have anything easier to understand. Those threads reflect my own experience and have been helpful to a number of developers.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    Do you have code for a login that validates with a table? I want to try change my login form from validating from within the vba to validate with a table.

  11. #11
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    I have a youtube video on making a login form.

    https://www.youtube.com/watch?v=YQmy5tLn0kw

  12. #12
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    This is a great video. Thank you but right now I want to try and make a login where the user has to type their username into the text box instead of having a combo. I will keep this video in mind though if I need a combo box login. I watched the video and it was very informative. Thanks again.

  13. #13
    robrich22's Avatar
    robrich22 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Location
    Louisville, KY
    Posts
    41
    Here is my code for checking username and password against a table.

    Code:
    Private Function VerifyCredentials(inUsername As String, inPassword As String) As Boolean
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim sqlQry As String
    
    
        sqlQry = "SELECT [App Users].Username, [App Users].Password FROM [App Users] " _
               & "WHERE [App Users].Username = '" & inUsername & "' AND [App Users].Password = '" & inPassword & "'"
                
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(sqlQry)
        
        If rst.EOF Then
            VerifyCredentials = False
        Else
            VerifyCredentials = True
        End If
        
        rst.Close
        Set dbs = Nothing
        Set rst = Nothing
    End Function
    
    
    Private Sub CmdLogon_Click()
    
    
    On Error GoTo ErrHandler:
        If VerifyCredentials(txtUsername, txtPassword) Then
            SetCurrentUser txtUsername
            RecordLogin txtUsername, 1
            DoCmd.OpenForm "App Main Menu"
            DoCmd.Close acForm, Me.Name
        Else
            MsgBox "Username or Password was incorrect.  Attempt to access system has been logged.", vbExclamation, "Unauthorized!"
            RecordLogin txtUsername, 0
            txtUsername.SetFocus
        End If
    Exit Sub
    
    
    ErrHandler:
        MsgBox "Error connecting to Database.  Contact System Administrator"
    Exit Sub
    
    
    End Sub
    
    
    Private Sub RecordLogin(inUsername As String, inSuccess As Integer)
        Dim sql As String
        
        sql = "INSERT INTO [App Access Log] ([LoginDate], [LoginTime],[WorkStationUser],[UserName],[Success])" _
            & "VALUES (#" & Date & "#, #" & Time & "#, '" & Application.currentUser & "', '" & inUsername & "', " & inSuccess & ")"
        DoCmd.RunSQL (sql)
    End Sub
    Last edited by robrich22; 03-15-2014 at 11:16 AM.

  14. #14
    robrich22's Avatar
    robrich22 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Location
    Louisville, KY
    Posts
    41
    Also to note, I will be releasing a free utility for frontend sync'ing, launching, and relinking in about 2 weeks. It is a C# .net application that will run on .net framework 4.0. You can view or contribute to the project at https://github.com/robrich22/DBConnect

    It's actually usable in it's current form, I am just adding some finishing touches to it. Send me a msg if you want a link to the install/final version.

    Thanks

  15. #15
    data808 is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    487
    Thanks for the help. Not good with the vba at all. Would you be able to give an example? The only thing I am comfortable using is docmd's and some if, then, else statements and even that I am a rookie.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-01-2013, 09:11 AM
  2. Access Switching to frontend and backend
    By caliskier in forum Access
    Replies: 4
    Last Post: 11-19-2012, 11:58 AM
  3. Passwording frontend / backend Access 2010
    By erkwong in forum Security
    Replies: 0
    Last Post: 01-30-2012, 01:28 PM
  4. Replies: 1
    Last Post: 01-12-2012, 09:43 AM
  5. Replies: 1
    Last Post: 11-25-2011, 11:16 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 - Senior Forums