Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: MS Access Database File Identification

    Hi all,

    I am creating a C# application which uses an Access database to store information. This is all going well.

    However the application allows the user to browse for, and select, the Database file to connect with (this allows the database file to be stored in different locations for different users).

    My question is, is there a way of identifying if the user has selected the correct database file? Does the Access file itself support some sort of name/property that can be set which the application can use to recognise it is the correct one and not just some random MDB file?

    Many thanks,
    Chris

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes: you can create additional (user-defined) properties on a DAO.Database object. An application can then enumerate the Properties collection of the DAO.Database object to retrieve the value of such a defined property.

    Here's an example in VB/VBA:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_lngLastError As Long
    
    ' = [STDC]{Begin} =====================================================================
    ' =
    ' =         ---------------------------------------
    ' =         *            Cls_Std_  Class          *
    ' =         ---------------------------------------
    ' =
    ' =         This section is common  to all Cls_Std classes.
    ' =
    
    Private Const c_strStd_ClassName As String = "Cls_Std_DbProperties"
    Private Const c_strStd_ClassGUID As String = "{8E71EE88-5511-4AE5-9B75-CD056A645306}"
    Private Const c_strStd_ClassBuild As String = "20111019-2.1.2"
    
    Private m_clsIdentity As Cls_Std_Identity
    
    Public Property Get ClassBuild() As String
    
        ClassBuild = c_strStd_ClassBuild
        
    End Property
    
    Public Property Get ClassGUID() As String
    
        ClassGUID = c_strStd_ClassGUID
        
    End Property
    
    Public Property Get ClassName() As String
    
        ClassName = c_strStd_ClassName
        
    End Property
    
    Public Property Get Identity() As String
    
        Identity = m_clsIdentity.Identity
    
    End Property
    '
    ' = [STDC]{End} =======================================================================
    '
    
    Private Sub Class_Initialize()
    
        Set m_clsIdentity = New Cls_Std_Identity
        
    End Sub
    
    Private Sub Class_Terminate()
    
        Set m_clsIdentity = Nothing
        
    End Sub
    
    Public Property Get Enumerate(Optional ByVal BuiltIn As Boolean = False) As Variant
    
        On Error GoTo Err_EnumerateDBProperty
        
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        Dim strPtyName As String
        Dim strPtyValue As String
        Dim strName As String
        Dim var(0 To 1) As Variant
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            strName = ""
            Select Case SystemPty(pty.Name)
                Case -1:    If BuiltIn = True Then strName = pty.Name
                Case 0:     strName = pty.Name
            End Select
            If Len(strName) > 0 Then
                If Len(strPtyName) > 0 Then strPtyName = strPtyName & Chr(11)
                strPtyName = strPtyName & pty.Name
                If Len(strPtyValue) > 0 Then strPtyValue = strPtyValue & Chr(11)
                strPtyValue = strPtyValue & pty.Value
            End If
        Next pty
        var(0) = Split(strPtyName, Chr(11))
        var(1) = Split(strPtyValue, Chr(11))
        Enumerate = var
        m_lngLastError = 0
            
    Exit_EnumerateDBProperty:
        Set pty = Nothing
        Set dbs = Nothing
        Exit Property
        
    Err_EnumerateDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_EnumerateDBProperty
    
    End Property
    
    Public Property Get Exists(ByVal PropertyName As String, Optional ByVal BuiltIn As Boolean = False) As Boolean
    
        On Error GoTo Err_ExistsDBProperty
        
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        Dim strName As String
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            Select Case SystemPty(pty.Name)
                Case -1:    If BuiltIn = True Then strName = pty.Name
                Case 0:     strName = pty.Name
            End Select
            If strName = PropertyName Then
                Exists = True
                Exit For
            End If
        Next pty
        m_lngLastError = 0
            
    Exit_ExistsDBProperty:
        Set pty = Nothing
        Set dbs = Nothing
        Exit Property
        
    Err_ExistsDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_ExistsDBProperty
    
    End Property
    
    Public Property Get LastError() As Long
    
        LastError = m_lngLastError
    
    End Property
    
    Public Sub Remove(ByVal PropertyName As String)
    
        On Error GoTo Err_RemoveDBProperty
        
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        If Exists(PropertyName) Then dbs.Properties.Delete PropertyName
        dbs.Properties.Refresh
        m_lngLastError = 0
        
    Exit_RemoveDBProperty:
        Set dbs = Nothing
        Exit Sub
        
    Err_RemoveDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_RemoveDBProperty
    
    End Sub
    
    Public Property Get Success() As Boolean
    
        Success = (m_lngLastError = 0)
    
    End Property
    
    Private Function SystemPty(ByVal PropertyName As String) As Long
    
        Select Case PropertyName
            Case "Connection"
                SystemPty = 1
            Case "Name", "Connect", "Transactions", "Updatable", "CollatingOrder", _
                 "QueryTimeout", "Version", "RecordsAffected", "ReplicaID", "DesignMasterID", _
                 "ANSI Query Mode", "AccessVersion", "ProjVer", "Build", "DefaultBackupLocation"
                SystemPty = -1
            Case Else
                SystemPty = 0
        End Select
        
    End Function
    
    Public Property Get Value(ByVal PropertyName As String, Optional ByVal BuiltIn As Boolean = False) As Variant
    
        On Error GoTo Err_GetDBProperty
        
        If Exists(PropertyName, BuiltIn) = True Then Value = CurrentDb.Properties(PropertyName).Value
        m_lngLastError = 0
            
    Exit_GetDBProperty:
        Exit Property
        
    Err_GetDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_GetDBProperty
    
    End Property
    
    Public Property Let Value(ByVal PropertyName As String, Optional ByVal BuiltIn As Boolean = False, ByVal PropertyValue As Variant)
    
        On Error GoTo Err_SetDBProperty
        
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        If Exists(PropertyName) Then
            Set pty = dbs.Properties(PropertyName)
            pty.Value = CStr(PropertyValue)
        ElseIf SystemPty(PropertyName) = 0 Then
            Set pty = dbs.CreateProperty(PropertyName, dbText, CStr(PropertyValue))
            dbs.Properties.Append pty
        End If
        dbs.Properties.Refresh
        m_lngLastError = 0
        
    Exit_SetDBProperty:
        Set pty = Nothing
        Set dbs = Nothing
        Exit Property
        
    Err_SetDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_SetDBProperty
    
    End Property
    Have a nice day!

  3. #3
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Ms-Access Database is opened within a Workspace under the DBEngine (Database Engine). You can address the Name Property of the Database Object to get the complete pathname of the Database.

    Example:

    ? DBEngine.Workspaces(0).Databases(0).Name

    The above statement displays the complete pathname of the first database opened under Workspaces(0). The same result can be obtained from the statement CurrentProject.FullName when addressed from within the database.

    You can create Custom Properties to the Database (besides the built-in properties) to store your own custom data and to retrieve or change it as you like. Check the following example:

    Code:
    Public Function CustomProperty()
    Dim ws As Workspace, db As Database, prp As Property
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
    Set prp = db.CreateProperty("dbowner", dbText, "a.p.r.pillai")
    db.Properties.Append prp
    
    'read the custom property 'dbowner' value and display
    MsgBox "database owner: " & db.Properties("dbowner").Value
    
    'change the 'dbowner' custom property value
    db.Properties("dbowner").Value = "msaccesstips.com"
    End Function
    Check the following link for another example:

    Saving Data on Forms, not in Table
    Last edited by apr pillai; 06-11-12 at 14:29.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •