Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying tables (A97 SR2)

    Hi

    I am a bit stuck trying to do the following :

    I am running a front-end database and I want to build a procedure to create programmatically new back-end databases for the next financial year when a certain date has been reached. It's easy enough to create the databases, but what I then want to do is copy some tables (structure only) from the current year's back-end database into the new database which I have just created.

    I have thought through various ways of doing this, but have not yet come up with a satisfactory solution. I considered opening another instance of Access, but that does not work (Automation error). Trying to use the Docmd.TransferDatabase command means you have to be in the database you are copying from/to - which I am not.

    All I can think of is :

    1. Importing the tables to the front-end
    2. Exporting them to the new back-end
    3. Deleting them from the front-end

    This seems cumbersome & inelegant - can anyone suggest a better way ?

    Nick

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    Hi Nick,

    I'm sure other Loungers will offer different advice, but let me share my 2 Cents <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>...

    You were on the right track with Automation, especially if you want to do this programatically. It is possible to open another "instance" of Access through code. You can then do anything to the new database that you can do to the current database using DoCmd.... or any other code-based technique.

    It took a little digging, but I remembered an old post I had made with an example of this: <post#=11387>post 11387</post#>. The example opens an existing database. You'll want to create a new database - appAccess.NewCurrentDatabase. You'll also need to use DAO or ADO to create the tables - this will be a little tedious, but it should work fine.

    Hope tihs helps

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    Mark

    Thanks for your prompt reply. I had a look at your code, but the problem I still have is that any attempt to open another instance of Access using either early or late binding causes an Automation error -2147... File not found. This makes me think that it may be something to do with my PC setup - I run Office 2000 with Access 97. I wonder if you have any thoughts on that.

    Nick

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    Hi Nick,

    I just tested it on a computer with Access 97, 2000, and 2002. It worked fine for me. Here's the code I used...

    Dim appAccess as Access.Application
    Set appAccess = CreateObject("Access.Application.8")
    appAccess.NewCurrentDatabase "C:Test.mdb"
    appAccess.Visible = True

    (By the way, you'll want to include error handling code in case the database already exists... In which case, you'll use appAccess.OpenCurrentDatabase "C:Test.mdb")
    Once you've created the new database, you can use DAO to create the tables. After creating or opening another database with Automation (as shown above), you can use CurrentDb() to refer to the newly opened database.

    As for your error, check under Tools | References (within any module or form module). You should have Microsoft Access 8.0 Object Library and Microsoft DAO 3.xx Object Library (assuming you plan to use DAO to create the tables).

    Hope this helps - post back if this doesn't work.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    Mark

    Tried it out on my Pc at home (same setup - Access 97 + Office 2000) & it worked a treat. No problem at all. I have to conclude that there may be some regsitry settings on my PC at work causing it to fail. I shall investigate further & let you know.

    Nick

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    The solution to this problem seems to lie somewhere in the registry. When I try to instantiate another copy of Access - whether from within Access itself or another Office application it keeps coming up with an Automation Error & file not found. We are continuing to investigate - if anybody has come across this problem before, I would be interested to know how they solved it.

    Nick

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying tables (A97 SR2)

    I have a completely different way of approaching this.


    1. <LI>Copy the whole back end database to a new name. This becomes your back up
      <LI>Empty all (or some) tables in the current back end database
      <LI>Continue to use the curent database for the new year.
    HTH
    David Grugeon
    Brisbane Australia

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    Hi David,

    That's a great point - I have also done that on occasion.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    David

    Thanks for the tip - it might be easier to do that than waiting to find a solution for the Automation Error.

    Nick

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying tables (A97 SR2)

    A good suggestion. This worked & completely solved the problem.

    Thanks

Posting Permissions

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