Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    9

    Unanswered: Reports in listbox to reside in external db

    I have reports visible in a listbox on a tabbed form. The Row Source for the listbox is:
    Code:
    SELECT [TblReports].ReportID, [TblReports].ReportName, [TblReports].ReportCaption, [TblReports].ReportCriteriaFlags FROM [TblReports] ORDER BY [TblReports].ReportCaption;
    ReportCriteriaFlags is used to make date, employee fields etc. visible/invisible based on what is required of the report. A single button opens the report selected in the listbox. This works fine.

    QUESTION:

    Some of the reports currently presented in the listbox are State-specific (i.e. California and Arizona have different forms for example). I would like to have these State-specific reports exist in a separate external database, and deploy the "State" database only to businesses in the appropriate State. The listbox then, in the main database would need to include reports from (2) locations, one, the main database, and two, the State-specific reports which reside in the external (but local) database.

    I really appreciate the help you have all provided thus far.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally IU wouldn't deploy state sepecific versions, I'd deploy a single app, but configure it locally, and modify the menus/switchboard as appropriate. so CA users get CA specific reports, Az likewise. you are reducing your maintenance task by supporting the one db, no migration problems, no version control problems. the app is the app, is the app.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    9
    Thanks healdem. My thoughts: I figured a single app with 100+ reports would be very large and then I have the issue of keeping the reports up to date. Example: U.S. States often update their forms annually. These forms (think of a PDF fill-in form) are the basis of the reports in my app. With your approach, if I then update the single app with the new state forms, I would have to re-install the app at 150 locations and migrate their existing data to the new app. With my approach, I could email the revised "State" database (containing the updated state reports), and the user at the locations would simply overwrite their existing "State" Database.

    Does my approach make any sense?

  4. #4
    Join Date
    Mar 2009
    Posts
    9
    BTW, I found these code examples browsing the Internet that (if I knew how to integrate them) might do the trick.
    Code:
    Access Example 1
    
     
    	Private Sub UserForm_Initialize()
    Dim myDataBase As Database
    Dim myActiveRecord As Recordset
    Dim i As Long
    'Open the database to retrieve data
    Set myDataBase = OpenDatabase("E:\Junk\sourceAccess.mdb")
    'Define the first recordset
    Set myActiveRecord = myDataBase.OpenRecordset("Table1", dbOpenForwardOnly)
    'Set the listbox column count
    ListBox1.ColumnCount = myActiveRecord.Fields.Count
    i = 0
    'Loop through all the records in the table until the EOF
    'marker is reached. Use AddItem to add a new row for each record.
    'Use List to populate the ListBox column and row field.
    Do While Not myActiveRecord.EOF
        ListBox1.AddItem
        ListBox1.List(i, 0) = myActiveRecord.Fields("Employee Name")
        ListBox1.List(i, 1) = myActiveRecord.Fields("Employee DOB")
        ListBox1.List(i, 2) = myActiveRecord.Fields("Employee ID")
        i = i + 1
    'Get the next record
        myActiveRecord.MoveNext
    Loop
    'Close the database and clean-up.
    myActiveRecord.Close
    myDataBase.Close
    Set myActiveRecord = Nothing
    Set myDataBase = Nothing
    End Sub
    
    Access Example 2
    
     
    	Private Sub UserForm_Initialize()
    'You need to set a reference in your project to the “Microsoft DAO 3.51 (or 3.6) Object Library”.
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
    'Open the database to retrieve data
    Set db = OpenDatabase("E:\Junk\sourceAccess.mdb")
    'Define the first recordset
    Set rs = db.OpenRecordset("SELECT * FROM Table1")
    'Determine the number of records in the recordset
    With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
    End With
    'Set the number of ListBox columns = number of fields in the recordset
    ListBox1.ColumnCount = rs.Fields.Count
    'Load the ListBox with the retrieved records
    ListBox1.Column = rs.GetRows(NoOfRecords)
    'Cleanup
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    End Sub
    Might these work?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off you deploy your app as two Accessfiles (preferably as MDE's less chance of the user screwing with the app and then blaming your for problems.
    data in one MDB, the user interface (forms,reports etc) in the other. this is the so called front end/back end split

    so when you deploy a new app, you send a new front end, and that connects to the back end

    I'd suggest you include all your satyt specific forms & reports int eh same app, and use the menu/switchboard to show the relevant app to the user based on that systems configuration. IE if its CA show ONLY unspecific and CA stuff. the fact that other states forms & reports are in the app doens't really matter as the users don't see those, they only see what they are allowed.

    to me its your sanity and developer time that is important here.
    lets say you develop your app and fidn that each state has its own requirements, you find a problem with a common module that means you have to apply the same change to 50 applications. its easy to fiorget which app is your development app, so its quite possible to overwrite changes. its quite possible to forget to deploy the changes to all apps.

    properly designed it should be transparent to your users.

    the main issue is if the relevant states start to impose a totally different process (ie different schema, different ways of doing things, then you are starting to verge onto different app per state).

    but if the procedures are not that different then I'd keep it in one monolithic front end

    you would probably call userforminitialise in the forms on open event., hwoever you do need to be carefull with changing forms or reports on the fly, sometimes you will be requewsted if you want to save chanegs.. that can cuase problmes, not least if you are deploying your app as an MDB
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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