Question Distribute FE Changes

[raweber]

So, I have a FE/BE 2007 database with a variety of different front-ends customized the the various users. They do share a lot of common queries, reports and forms, but not entirely.

I have explored some of the various auto-updaters, and they seem to be all aimed at distributing an entire front-end to a bunch of users. What I would like to do is distribute only the changed forms, queries and reports to the various users.

Anyone know of a utility that will do this?

Thanks, Rob

 

[Pat Hartman]

No. You would need to write your own.

People usually use a login form to identify a user and then use that information to control access to certain forms and reports. That way everything is in one database and easier to manage and distribute.

 

[raweber]

How about this - is there a way to manually copy the altered forms, queries and reports from one Front End to another so I don't have to make the specific changes to each and every front end individually?

Thanks, Rob

 

[spikepl]

Use the External Data wizard to import objects from another db.

I think Pat was politely trying to tell you that your current setup with disparate fe's is work-intensive and a potential source of eternal grief

 

[Pat Hartman]

I've posted some disconnected code I use. It should contain the elements you need to put together a smooth export/export routine. The code snippet loops through all containers and exports the objects. You would want to qualify that to search by modified date or perhaps pick specific objects. Then there are several independent import/export subs. I usually just modify the code and run the procedure I need. This is not something the users have access to. But since you want your users to control this, you'll need to build them a form that looks for files in a specific directory and then imports them. You'll also have to make allowances for replacing which none of my code does because I just use this for my own purposes and handle that manually.

Option Compare Database
Option Explicit

Public Sub RunCode()
    Call ExportDatabaseObjects("Modules")
End Sub
Public Sub ImportForm()
Dim strPath As String
    strPath = "C:\Data\Work\Maggio\TextObjects\Form_"
    Application.Application.LoadFromText acForm, "sfrmDependents", strPath & "sfrmDependents" & ".txt"
End Sub
Public Sub ImportModule()
Dim strPath As String
    strPath = "C:\Data\Work\Maggio\TextObjects\Module_"
    Application.Application.LoadFromText acModule, "sfrmDependents", strPath & "sfrmDependents" & ".txt"
End Sub
Public Sub ImportReport()
Dim strPath As String
    strPath = "C:\Data\Work\Maggio\TextObjects\Report_"
    Application.Application.LoadFromText acReport, "rptAmortization", strPath & "rptAmortization" & ".txt"
End Sub
Public Sub ExportReport()
Dim strPath As String
    strPath = "C:\Data\Work\Maggio\TextObjects\Report_"
    Application.SaveAsText acReport, "rptAmortization", strPath & "rptAmortization" & ".txt"
End Sub
Public Sub ExportForm()
Dim strPath As String
    strPath = "C:\Data\Work\Maggio\TextObjects\Form_"
    Application.SaveAsText acForm, "sfrmDependents", strPath & "sfrmDependents" & ".txt"
End Sub
Public Sub ExportDatabaseObjects(ExportType As String)
On Error GoTo Err_ExportDatabaseObjects
    
    'Dim db As Database
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim D As Document
    Dim C As Container
    Dim i As Integer
    Dim sExportLocation As String
    
    Set db = CurrentDb()
''import from text =
''application.Application.LoadFromText acForm, "frmRisks","C:\Temp\TextRiskReview070615\Form_frmRisks.txt"


    sExportLocation = "C:\Data\Work\Maggio\TextObjects\" 'Do not forget the closing back slash! ie: C:\Temp\
    
    Select Case ExportType
        Case "TableDefs"
            For Each td In db.TableDefs 'Tables
                If Left(td.Name, 4) <> "MSys" Then
                    DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".txt", True
                End If
            Next td
        Case "Forms"
            Set C = db.Containers("Forms")
            For Each D In C.Documents
                Application.SaveAsText acForm, D.Name, sExportLocation & "Form_" & D.Name & ".txt"
            Next D
        Case "Reports"
            Set C = db.Containers("Reports")
            For Each D In C.Documents
                Application.SaveAsText acReport, D.Name, sExportLocation & "Report_" & D.Name & ".txt"
            Next D
        Case "Scripts"
            Set C = db.Containers("Scripts")
            For Each D In C.Documents
                Application.SaveAsText acMacro, D.Name, sExportLocation & "Macro_" & D.Name & ".txt"
            Next D
        Case "Modules"
            Set C = db.Containers("Modules")
            For Each D In C.Documents
                Application.SaveAsText acModule, D.Name, sExportLocation & "Module_" & D.Name & ".txt"
            Next D
        Case "QueryDefs"
            For i = 0 To db.QueryDefs.Count - 1
                Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt"
            Next i
        Case Else
    End Select

    Set db = Nothing
    Set C = Nothing
    
    MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
    
Exit_ExportDatabaseObjects:
    Exit Sub
    
Err_ExportDatabaseObjects:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ExportDatabaseObjects
    
End Sub

 

[raweber]

I guess I didn't look to closely past his first sentence. Is there much written about putting together a log-in form? Now I'm thinking and this makes me curious.

 

[Pat Hartman]

I'm sure if you search here you'll find several examples. But controlling who sees what goes beyond that. In Access versions prior to A2007, you could use ULS, clunky as it was to do this. Now you have to roll your own. I don't have a sample I can post but in general, in addition to having the login form which collects the user info (you could also do this by using the Environ("UserName") variable to get the user ID), you need to keep tables of who (preferally by group so this doesn't get out of hand) has access to what. Then your menus would join to this "access" table to control what each person sees.