Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Opinions - Program to show data changes

    Hello everyone,
    I have a piece of commercial software that uses Access as a back end. Some of the GUI drives me nuts so I'd like to create either an additional GUI or just manipulate data behind the scenes. So to do so I have to understand how things work behind the scenes.

    My thought is this. I'd take a snapshot of the db, in some way, do something like add an order using the program and then take another snapshot of the db and compare to the original. It would tell me what tables and records, even down to fields were added/deleted/updated. If I had this info I could then create my own GUI to do the same task. I wouldn't rewrite the whole program, just parts I feel inefficient.

    This should be possible with ADO in VBA and just looping through everything, but I'm wondering if anyone knows of anything that already does this or if anyone has any ideas as to how they'd implement it.

    Thanks for any input.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by gwgeller View Post
    Hello everyone,
    I have a piece of commercial software that uses Access as a back end. Some of the GUI drives me nuts so I'd like to create either an additional GUI or just manipulate data behind the scenes. So to do so I have to understand how things work behind the scenes.

    My thought is this. I'd take a snapshot of the db, in some way, do something like add an order using the program and then take another snapshot of the db and compare to the original. It would tell me what tables and records, even down to fields were added/deleted/updated. If I had this info I could then create my own GUI to do the same task. I wouldn't rewrite the whole program, just parts I feel inefficient.

    This should be possible with ADO in VBA and just looping through everything, but I'm wondering if anyone knows of anything that already does this or if anyone has any ideas as to how they'd implement it.

    Thanks for any input.
    Unless this is a very very simple system, ti probably is going to take a lot more effort that you have described to figure out how to create you own GUI that does not really mess up the data.

    You will need to take a set of data completely throughout the life cycle of the data to fully begin to understand what all really happens and how you can be sure that you duplicate all the correct functionality needed for the forms you replace.

    If I we hore to do this, the first thing I would do is contact the vendor to see what support they will provide.

    How often do they upgrade/update the software. Every thing this happens, you will probably have to repeate the length discover process to be sure that you replacement forms still function as needed. You can not assume you ever know for sure nothing has changes unless you test it.

    I speak from experience from doing this with several accounting systems.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    also consider; you can link to those same tables directly from a totally new and separate FE.

    There have been several times where clients are locked into a specific commercial product that doesn't do things they need. I've been able to give them their solution by linking to the tables but creating a whole new separate FE doing just the extra things they need to do.

    When one can not link to the tables - the other alternative is to regularly export/import the data into a separate Access app...

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    Thanks for the replies. HiTech, this is just a little project for my side business. I don't use much of the current program so I feel pretty comfortable that I'm not doing anything too risky. Mostly I just enter invoices and expenses. The entry is really cumbersome and that is the part I want to make more efficient. NTC I am thinking the same as you. I'd build a separate GUI that accessed the data.

    With that being said this ended up being more simple than I'd hoped. Basically what made it possible is that nearly all the tables have an ID column. The ones that don't I do not use. So the process is this:
    1. Make a copy of the production db.
    2. Get a list of tables using ADOX.
    3. Using ADO loop through each table selecting all records, sort by ID. Do the same with the copy.
    4. Loop through each record and compare fields values.

    Because we have the ID we can sort by it. Not only does this line up the records for comparison, but it enables us to determine if records have been added/deleted too.

    While I'm looping through the the production and copy table recordsets I compare the IDs. If they match then I compare all the fields. If they don't match then either a record was added or deleted. There is a little more to it and I'd be glad to help anyone else out if they need the details.

    To test things out I added an order and ran the comparison. It showed that a new record was added to three tables, which is exactly the info I need. Next will be to try an update.
    Having the ID column in most tables really made this easy, but as long as the tables have a primary key it is still possible, but will be more work if the column names differ.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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