Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Table Data w/Autonumber (Access 2000)

    Got a new problem, guys. I have this monster application (about 20 forms, 18 tables, lots of queries and reports) that I've divided into a standard foreground mde file (with only forms, reports & queries) and background mde file (with only tables). There is code in the foreground system to dynamically locate and link to the tables in the background file. There is even code to "delink" and "relink" the tables in the event of a new foreground file replacing a prior version of the foreground mde file. This all works quite nicely.

    The problem arises when I have a change in the background (tables only) mde file -- usually something like a new added column to one or more tables.

    I can't just replace the old background file with the new, because the existing data needs to be copied into the new table set. I can't ask the user to do copies of the data tables, manually, because they can't see the tables (all modules are "locked"). What I need is a programmatic (VBA) technique that can copy old table data to new table structures, while keeping relational pointers (such as autonumbers and their dependent reference variables in other tables) intact; i.e., not renumbering the autonumber fields. Manual copying performs this type of copy quite nicely, but I can't seem to figure out how to accomplish this task from within a VBA module.

    All suggestions gratefully accepted!

    -- Jim

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

    Re: Copy Table Data w/Autonumber (Access 2000)

    I'm not sure why you decided to make you *back end* an mde, since people usually want to protect code, forms, and reports, and "modules" should only be in the front end. Your only option is to send them a new, empty mde backend and tweak your code to link to the empty tables and copy the data from old to new. You might want to build a front end table that contains the SQL required for each table migration, since if you change the order of fields or rename some fields, a SELECT * won't work properly. Of course, you only need specific SQL for those tables that have changed.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Copy Table Data w/Autonumber (Access 2000)

    I've never been able to succesfully do the sort of thing you describe. The problem is that the changes vary from situation to situation as the database evolves, and by the time you figure out a way to automate the process you could have done it several times over manually. We usually make the changes in the test database, and then make the changes in the live database. A further point to ponder - for really robust back-ends, we typically use SQL Server, and that's even more difficult to try to automate. I also agree with Charlotte - having your back-end as an MDE doesn't buy you anything of value. Front-ends we usually deploy as MDEs.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Table Data w/Autonumber (Access 2000)

    Thanks, guys for the quick response. As to why "mdes", I guess it was strictly so all files would be consistent in their naming conventions for the users. I didn't see any disadvantage in making the backend a MDE, so I did it. If there is some disadvantage of which I am unaware, please let me know.

    As to SQL Server, the system is not meant for that quantity of users (not that it wouldn't be nice to find such a robust user!) -- I am assuming that the max frontend connection count will never exceed about 10-15 stations. That should be well within the capabilities of the standard workstation peer-to-peer, frontend/backend capability.

    I guess I am going to have to rethink this entire support process. If anything else comes to mind, please feel free to share your inspirations.

    -- Jim

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

    Re: Copy Table Data w/Autonumber (Access 2000)

    It probably doesn't hurt, but there isn't much point, since table designs are not protected in an mde, any more than the data is. I don't know what you mean by a "standard workstation peer-to-peer" front end/ backend. Peer-to-peer is absolutely the worst way to share an Access database (well, except maybe for a WAN).
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Table Data w/Autonumber (Access 2000)

    "standard workstation peer-to-peer" == Any LAN on which there is no one computer with File-Server software, where any workstation can see shared files on any other workstation attached to the LAN and belonging to the same workgroup.

    In such an environment, I can choose one arbitrary workstation to contain the "backend" data file, and then install the frontend file on all user workstations that wish to share the backend. Why is this such a "worst way to share an Access database"??
    -- Jim

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Copy Table Data w/Autonumber (Access 2000)

    It may work for you, especially if the workstation where the back-end is located is not actually being used by another user. It largely depends on the way the database is being used. If it involves lots of heads-down data entry by 10 to 15 people with an occasional heavy-duty query being run, you are likely to find users complaining about response time. On the other hand if they refer to data in it occasionally during the day, it should work fine. But I would certainly want a 100-Mbit LAN that was rock solid, and I would back up the database at least a couple of times a day if it is important data that changes during the day. I've seen Access databases used by up to 100 users, but it was very casual use. Eventually the response times pushed them into upgrading to SQL Server and they now wish they had done it years ago.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Table Data w/Autonumber (Access 2000)

    Thanks, Wendell. Those are good guidelines.

    Are there any LANs still around that run with less than 100-Mbit? I think I retired my 10-Mbit hub about 5 years ago! <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

    -- Jim

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

    Re: Copy Table Data w/Autonumber (Access 2000)

    Actually, I know quite well what a peer-to-peer network is. My point was that it is a vey poor way to share and Access database, particularly if the machine hosting the backend is also used as a workstation. I've had to untangle a few of these, and I didn't enjoy the experience. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Charlotte

Posting Permissions

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