Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    3

    Question Unanswered: How to move and validate data between databases

    Hello everyone,

    I am quite new to access, if not to vba or databases in general. I have an Access master DB and several Access client DBs. My problems so far:

    1. the master db is supposed to delete several tables (and data) in the client dbs and replace them with its own tables (and data)
    2. the master db is supposed to read data from the clients, to compare them with its internal data, and to update either itself or the client.

    I tried to read into the subject and got totally overwhelmed by the mass of ways to exchange date (i.e ADODB Connections & execute on those, link tables via createTableDefs & append, docmd.transferDatabase) and got none to work.

    What would be the easiest way to solve my problems? Any hints, links, etc. are highly appreciated.

    Thank you all in advance,
    Martin

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Can I ask why you have copies of data as master and client??
    why dont think of making a back-end share for all clients as front-end data base?
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Dare I mention Replication? Personally I don't like it because I am not the one in control, but it might answer your needs
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2008
    Posts
    3
    Quote Originally Posted by Aran1
    Can I ask why you have copies of data as master and client??
    why dont think of making a back-end share for all clients as front-end data base?
    Mainly this setup has existed for a while, with employees exporting from their databases (clients) to excel and an human validator (secretary ) entering these into the master db. I would love to kill the whole setup, but am bound by management to live with it

    So it's either connecting those dbs or keep doing it by hand.

    Also the client dbs provide some kind of offline functionality to the employees working remote.

    Thanks for your input,
    Martin

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Have you inherited this project from another developer? Is it possible that the Client dbs are already replicas? If so, Access will do the updating automatically.

    I have never used Replication in an application. I only know the theory. From what I have read, there are definite pitfalls with using replication. But maybe someone here can help you identify if the databases are already set up for replication. One thing I kind of remember is a UID (universal ID?) that is ridiculously long. If your tables have something like that in it then replication may already be configured.

  6. #6
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    You can simply go to replication option menu and see if all items in menu is enabled then your db is a replica but if the "Recover Design Master" item is disabled then your db is a design master.
    You can also see your db components if they have a synchronizing tag like shortcut tags on them then your db is replicated.
    And finally if you want to upgrade your db to 2007 in future then be careful as 2007 doesn’t support replication
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  7. #7
    Join Date
    May 2008
    Posts
    3

    Lightbulb Solved.

    Thanks for the tips - i am already working with Access 2007, so the replication was no solve either.#

    I finally decided to kill all my pervious attemts and startet from scratch using mainly currentDb.execute (INSERT /DELETE... IN <PathToClient> SELECT) statements to update the clients [Was: Problem 1]

    and a recordset-loop that compared client-records to a query-created recordset (using FindFirst) to check for changes in records [Was Problem 2]

    Aint'n nice, but it works.

    Thanks again, hope to see you on my next hail-for-help,
    Martin

  8. #8
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    In fact I had a problem similar to yours and looking for an index to tell me if the table is altered or not and then decide to start updating but there isn't such an index, in addition that recordset-loop is a very long exhausting loop.
    I have an Idea, make a table (say tbl_modification) in the master db and list all tables name in master db in this table(i.e tbl_modification), put code in your forms to put date in front of each table that is being altered, also each time you update copy databases put the date of update in the third column of this table(for each copy of db one column and for master db one column) so each time you want to see if data has been changed in master db you just simply compare two dates in two columns (i.e master column date and related copy column date) if update date is before altering date then you should update else no data update required.
    This arrangement I believe will work and is faster than that recordset-loop.
    you can even see which table is altered and decide to update just those tables that have been mdified since last updating.
    Hope I made good illustration to get my point.
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Glad to hear it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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