Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make a copy of the current database automatically (A2000)

    I created a database with a form with 9 buttons. The fifth step presents an input box so the user can create a name for another database that will be created and loaded with many of the objects from the current database. Since I want the entire process to be as 'hands off' as possible for the user, I was dismayed when I discovered that references on the new database always have to be juggled for the code to work properly.

    I finally decided that instead of creating a new database and exporting objects to it I would simply copy the entire database to the new name, thereby avoiding the references problem.

    I would still like this process to be automated, avoiding the user having to go to Windows Explorer and manually copying the database. I thought of capturing the path and filename of the source database and prompting for the new copy database name -- then create a function or macro from this information and export it to the utility database to be run to make the copy. Am I making this more difficult than necessary to keep it automated -- after all, you can't copy a database if it's open. Any thought and/or tips on how to accomplish creating a macro or function from the captured info. would be welcomed.

    Thanks,

    Randy

  2. #2
    Lounger
    Join Date
    Jan 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make a copy of the current database automatically (A2000)

    Your database is the tables. Everything else can be copied (drag the origingal in explorer to create a complete copy and rename )or exported to the new database. Now all that is left is for your user to create a new table and I think that the process of creating you a new table gives you the choice of current or other db.
    The create new table can be done with an SQL expression in code which can be done by creating the table via a query, switching to SQL view and copying the code. I'm fairly sure that you could add the new table name to the SQL. My difficulty is that I am unable to remember the detail and would have to spend half an hour fiddling and testing to get the precise syntax. I hope this gives you some pointers if no one else gives you a more precise answer. The target db name and path could be put in a string, but the syntax of including a string within an SQL is a bit hairy.
    Hope this helps.
    Mike

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make a copy of the current database automatically (A2000)

    Thanks for your reply. I will elaborate somewhat.

    The entire process is broken up into several steps, all performed with it's own respective button. I want to have the user complete some of the process, then make a copy of the database with a different name, where they will complete the process a bit differently than on the original database.

    I currently have some code that creates a new empty database, and copies all of the objects to the new database. The problem with that is that References (the dirty word) need to be set up on the new copy. I don't want to encumber my users with that task, so I figure that since making a copy of the entire database preserves the References, that would be the way to do.

    I hope this clarifies what I am looking for. I thought of creating a utility with VB6.0 that the user could open from the database that would store the current path and mdb file name, and prompt the user for the new copy name. The user could then shut down the database and press the button on the VB form to make the copy, followed by code to automatically re-open the first database to complete the rest of the steps.

    But I was wondering if anyone out there had a better idea.

    Thanks again,

    GHH3RD
    Randy

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

    Re: Make a copy of the current database automatically (A2000)

    It would probably be simplest to just compact your database to the destination path and filename the user designates. That leaves your current database intact but gives you s compacted and (fairly ) clean copy as a backup.
    Charlotte

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

    Re: Make a copy of the current database automatically (A2000)

    Charlotte's approach is the simplest and most elegant <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> - but it does require that the user have exclusive use of the database at the point where the compact/repair process is going on, and also cannot be done from code within the current database. I seem to recall a thread where a similar process was going on, and it included programatically setting references, but a quick search didn't find it. By the way, it is generally possible to copy an Access database when it is open by another user - the multiuser capabilites make that possible.

    I'm curious about some aspects of this. For one thing, why do you want to copy the database to begin with? Is your database normally multiuser, or single user? And are the users storing the database on their own workstation, or somewhere else on the server?
    Wendell

Posting Permissions

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