Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unanswered: Merging Tables...?!?!

    Hi there guys,

    First of all...nice forum you guys got here...very helpful...now here is my problem:

    I am currently updating a table in a database that will be shipped with a VB frontend to several internal users. This database contains other tables that the clients store important information in and each client store their own information in each of these.

    I want to know if there is a way for me to send the the table i am updating (along with the rest of the application) to all the clients without disturbing the rest of the database.

    I don't care if the data in the table i am updating is overwritten on the client side, but the rest of the database should remain intact.

    Any idea!??!

    Thanks,
    KalluMama

  2. #2
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Are all the clients connecting to the same database, or does each client have their own database?

  3. #3
    Join Date
    Apr 2004
    Posts
    9
    Each client maintains their own data...no central data

  4. #4
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    If each client has their own data, then why do you want to send the table to all clients? Is it not easier just to have a centralised database, and then each client only links to their data?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Saila
    If each client has their own data, then why do you want to send the table to all clients? Is it not easier just to have a centralised database, and then each client only links to their data?
    To elaborate on this suggestion, while maintaining your current infrastrture:

    I would recommend having a "central" database that would contain the table you need to update. Then in the ONOpen event of your first form in the client db, check the lastmodified stamp of your client against your "central" db. If the central db is newer, import the new information from your central db to the client db.

    For tips on where to get started, look into the scripting.filesystemobject methods in the help text.

  6. #6
    Join Date
    Apr 2004
    Posts
    9
    Originally posted by Saila
    If each client has their own data, then why do you want to send the table to all clients? Is it not easier just to have a centralised database, and then each client only links to their data?
    Saila/Teddy: Unfortunately this is how the database was originally created by someone else, now i have inherited it and need to make changes.

    I could just export the table to all their machines but that usually creates a new table with the same name and adds '1' to the name. It also breaks any relationship that might exist.

    I figured a way to write a macro to do the imort/export of the table and also took care of the file name with that macro...but i still need to fig out to do recreate the broken relationship between the imported table and another one.

    The central database solution will not work in my case...since many users do not have internet access.

  7. #7
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    If they don't have access, how were you planning to send the tables through?

  8. #8
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    You could use the macro/query you are currently running, just delete/rename the original table before you import the new one. Not too sure about recreating the relationship. But if there is no connection, then someone has to run the macro manually, so just teach them how to recreate the relationship.

  9. #9
    Join Date
    Apr 2004
    Posts
    9
    Originally posted by Saila
    If they don't have access, how were you planning to send the tables through?
    They all have MS Access, they all do not have INTERNET access all the time, so that solution might not work...

  10. #10
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    In your macro, were you importing and then renaming? I think that if you delete/rename the original and then import the new table it may keep the relationship, never had to do this, so I'm not hundred percent sure

  11. #11
    Join Date
    Apr 2004
    Posts
    9
    Originally posted by Saila
    In your macro, were you importing and then renaming? I think that if you delete/rename the original and then import the new table it may keep the relationship, never had to do this, so I'm not hundred percent sure
    I tried it, i first imported the table and then renamed the table, but the relationship is gone...i guess what i now need is to fig out how to recreate the replationship through a macro....any ideas?

  12. #12
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Playing with it at the moment, why did you have to rename the imported table?

  13. #13
    Join Date
    Apr 2004
    Posts
    9
    Originally posted by Saila
    Playing with it at the moment, why did you have to rename the imported table?
    Say the existing table is called myTable, then the imported table becomes myTable1, the VB frontend is looking for myTable. So i rename myTable to something else and rename myTable1 to myTable. But at this point the connection between myTable and mainTable is broken.

    If I could fig out a code to recreate that conncetion i will be set!

  14. #14
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Try renaming myTable to oldmyTable, then import the mytable.

  15. #15
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    ok, that doesn't work either...

Posting Permissions

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