none
Looking for Add-in to update a client's database

    Question

  • So, the title pretty mush says it all, but to be more specific:

    Any table or relation that I have on my database should be added to the client's database - and any table/relation that the client database has and my don't have should be removed.

    The same goes for fields in tables.

    All in all the client's database structure should be identical to mine.

    Any suggestions?

    Thanks

    Thursday, February 07, 2013 7:21 AM

Answers

  • iscitizen wrote:

    Any table or relation that I have on my database should be added to the client's database - and any table/relation that the client database has and my don't have should be removed.

    The same goes for fields in tables.

    All in all the client's database structure should be identical to mine.

    I keep a backend version number in a table.   Every time my FE opens the
    backend database it checks that variable. If it's older then I execute
    subroutines containing the VBA

    http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEMscreens/CompareEM-About.htm
    Works very well.    I use the code a lot. 
    I also run action queries as required in the code.  For example in one
    case I decided to change an Technician string to a lookup table.  So
    after the code ran to create the Technician lookup table I then run an
    action summing query to create records in that table.  Then the code
    created a child table in the database.  Then I ran another query which
    created the child table records from the string value in the work orders
    table joined to the string value in the lookup table.  Finally the code
    deleted the field from the Work Order table.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Thursday, February 07, 2013 9:20 PM

All replies

  • Make the changes in one place and then link or import to the other places. There is no automatic solution - and you would not want there to be since it could cause untold damage. You could possibly write a routine that would do a compare between two databases but I don't think making automatic updates would work. What would it do if a change it needed to make violated the referential integrity of a relationship?
    Thursday, February 07, 2013 3:09 PM
  • If you do just a little bit of Googling you will find any number of procedures to add tables, edit fields, ...  Look and you will find!  Then you can piece everything together to suit your exact needs.

    Daniel Pineault, 2010 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, February 07, 2013 4:03 PM
  • you might consider uploading your database with all the tables and relationships, but no data and creating a routine that appends all their data to your new database structure.  Of course, as has already been mentioned, if the data does not meet referential integrity you will run into trouble and will have to have some kind of rule to handle those and other exceptions.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Thursday, February 07, 2013 4:23 PM
  • iscitizen wrote:

    Any table or relation that I have on my database should be added to the client's database - and any table/relation that the client database has and my don't have should be removed.

    The same goes for fields in tables.

    All in all the client's database structure should be identical to mine.

    I keep a backend version number in a table.   Every time my FE opens the
    backend database it checks that variable. If it's older then I execute
    subroutines containing the VBA

    http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEMscreens/CompareEM-About.htm
    Works very well.    I use the code a lot. 
    I also run action queries as required in the code.  For example in one
    case I decided to change an Technician string to a lookup table.  So
    after the code ran to create the Technician lookup table I then run an
    action summing query to create records in that table.  Then the code
    created a child table in the database.  Then I ran another query which
    created the child table records from the string value in the work orders
    table joined to the string value in the lookup table.  Finally the code
    deleted the field from the Work Order table.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Thursday, February 07, 2013 9:20 PM
  • A simple methodology that you may consider using.

    Rather than making any modifications to your BE tables in design view in that database, use ddl or DAO in the FE to make the changes and keep this code in a module. Do this in development so that when you release a new FE to your client you know it works and they wil have the same BE table structure.

    As Tony suggest use a verson comparison at startup and if the version has change run the code that makes the changes.

    So you could use code

    On Error Resume Next ' Ignore the error is the field already exist
    
        strSQL = "ALTER TABLE Company Add Country_ID Int;"
        db.Execute strSQL
    The above code is an example from an app that uses a SQL server BE but the same applies for Jet/ACE

    for more exaples have a look at

    http://support.microsoft.com/kb/180841


    Kent

    Saturday, February 09, 2013 12:03 AM
  • Assuming that you have split your database (and if you haven't, you should do) then you could try this Back End Updater code HERE.

    Peter Hibbs.

    Sunday, February 10, 2013 12:40 PM
  • Nice work.

    I like the use of actions based on the version number. In my own BE updater I use tables to hold Tables, Fields and Relationships and it deals with multiple BEs. Bit too complex for most situations so yours is way better. Simple and Elegant.

    In Peter's Updater he allows you to enter a simple instruction like "Make Table" and enter the table name and the field names. Then his code does the work of creating the SQL strings making it easy to use.

    In short - Peter's BE Updater is superb and is exactly what is needed to easily update your BE structures.


    Kent

    Sunday, February 10, 2013 11:26 PM
  • KentGorrell wrote:

    In short - Peter's BE Updater is superb and is exactly what is needed to easily update your BE structures.

    However I like the Compare'Em solution because it figures out when the
    tield, table, index and relationship differences are between two
    databases, that is last release and this release, and generates the DAO
    code for me.  So I don't have to remember to record any of that
    information.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Tuesday, February 12, 2013 5:05 AM
  • I never need to remember because I never make changes in the BE.

    I always make the changes in the FE and let it make the changes to the BE.

    Tony - I can only see a MDE version of Compare-Em and it doesn't handle ACCDBs. Is there a more recent version?

    @ Iscitizen - you may like to respond the these posts just so we know you have not abandoned the thread and maybe let us know if we are on the right track toward answering your question.


    Kent

    Tuesday, February 12, 2013 5:56 AM
  • Hi iscitizen,

    I temporarily marked the reply as answer and you can unmark it if it provides no reply.

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 28, 2013 10:17 AM