Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Easier to split tables (2002)

    Hi,

    I have a database that contains spilt tables as well as tables in the database itself. The database resides in 6 different offices and is running fine. There are going to be some changes/additions made to the database that will require adding tables, some of which need to be split off. Presently when I've done this, I've created and tested the tables, then imported into the database containing the split tables and relinked them to the main database. This works fine but it tedious and time consuming to try to get it done is 6 different offices on mulitple computers. Is there a way in code for me to be able to do this. I'm thinking if there was a command button that the user could click (after making a backup of their database and split tables) which would split the tables that need to be split.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Can you explain what you mean by a "split" table? Do you mean linked tables?
    <hr>I'm thinking if there was a command button that the user could click (after making a backup of their database and split tables) which would split the tables that need to be split. <hr>
    I'm sorry but I have no idea what you mean by this. Do you want to relink tables or what?
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Hi Charlotte,

    Yes I mean linked tables. I called them "split" since Access refers to the database wizard as "database splitter".

    In order to make the changes/updates to the database that the user wants, new tables will be created. There will be approximately 7 new tables. Out of the 7 tables, 4 will be split off and linked and 3 will remain in the database as they are used to hold temporary data. I am looking for a way to split the tables off into the already existing database that contains the already existing linked tables. My goal is not to have to do this one table at a time by importing the tables and then relinking as it would require doing this on 12 machines in 6 different offices. I was hoping there was a way to do this with code.

    Thank you,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    You said the "database" resides in 6 offices. Which database, the front end (which contains the linked tables) or the back end to which you link? If you have the database with the actual tables on a server and the 6 offices link to it, that is a different problem then if you have a separate back end file with users linking to it in each of 6 offices.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Each of the six offices has its own server, with 2 to 20 workstations depending on the size of the office. The information that is contained in the split tables (I'm assuming by this you mean back end) is specific to each of the offices. With that being said, each office has the front end database on the workstations and they link back to the back end database on the server. The structure of the database is identical in each of the offices.

    When I make the requested additions/changes to the database, all six offices will need those changes. I am trying to avoid having to manually import the new tables into the back end database on the six servers and then relink them on each workstation.

    I hope that makes sense.

    Leesha

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Easier to split tables (2002)

    As I see it, your biggest problem will be updating the frontend. Apparently the tables that reside in the frontend are specific to that user, since you want to preserve them.

    I would update the backend directly, then have a new frontend that was linked to the backend; using the UNC designation for the database location (rather than mapped drive letters), so it won't have to be relinked when copied down to each user's PC. Save the old frontend for each user, copy down the new frontend, then copy in those tables from the saved frontend.

    BTW, I'd strongly suggest that the only tables you keep in the frontend are temporary tables. Store any personal settings in the registry.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    To reduce confusion as you go along asking for assistance, I would strongly suggest you break yourself of the habit of referring to linked tables as "split" tables. The wizard is called a database splitter because it splits a single database into two, with the data tables in the new back end and linked to the front end. The standard way to refer to these tables are "linked" or "attached", and anyone who can help you will understand those terms immediately without further explanation.
    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Hi Mark,

    The frontend tables are temporary tables that are used to run various reports. The data is not stored for any reason but is simply used in that moment by that user to run the report based on data they have entered into the table on their workstation. Each workstation contains the same temporary tables.

    My goal was to avoid having to do each workstation individually or each server as it will take a huge amount of time. It seems that will have to be the case.

    I wasn't quite sure what you meant by UNC designation vs mapped drive letters. Presently we use mapped drives.

    Thanks,
    Leesha

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Easier to split tables (2002)

    Leesha,

    Since the frontend tables are just temp tables, updating each user with new frontend is easy. After you have finished making changes to the backend, put 1 copy of the frontend on the server. Link the tables to the backend. Except that when you do the linking, don't select the BE database using the mapped drive letter. Select "Network Neighborhood" (or whatever it is called depending on which version of Windows you have). This makes the linking independent of the drive mappings. then merely copy this "standard" FE database down to each user's PC. In many of my installations, I put a .bat file in Windows Startup folder that will do this copy each time user starts the PC.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    I'm sorry Mark, I think I got my frontend and backend mixed up. The tables on each workstation are temporary. Those have been easy to update. The tables on the servers are not temporary but contain data that is specific to each office. When the additions to the program are made, it will mean new temporary tables on each workstation and new tables on the server. I am trying to find an easy way to update the servers tables (there are offices therefore 6 servers) without having to import and link the talbes individually on all 6 servers.

    Leesha

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Easier to split tables (2002)

    Partly my fault, I didn't realize you had 6 servers.

    You can update BE database from code, using DAO and/or SQL, and even use docmd.transferDatabase to copy-in new tables. Depending on your situation, this may also include defining new relationships between tables, creating new indexes, new fields, etc. If you've never done this before, it might be easier just to manually do it at each site. If this looks like this will happen frequently, you might want to learn to do it all. Unfortunately, there are no magic utilities to do this; you will have to set-up the code yourself (although you might be able to find code samples). I've done it some of it infrequently (and some not at all), so I have to look it up each time myself.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    I'm not sure how frequently it would happen, but I am interested in learninghow to do it in code. Could you point me in the right direction of the sources/books you used to learn the code.

    Thanks!

    Leesha

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Easier to split tables (2002)

    Leesha,

    Have a look at New Tables and Fields via VBA - Part 1 and following parts.
    Francois

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Thanks!! I appreciate the reference!

    Leesha

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Easier to split tables (2002)

    Hi Mark,

    At your suggestion re using the docmd function I did a macro and then converted the macro to VBA code to see how it would look. Not only did it allow me to copy the test table but it also deleted it from the database it was originally in as it will now be a linked table. I didn't see any way in the macro to link the table after these two steps are completed and wondered if you knew any off the top of your head. I'm on the hunt for that piece of code or at least how to set it up and figured I'd start here. I'm 3/4 of the way to where I want to be and am most grateful for your comment on the docmd.

    This is the code I got from the macro:

    DoCmd.CopyObject "C:SSISSSIS Split TablesSSIS Split Tables_BE.mdb", "table1", acTable, "Table1"
    DoCmd.DeleteObject acTable, "Table1"

    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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