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

    CopyObject (97 SR1)

    I have a database used in 6 different locations spread over a wide area, so I need a way of making updates without having to visit each place. I have successfully sent them a database called update.mdb that just includes copies of any objects I want to replace, and code with a series of docmd.copyobject commands to insert the new objects in the current database.

    Recently I wanted to do it again. All I wanted to do was change one line of code in a module called moduleMain in a database called backup.mdb. This is the code I used:
    Dim SourceFile, DestinationFile As String

    ' first make a backup in case something goes wrong
    SourceFile = "C:clientdatabasebackup.mdb" ' Define source file name.
    DestinationFile = "C:clientdatabasebackupold.mdb" ' Define target file name.
    FileCopy SourceFile, DestinationFile ' Copy source to target.
    DoCmd.CopyObject SourceFile, "moduleMain", acModule, "moduleMain"

    Everytime I run it , Access performs an illegal operation and crashes. The file copy bit has worked OK, and the copyobject line will work if the change the name of the module so that it doesn't replace the existing module.

    Any suggestions why it won't work?
    Regards
    John



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

    Re: CopyObject (97 SR1)

    First an observation. You line "Dim SourceFile, DestinationFile As String" is not declaring both SourceFile and DestinationFile as string, if that's what you're trying to do. It's declaring SourceFile as a variant and DestinationFile as a string. I don't know whether that would make a difference in your CopyObject method, but it can't hurt to change it to a string variable.

    Have you tried deleting or renaming the existing moduleMain before you copy a new one to the destination database? You might also want to experiment with using the TransferDatabase method instead of CopyObject.
    Charlotte

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

    Re: CopyObject (97 SR1)

    Thanks for these sugestions.
    Yes I should change the dim to a string, but it doesn't help with this problem.
    I have tried using Transferdatabase action and it now works OK. I now have the following and it works OK

    Dim SourceFile As String
    Dim DestinationFile As String
    ' first make a backup in case something goes wrong
    SourceFile = "C:clientdatabasebackup.mdb" ' Define source file name.
    DestinationFile = "C:clientdatabasebackupold.mdb" ' Define target file name.
    FileCopy SourceFile, DestinationFile ' Copy source to target.
    ' DoCmd.CopyObject SourceFile, "moduleMain", acModule, "moduleMain"
    DoCmd.TransferDatabase acExport, "Microsoft Access", SourceFile, acModule, "moduleMain", "moduleMain"

    I haven't tried deleting or renaming moduleMain because I can't find a way of deleting or renaming an object in a database other than the one in use at the moment.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CopyObject (97 SR1)

    To delete an object:

    DoCmd.DeleteObject objecttype, objectname

    To rename an object:

    DoCmd.Rename newname, objecttype, oldname

    The parameter objecttype can be one of the following:
    acDefault (default)
    acTable
    acQuery
    acForm
    acReport
    acMacro
    acModule

    The parameters objectname, oldname and newname are strings.

    Example:

    DoCmd.Rename "moduleMainRenamed", acModule, "moduleMain"

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

    Re: CopyObject (97 SR1)

    I had read these commands about deleting and renaming, but there seemed to me to be nowhere in the syntax to say what database the objects were in. To me this implied they had to be in the current database.

    My scenario is that I want to make changes to one database from within another. Can you do that with these commands?
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CopyObject (97 SR1)

    You're correct. The Rename and DeleteObject methods of DoCmd operate on objects in the current database only.

    I don't know if it is possible to delete forms, reports, macros and modules in other than the current database. For tables and queries, you can use DAO - the TableDefs and QueryDefs collections of a database have Delete methods.

    You might open a second instance of Access using Automation, open the other database in that instance as current database and rename/delete the object. But that is not very elegant. Hopefully somebody else has a better idea.

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

    Re: CopyObject (97 SR1)

    OpenCurrentDatabase is the only method I can think of for Access 97.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CopyObject (97 SR1)

    If you created a function in the database backup.mdb that renames or deletes an object you could call it from another database and pass the name of the object to delete or the old name and the new name of the object to rename. I got this code from the online help which could be modified for this purpose.

    "For example, suppose you have defined a procedure named NewForm in a database with its ProjectName property set to "WizCode." The NewForm procedure takes a string argument. You can call NewForm in the following manner from Visual Basic:"

    Dim appAccess As New Access.Application
    appAccess.OpenCurrentDatabase ("C:My DocumentsWizCode.mdb")
    appAccess.Run "WizCode.NewForm", "Some String"

    The project name can be found in the advanced tab of the options dialogue. I haven't actually tried to do this so testing would be needed before rolling out.

Posting Permissions

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