none
Relinking a Password protected BE Database to FE RRS feed

  • Question

  • I have a front end file and a back end database file. I use the following code for relinking my FE to BE. Now, I noticed that some operators have access to database and it is very dangerous. I want to make the BE password-Protected , but I don't know where I should add this Password in my FE code. The below code is in the frmUserLogin Form that appear at the program startup.

    Option Compare Database
    Option Explicit
    Public Function CheckLinks(vTableName As String) As Boolean
    'Checks whether back end database filename = location stored in front end database and automatically re-links tables if not
    'Entry  (vTableName) holds name of any table present in back-end data file
    'Exit   (CheckLinks) True if tables linked OK or False if link fails
    'Requires module modFileSelect (unless not required, see separate Word documentation for more info)
    'Call CheckLinks function in Open event of first form loaded (i.e. LogIn or Switchboard forms)
    'Example Code =
    '       Private Sub Form_Open(Cancel As Integer)
    '           If CheckLinks("TableName") = False Then Application.Quit    'where TableName is the name of a table in the back end
    '           ....
    '       End Sub
    Dim vAppPath As String, vBackEndPath As String, vBackEndFilename As String
    Dim dbLocal As Database, dbData As Database
    Dim tdf As TableDef
    Dim vCount As Long, vLoop As Long
    Dim vArray() As String
        On Error GoTo ErrorCode                                                 'trap errors including No Linked Table Found (see below)
       
        'Copy the BE pathname + filename from the FE into vBackEndPath
        vBackEndPath = CurrentDb.TableDefs(vTableName).Connect                  'fetch connection string for specified table
        vBackEndPath = Right(vBackEndPath, Len(vBackEndPath) - 10)              'and remove prefix string (;DATABASE=)
       
        'Chk if .ini file exists and if not create a blank one
        Set dbLocal = CurrentDb()                                               'fetch filename of front-end file
        vAppPath = dbLocal.Name
        vAppPath = Left(vAppPath, Len(vAppPath) - 3)                            'replace ...mdb (assumes extension is .mdb or .mde)
        vAppPath = vAppPath & "ini"                                             'with ...ini
        If Dir(vAppPath) = "" Then                                              'if .ini file not present on disk then
            Open vAppPath For Binary As #1                                      'create new temporary blank .ini file (use same filename as front end)
            Put #1, , ""                                                        'and write to disk
            Close #1                                                            'close file
        End If
       
        'Load contents of .ini file into vBackEndFilename
        Open vAppPath For Binary As #1                                          'open .ini file (will be blank or hold pathname/s)
        vBackEndFilename = String(LOF(1), "*")                                  'fill vBackEndFilename with * first
        Get #1, , vBackEndFilename                                              'read .ini file from disk into vBackEndFilename
        Close #1                                                                'and close file
       
        'Copy BE pathnames from .ini file to array
        vArray = Split(vBackEndFilename, vbCrLf)                                'copy BE pathnames into array
       
        'Test each BE pathname in array to see if BE exists on disk
        For vLoop = 0 To UBound(vArray)                                         'loop thru array
            vBackEndFilename = vArray(vLoop)                                    'fetch next BE pathname (if any)
            If Dir(vBackEndFilename) <> "" Then Exit For                        'if BE file found at this location then exit loop
        Next
       
        'Chk if pathname in FE is same as valid BE on disk and exit if Yes
        If vBackEndFilename = vBackEndPath Then                                 'if pathname in .ini file = pathname in FE file then
            CheckLinks = True                                                   'return with no error (CheckLinks = True)
            Exit Function
        End If
        'If no .ini file or BE files found then get user to select one
        If vBackEndFilename = "" Then                                           'if no BE file found then
            vBackEndFilename = GetBackEndFilePath                               'show file selector and choose back-end file
            CheckLinks = False                                                  'return False if user quits here
            If vBackEndFilename = "" Then Exit Function                         'abort if user cancels File Selector (CheckLinks = False)
            Open vAppPath For Append As #1                                      'open .ini file again
            Print #1, vBackEndFilename                                          'and add BE pathname + CrLf to .ini file
            Close #1                                                            'and close file
        End If
       
        'Relink tables in FE to selected BE file
        Set dbData = OpenDatabase(vBackEndFilename)                             'open temp connection to BE database (speeds up linking)
        DoCmd.Hourglass True
        vCount = dbLocal.TableDefs.Count                                        'fetch number of tables to relink
        Call SysCmd(acSysCmdInitMeter, "Relinking Tables to File " & vBackEndFilename, vCount)
        vCount = 0                                                              'set table counter to 0
        For Each tdf In dbLocal.TableDefs                                       'loop through all (non-system) tables
            If Len(tdf.Connect) > 0 Then                                        'skip if system table
                vCount = vCount + 1                                             'inc table counter
                tdf.Connect = ";DATABASE=" & vBackEndFilename & ";PWD=Babajoon;"                  'set pathname + filename of back-end"
                tdf.RefreshLink                                                 'and make link to back end (if error go to ErrorCode)
            End If
            Call SysCmd(acSysCmdUpdateMeter, vCount + 1)                        'update progress bar
        Next tdf                                                                'repeat till all tables linked
        DoCmd.Hourglass False
        Call SysCmd(acSysCmdRemoveMeter)                                        'hide progress bar
        dbData.Close                                                            'close temp connection
        Set dbData = Nothing
        CheckLinks = True                                                       'return with no error (CheckLinks = True)
        dbLocal.Close                                                           'close local database
        Set dbLocal = Nothing
        Exit Function                                                           'exit (CheckLinks = True)
    ErrorCode:
        If Err = 3011 Then Resume Next                                          'if Error 3011 then carry on (see separate Word documentation)
        Call SysCmd(acSysCmdRemoveMeter)                                        'hide progress bar
        DoCmd.Hourglass False
        MsgBox Err & "  " & Err.Description                                     'show error and then
        CheckLinks = False                                                      'return False (and quit application) if serious error
    End Function
    Public Function GetBackEndFilePath() As String
    'Open a File Selector and return with full pathname to back end file (or NULL if user cancels)
    'This routine uses the File Selector code from Ken Getz and Paul Litwin (in modFileSelect module)
    'If you already have some similar code in your project then you could use that instead to return a path to the back end file
    Dim vFilter As String
    Dim vFlags As Long
        vFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR  'define flag settings (see modFileSelect module)
        vFilter = ahtAddFilterItem(vFilter, "Access (*.accdb)", "*.accde")              'define filter string (see modFileSelect module)
        GetBackEndFilePath = ahtCommonFileOpenSave( _
                OpenFile:=True, _
                InitialDir:="C:", _
                Filter:=vFilter, _
                Flags:=vFlags, _
                DialogTitle:="Locate Database Back-End File")                       'open File Selector and return with path name (or Null if cancelled)
    End Function


    Karim Vaziri Regards,


    • Edited by kvaziri Monday, July 25, 2016 7:42 PM
    Monday, July 25, 2016 7:40 PM

Answers

  • >>>I want to make the BE password-Protected , but I don't know where I should add this Password in my FE code. 

    According to your description, do you want to work with database passwords in VBA Code? If so, you could refer to this reference about Working with Database Passwords in VBA Code

    If I have any misunderstandings on your question, please correct me and provide more details about your issue.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:55 AM
    • Marked as answer by David_JunFeng Monday, August 8, 2016 1:14 AM
    Tuesday, July 26, 2016 3:15 AM

All replies

  • >>>I want to make the BE password-Protected , but I don't know where I should add this Password in my FE code. 

    According to your description, do you want to work with database passwords in VBA Code? If so, you could refer to this reference about Working with Database Passwords in VBA Code

    If I have any misunderstandings on your question, please correct me and provide more details about your issue.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:55 AM
    • Marked as answer by David_JunFeng Monday, August 8, 2016 1:14 AM
    Tuesday, July 26, 2016 3:15 AM
  • Dear JunFeng,

    It's OK. I want to work with password in databases. I'll study the link and tell you the result.

    Regards,

    Vaziri


    Karim Vaziri Regards,

    Tuesday, July 26, 2016 10:24 AM
  • Hi kvaziri,

    Since we haven't heard from you for a long time, have you resolved this issue? If not, please feel free post your question on MSDN forum, we will supply help as soon as possible.

    Thanks for your understanding.
    Friday, August 5, 2016 8:43 AM