Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Location
    Mexico City
    Posts
    4

    Question Unanswered: How to control that only last version of the application connects to LIVE database?

    Hi All!

    This is my first post on this forum.
    We are about to start a project using Access as the front-end and SQL Server for data storage.
    My concern is that I'll be responsible of the design and build of the db, and also of data integrity, that means, even when I'll try to use the necessary constraints and instead of triggers on the database, I still need to certify that the Access application obeys some rules like taking care of performance, security, business rules, etc. By the way, the development of the front-end will take place somewhere else, not even in my same country.

    So, my question is, is there any way I can have some kind of control of who inserted/modified data, with which version of the application? The first one looks easy by having columns for electronic signature on every table (date_inserted, last_modified, user_last_modified), but what about the latter?
    Maybe the only option is to have controled environments for QA and Live, using maybe Citrix or something similar so that developers/users cannot run an uncertified version and connect to QA/Live databases? And if that's so, the best option would be Citrix?

    Sorry if I'm asking too many questions.Thank you very much in advance for your comments!

    From Mexico,
    Rocio (that's a ladie's name!)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Personally, I create several additional properties on the Database object ("VersionMajor", "VersionMinor", "VersionRevision", "VersionDate") that are used to identify the version of an application. The database on the SQL Server has a table that contains matching columns. When an Access application starts it compares its own version properties with the version values retrieved from the server. If they do not match, the application can halt and quit Access or it can perform an automatic upgrade to the last version matching the version information retrieved from the server.

    Class Module: Cls_Std_DbProperties
    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 = "{3CDDCF45-BD89-4C1E-95C6-BD1B9F2CD834}"
    Private Const c_strStd_ClassBuild As String = "20111011-2.1.1"
    
    
    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
    '
    ' = [STDC]{End} =======================================================================
    '
    Public Property Get Value(ByVal PropertyName As String) As Variant
    
        On Error GoTo Err_GetDBProperty
        
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            If pty.Name = PropertyName Then
                Value = CStr(pty.Value)
                Exit For
            End If
        Next pty
        m_lngLastError = 0
            
    Exit_GetDBProperty:
        Set pty = Nothing
        Set dbs = Nothing
        Exit Property
        
    Err_GetDBProperty:
        m_lngLastError = Err.Number
        Resume Exit_GetDBProperty
    
    End Property
    
    Public Property Let Value(ByVal PropertyName As String, ByVal Value 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(Value)
        Else
            Set pty = dbs.CreateProperty(PropertyName, dbText, CStr(Value))
            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
    
    Public Property Get Exists(ByVal PropertyName As String) As Boolean
    
        On Error GoTo Err_ExistsDBProperty
        
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        For Each pty In dbs.Properties
            If pty.Name = 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 Property Get Success() As Boolean
    
        Success = (m_lngLastError = 0)
    
    End Property
    Class Module: Cls_Std_Version
    Code:
    Option Compare Database
    Option Explicit
    
    Private Const V_LOCAL As Integer = 0
    Private Const V_SERVER  As Integer = 1
    Private Const V_MAJOR As Integer = 0
    Private Const V_MINOR As Integer = 1
    Private Const V_REVISION As Integer = 2
    Private Const V_DATE As Integer = 3
    Private Const V_FULL As Integer = 4
    
    Private m_Version(0 To 1, 0 To 4) As String
    Private m_SourceDatabase As String
    Private m_DestinationDatabase As String
    Private m_varMustUpdate As Variant
    Private m_strConnect As String
    
    ' = [STDC]{Begin} =====================================================================
    ' =
    ' =         ---------------------------------------
    ' =         *            Cls_Std_  Class          *
    ' =         ---------------------------------------
    ' =
    ' =         This section is common  to all Cls_Std classes.
    ' =
    
    Private Const c_strStd_ClassName As String = "Cls_Std_Version"
    Private Const c_strStd_ClassGUID As String = "{624EBA71-1C4C-49F0-87F1-F3CD58C3C619}"
    Private Const c_strStd_ClassBuild As String = "20111011-2.1.1"
    
    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
    '
    ' = [STDC]{End} =======================================================================
    '
    
    Public Property Get Connect() As String
    
        Connect = m_strConnect
        
    End Property
    
    Public Property Let Connect(ByVal ConnectionString As String)
    
        m_strConnect = ConnectionString
        
    End Property
    
    Public Function GetVersion(Optional ByVal ConnectionString As String) As Boolean
    
        Dim clsDBProperty As Cls_Std_DbProperties
        Dim dbsSQL As DAO.Database
        Dim rstSQL As DAO.Recordset
        Dim strServer As String
        Dim strcommand As String
        
        Set clsDBProperty = New Cls_Std_DbProperties
        m_Version(V_SERVER, V_FULL) = m_Version(V_SERVER, V_MAJOR) & "." & _
                                      m_Version(V_SERVER, V_MINOR) & "." & _
                                      m_Version(V_SERVER, V_REVISION) & " - " & _
                                      m_Version(V_SERVER, V_DATE)
        With clsDBProperty
            m_Version(V_LOCAL, V_MAJOR) = .Value("VersionMajor")
            m_Version(V_LOCAL, V_MINOR) = .Value("VersionMinor")
            m_Version(V_LOCAL, V_REVISION) = .Value("VersionRevision")
            m_Version(V_LOCAL, V_DATE) = .Value("VersionDate")
        End With
        m_Version(V_LOCAL, V_FULL) = m_Version(V_LOCAL, V_MAJOR) & "." & _
                                     m_Version(V_LOCAL, V_MINOR) & "." & _
                                     m_Version(V_LOCAL, V_REVISION) & " - " & _
                                     m_Version(V_LOCAL, V_DATE)
        strServer = ConnectionString
        If strServer = "" Then strServer = m_strConnect
        If strServer = "" Then strServer = clsDBProperty.Value("Jet_Server_Connect")
        If Len(strServer) > 0 Then
            Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, strServer)
            strcommand = "Proc_Get_Version_Info '" & Left(CurrentProject.Name, InStr(CurrentProject.Name, ".") - 1) & "'"
            Set rstSQL = dbsSQL.OpenRecordset(strcommand, dbOpenSnapshot, dbSQLPassThrough)
            With rstSQL
                If Not .EOF Then
                    m_Version(V_SERVER, V_MAJOR) = !Application_Version_Major
                    m_Version(V_SERVER, V_MINOR) = !Application_Version_Minor
                    m_Version(V_SERVER, V_REVISION) = !Application_Version_Revision
                    m_Version(V_SERVER, V_DATE) = !Application_Date
                    m_varMustUpdate = CBool(!Application_Must_Update)
                    m_SourceDatabase = !Source_Path
                    m_DestinationDatabase = !Destination_Path
                End If
                .Close
            End With
            Set rstSQL = Nothing
            dbsSQL.Close
            Set dbsSQL = Nothing
        End If
        Set clsDBProperty = Nothing
        
    End Function
    
    Public Property Get MustUpdate() As Boolean
    
        If IsEmpty(m_varMustUpdate) Then GetVersion
        MustUpdate = m_varMustUpdate
        
    End Property
    
    Public Property Get SourceDatabase() As String
    
        If IsEmpty(m_varMustUpdate) Then GetVersion
        SourceDatabase = m_SourceDatabase
        
    End Property
    
    Public Property Get DestinationDatabase() As String
    
        If IsEmpty(m_varMustUpdate) Then GetVersion
        DestinationDatabase = m_DestinationDatabase
        
    End Property
    
    Public Property Get Version(Provider As Long, VersionPart As Long) As String
    
        If IsEmpty(m_varMustUpdate) Then GetVersion
        Version = m_Version(Provider, VersionPart)
        
    End Property
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Location
    Mexico City
    Posts
    4
    Hi Sinndho,

    Thank you for your reply and code, although I'm not proficient in OO programming.
    Are you talking about controling the version of MS Access? I meant trying to control that if the developer changes something on the code of an Access form that inserts/deletes/modifies information in the SQL Server database, I should be aware of it, or better said, not let developers to use any Access forms being connected to LIVE db without having me certified those changes on the Access form. Does that make sense?
    I find that very difficult to accomplish since the Access forms reside on the developer's desktop.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I meant the version of the program (or application) written by you or another developer (or team), written in VBA, using objects such as Forms, Reports, etc. stored in an Access database (.mdb or .accdb file) and that connects to and interacts with a SQL server.

    In the model I use, 4 pieces of information: Major, Minor, Revision, Date (e.g. 2.5.17 20100519) that, assembled together, constitute the version of the application. These are stored in the Access database in user-defined properties of the Database object. On the server, they are stored in a table:
    Code:
    CREATE TABLE [dbo].[Tbl_Versions](
    	[SysCounter] [int] IDENTITY(1,1) NOT NULL,
    	[Application_Name] [nvarchar](50) NOT NULL,
    	[Application_Version_Major] [int] NOT NULL CONSTRAINT [DF_Tbl_Versions_Application_Version_Major]  DEFAULT ((0)),
    	[Application_Version_Minor] [int] NOT NULL CONSTRAINT [DF_Tbl_Versions_Application_Version_Minor]  DEFAULT ((0)),
    	[Application_Version_Revision] [int] NOT NULL CONSTRAINT [DF_Tbl_Versions_Application_Version_Revision]  DEFAULT ((0)),
    	[Application_Must_Update] [bit] NOT NULL CONSTRAINT [DF_Tbl_Versions_Application_Must_Update]  DEFAULT ((0)),
    	[Application_Date] [smalldatetime] NOT NULL CONSTRAINT [DF_Tbl_Versions_Application_Date]  DEFAULT (getdate()),
    	[Source_Path] [nvarchar](max) NOT NULL CONSTRAINT [DF_Tbl_Versions_Source_Path]  DEFAULT (''),
    	[Destination_Path] [nvarchar](max) NOT NULL CONSTRAINT [DF_Tbl_Versions_Destination_Path]  DEFAULT (''),
    	[Comments] [nvarchar](max) NOT NULL CONSTRAINT [DF_Tbl_Versions_Comments]  DEFAULT (''),
    	[Inactive] [bit] NOT NULL CONSTRAINT [DF_Tbl_Versions_Inactive]  DEFAULT ((0)),
     CONSTRAINT [PK_Tbl_Versions] PRIMARY KEY CLUSTERED 
    (
    	[SysCounter] ASC
    )WITH (PAD_INDEX  = OFF, 
           STATISTICS_NORECOMPUTE  = OFF, 
           IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS  = ON, 
           ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    which can be accessed through a stored procedure that returns them:
    Code:
    -- =====================================================
    -- Database:    Sales
    -- Object:      Procedure [dbo].[Proc_Get_Version_Info]
    -- Author:      xxxxxxxxxx
    -- Create date: 2009-11-24
    -- Description:	Returns Version info elements
    -- =====================================================
    CREATE PROCEDURE [dbo].[Proc_Get_Version_Info] 
    	@Application_Name nvarchar(50)
    AS
    BEGIN
    	DECLARE @Status INT
    	DECLARE @ReturnValue INT
    	BEGIN TRY
            SET @ReturnValue = 0
            SET @Status = -1
    		SELECT @ReturnValue AS ReturnValue, @Status AS [Status]
    		SELECT TOP 1 Tbl_Versions.Application_Version_Major, 
    			         Tbl_Versions.Application_Version_Minor, 
            			 Tbl_Versions.Application_Version_Revision, 
    			         Tbl_Versions.Application_Must_Update,	
    			         Tbl_Versions.Application_Date, 
    			         Tbl_Versions.Source_Path, 
    			         Tbl_Versions.Destination_Path
    			    FROM Tbl_Versions
    			   WHERE Tbl_Versions.Application_Name = @Application_Name AND
    			         Tbl_Versions.Inactive = 0
    			ORDER BY Tbl_Versions.Application_Date DESC;
    	END TRY
    	BEGIN CATCH
            SET @ReturnValue = @@ERROR
            SET @Status = 0
    		INSERT INTO Tbl_Log_Procedures (Procedure_Name, Error_Code, Comment)
    			VALUES ('Proc_Get_Version_Info', @ReturnValue, @Application_Name)
    		SELECT @ReturnValue AS ReturnValue, @Status AS [Status]
    	END CATCH
    	RETURN @Status
    END
    When an Access application starts, it connects to the SQL server and calls the Proc_Get_Version_Info stored procedure to retrieve the version info. It then compares with its own version info and a decision is made:
    - Server.Major = Application.Major And Server.Minor = Application.Minor And Server.Revision = Application.Revision --> Continue..
    - Server.Major <> Application.Major --> Halt and quit application.
    - Server.Minor > Application.Minor --> Performs an automatic upgrade using the Source_Path and Destination_Path values returned by the stored procedure.
    - Server.Revision > Application.Revision --> Performs an automatic upgrade if Application_Must_Update (returned by the S.P.) is True, continue normally if Application_Must_Update is False.
    Any other configuration of Version data --> Halt and quit application.
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Location
    Mexico City
    Posts
    4
    That looks great!
    Let me tell you my understanding to verify if I'm correct:
    This solution will work when there is a change on the database that makes me decide to set a new version (mayor, minor or revision) on the SQL table, so that the Access program doesn't work until it is upgraded. The automatic upgrades will work only if I also provide the upgrade and place it on the specified path. Another option is just to send a Warning saying than an upgrade is required and that will make the Access developer to work on it. (Of course there should be also some other direct communication between us for me to explain the changes and impacts on the database. Also, there should be an agreement for the Access development team for not to change ever the version on the user-defined properties of the database object and keep that controlled only by me)

    I can certainly try that, thanks a lot for the idea.

    But I'm still thinking.. what happens if the Access development team makes a change on the program thay might compromise data integrity, or a form does not comply some performance or business rules? That doesn't depend on the database version. Since they have control of the .mdb or .accdb locally, it looks like we should have a controled environment where I will install the revised version of the program that connects to the Live/Production SQL Server database and have the final users connect to it through Citrix or something similar. At least that's the only thing I've imagined so far. What do you think?

    Thanks so much!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using SQL server then you shoudl beable to enforce many if not most of the business rules there
    you can enforce the security / tracking requirement amongst others using triggers in SQL server

    if you are targetting a server backend then run, don't walk and get a good book on writing server freindly code using a book such as Access developer handbook
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by RocioA View Post
    Let me tell you my understanding to verify if I'm correct...
    Yes, you are.
    Quote Originally Posted by RocioA View Post
    what happens if the Access development team makes a change on the program thay might compromise data integrity, or a form does not comply some performance or business rules? That doesn't depend on the database version.
    This system was not set up against the Access developers and works from an agreement among the Access developers and the DBAs and implies their collaboration as a team.

    The only changes that are allowed, from the SQL Server part as well as from the Access front-end part and that do not require an upgrade are those that do not modify the data structures or business rules (performance improvements, bug corrections, etc.). They are identified by the Revision part of the version numbering system. If you go back to the description I previously posted, you'll notice that this part of the version number may change without necessitating an upgrade and without halting the application.

    As healdem rightly pointed out, the major part (if not all) of the security and business rules can (and in my opinion should) be implemented at the database level, not at the client application level.

    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Apr 2012
    Location
    Mexico City
    Posts
    4

    Thumbs up

    Right, I agree.

    Thank you both!

Tags for this Thread

Posting Permissions

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