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

    Coping database with a control button (Access 2000)

    Is it possible to have a control button that would copy all of the items in my database (forms, tables, queries, reports) but without the data, like a database template? I would appreciate any words of advice, or just let me know if there are previous threads. I looked but not too carefully.

    Thanks a lot,
    Sarah

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Coping database with a control button (Access 2000)

    Hi Sarah,
    you don't make it clear if you know VBA, but if you do you can use the TransferDatabase method of the DoCmd object with a parameter of acExport + info detailing the destination file, type of object you wish to export etc. (look up the parameters in Help).

    You could loop through the object collections involved, or if there is a small number of them, code the names you want to export explicity. If you loop through the tabledefs collection you might need to check attributes to make sure you're not copying any system or linked tables etc. Oh, and there's a parameter for tables that controls whether to export just the structure or the data too.

    Hope this helps.

    Jeremy

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coping database with a control button (Access 2000)

    Thanks for your reply Jeremy,

    What I've managed to do is find some code that will open a browser dialog box. This code has gotten me half way there as it allows me to manually enter a file name, but the code is set up to open files only and not create any new ones. I know very little about coding (learning more as we speak)...and I was just wondering, and hoping, that you or someone else could take a look at the code and see if I can manipulate it to create a new file, rather than open an existing one. To me, it looks like a whole lot of code so this might just be wishful thinking.

    Thanks so much again for your time and help.

    Sarah
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Coping database with a control button (Access 2000)

    If you manually make a copy of your db, delete all the data from the tables, and keep this copy in reserve as a template, you can then make copies of that template easily using the filecopy command, which you can attach to a button.
    This copies the whole thing in one go, rather than copying each of the objects. You can't copy the file while in use, so the template to copy needs to be separate from the db in use.
    If your db is still being developed then this method would not be much good because you would need to keep updating the template, but if you think it is finished it should work OK.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Coping database with a control button (Access 2000)

    Hi, Sarah.

    The code you have posted seems to be a variation on code I have acquired myself from various sources, and as you say will get you started in terms of locating an existing file.

    Creating a new database object isn't too difficult, but how you do it depends on whether you use DAO or ADO, so I won't go into specifics here - please post if you need help on this.

    You can approach the location of the new database in one of three ways, depending on how much control you need, how flexible you want to make the code, and whether it will always run on your machine with a known folder structure etc. In order of difficulty, you could:
    (i) hard-code a folder path and use this in your CreateDatabase statement;
    (ii) retrieve the current path from your "live" database and use this with a new db name appended;
    (iii) use code like the sample you have posted for FileOpen that lets you select a folder. I think I have an example of this somewhere, so let me know if you need it.

    I would start by using method (i) until you have some working code you can test that actually creates a db object, and populates it with tables, queries etc., then go back and improve what you've done (my usual technique of self-improving code!!).

    Does this take you a bit further?

    Best Wishes

    Jeremy

Posting Permissions

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