Delete object in other database not working

[AlexMidd]

Well, I have tried and tried and tried! I am trying to export objects to another database but this has always produced an general error that causes Access (97) to shutdown. This is a known problem and there is a fix in the Microsoft Knowledge Base (Article Q160875) that gives code to delete the object from the other database, if one with the same name exists there, before doing the export.

The only problem is, my databases are secured. When I try to open the other database to delete the objects, it says that I do not have permission to use them, even though I am already in a database using a valid user name and password.

I have tried to get around this by using the following code, but it still refuses to work and deletes the objects from the CURRENT database, not the other database, thus the object I wanted to export no longer exists. Any ideas?

    Dim OtherDB As Access.Application
'    DbToSync = Name of other database, as chosen from Open File dialog

    Dim lngReturn As Long
    lngReturn = Shell("""C:\Program files\Microsoft Office\Office\MSACCESS.EXE"" " _
     & "/wrkgrp c:\SmartCert\System_a.mdw /User DBAdmin /Nostartup")
    DoEvents
    Set OtherDB = GetObject(, "Access.Application")
    OtherDB.OpenCurrentDatabase DbToSync
    Set Rst1 = Dbs1.OpenRecordset("qrySynchronisation_Selected", dbOpenDynaset)
    With Rst1
        .MoveFirst
        While Not .EOF
            If .Fields("Type1").Value = "Scripts" Then
                    objType = acMacro
            ElseIf .Fields("Type1").Value = "Forms" Then
                    objType = acForm
            ElseIf .Fields("Type1").Value = "Reports" Then
                    objType = acReport
            ElseIf .Fields("Type1").Value = "Tables" And Left(.Fields("DocName1").Value, 3) = "qry" Then
                    objType = acQuery
            End If
            objName = .Fields("DocName2").Value
            OtherDB.DoCmd.DeleteObject objType, objName
            DoCmd.TransferDatabase acExport, "Microsoft Access", DbToSync, objType, objName, objName, False
        Wend
    End With
    OtherDB.CloseCurrentDatabase
    Set OtherDB = Nothing

Rst1 is a query which contains objects that the user has chosen to export to the other database, so the code is looping through this recordset, checking to see if the object already exists in the other database and deleting it if it does, then transfering the object form the current database to the other database.

** NB ** The reason there is no /password switch in the lngreturn part is for security reasons. A logon dialog does appear when this code is run but Access seems to ignore this and open the database anyway, then opens a THIRD database once the logon has been completed. Have fun! )

Alex Middleton

 

[nickjar2]

I don't know if this may be what you're looking for. Try this URL:

http://support.microsoft.com/support/kb/articles/Q207/7/93.ASP?LN=EN-GB&SD=gn&FR=0

Here there is a file to download called Secfaq.exe. Once downloaded and opened in Word there is a section:
46. How can I open a database in code that was secured using another workgroup file?

You may find some code here that will do the trick.

Nick

 

[AlexMidd]

The problem with this is that it puts the database into an object declared as a Database object, where I need to put it into a an object declared as an Access.Application. The reason for this is that the DeleteObject method is not available to the Database object whereas it is available to the Access.Application object.

I presume I could use the Database object and loop through all its Containers until I find the selected objects, then delete them using the normal Delete method available to the Database object. this, however, seems unnecessarily complicated when it should be that the objects can be deleted by name, as the name is already known. Another one of Access' weaknesses! Have fun! )

Alex Middleton

 

[AlexMidd]

I have just tried the above and, of course, it does not recognise the Delete method. I can only do this using DeleteObject, which has to be done from an Application object, not a Database object.

So, it seems to be the usual Catch 22 situation: I can only open another database with a workgroup file as a Database object but I can only delete an object from the database within an Application object. Have fun! )

Alex Middleton