Deleting Objects from a password protected database

You have to connect to the database using proper credentials.

Why would you delete them, rather than just update them? I'm curious ...

Manipulating objects in a protected database would sort of defeat the purpose or protecting it in the first place, wouldn't it?

Hi,
If you have the password, then you can unprotect the file, do the required changes and you can set the password back. If you don't have it but still you're able to access the data, you can import the data into a new database where you can add the forms and the other stuff that you need to have.

Hi all, thanks for your replied,

I want to be a bit clearer. There are multiple back end databases, one for each year going back to 2002, that I'm making more secure by using custom ribbons, disallowing the bypass key, hiding tables, and so on. This means exporting objects to these .mdb files. I don't want to do this by importing and exporting into each individual file, so I've written some VBA Code with a loop to do this for me.

The thing is that each of these .mdb files is password protected. So here's the code that I'm using to export these objects.

Sub SendBackEndObjects(strDBName As String, strPWD As String)
Dim wrkDefault As Workspace, dbsNew As DAO.Database

Set wrkDefault = DBEngine.Workspaces(0)
Set dbsNew = wrkDefault.OpenDatabase(strDBName, False, False, IIf(strPWD = "", "", ";pwd=" & strPWD))
'Export the needed tables and queries

DoCmd.TransferDatabase acExport, "Microsoft Access", "" & dbsNew.Name & "", acForm, "frmSplash", "frmSplash", , True
DoCmd.TransferDatabase acExport, "Microsoft Access", "" & dbsNew.Name & "", acForm, "dlgBPK", "dlgBPK", , True
DoCmd.TransferDatabase acExport, "Microsoft Access", "" & dbsNew.Name & "", acModule, "Bypass Key Functions", "Bypass Key Functions", , True
DoCmd.TransferDatabase acExport, "Microsoft Access", "" & dbsNew.Name & "", acTable, "USysRibbons", "USysRibbons", False, True




dbsNew.Close
Set dbsNew = Nothing
Set wrkDefault = Nothing




End Sub

Select allOpen in new window

This works fine.

BUT I also want to make sure that I'm REPLACING these objects, in case I need to do it again in the future. Maybe I've changed a form or table . For that I need to remove the old objects (I think) before I run the Transferdatabase command. (I haven't tried this without doing this. I will attempt this just to see what happens).

Eduard, your suggestion is good, but is there no way to remove an object from a password protected .mdb file??? I found ways to import, export, to hide tables and so on...

Anyway, this is what I'm looking to do. Let , me know if you have any suggestions.

Thanks again,
Eddie

Hi,
Thank you for the explanation, but it doesn't cover all the context. How you're supposed to run the update scripts? It's the database really encrypted, password protected, or do you have just a password that should be used for the connection string?

Eddie,
Are you sure that merging the old databases wouldn't be a better solution?  I guess at this late date, it depends on how many forms and queries you would need to change to filter for the year the client wants.

This sort of reminds me of one of the grunt jobs that trainees got to do in my first job as a programmer back in 1968.  The company (The Hartford Insurance Group) kept 7 years of history and over time, file formats changed.  So every time something in the current file format changed, one of us got the job of using a utility we had created to convert 7 years worth of back up tapes to the current format so that the current programs could always look at the old data if we needed them to.  This was actually how I met my husband.  He was working in a different group and although I had seen him around, we hadn't actually met.  I came into the office one day and his desk was surrounded with three skids of 9-track tapes stacked about 5 feet high.  There were hundreds of them.  Apparently he was doing one of these historical conversions and had made a mistake and the lead operator thought it would be amusing to send the "mistake" up to the 14th floor for every one to see.

I don't think TransferDatabase will overwrite an existing object, so you're correct when you say you must delete them.

I'd create a second function that attempts to delete the object:

Function DeleteObjectFromDatabase(dbs As DAO.Database, ObjectName as string, ObjectType as acObjectType) As Boolean
  On Error GoTo Err_Handler
  dbs.DoCmd.DeleteObject ObjectType, ObjectName

  Err_Handler
    '/ trap errors here
End Function

Select allOpen in new window

That's air code so you'd have to ensure it works correctly.

From there, you'd just call DeleteObjectFromDatabase for each object you want to replace:

DeleteObjectFromDatabase newDBS, "frmSplace", acForm
DeleteObjectFromDatabase newDBS, "frmBPK", acForm
and so on ...

Of course, if someone has the database open you may not be able to do this.

Hi everyone,

Thanks SO much for your responses. So I've done some digging on my own and here's what I found...

TransferDatabase DOES replace objects. News to me, but I tested this and it works. So that takes care of my immediate need.

The second thing I found (not tested) is that... Docmd will work off an Application Object. You can Open a Current Database of a separate Applicatoin object with a password, as in...

 Set appAccess =  CreateObject("Access.Application") 
 appAccess.OpenCurrentDatabase strDB, False, strPWD 

Select allOpen in new window

You can then do a Docmd.DeleteObject (or any other Docmd command) off the Applicatoin Object, as in AppAccess.DoCmd...

I believe you have to set the Application Object's visible property to False if you want all this hidden. But again, not something I've tried. Curious if anyone knows the answer to this.

Scott, are you sure you can do a DoCmd off a database object? I've been looking for that, as that would have solved my problem a while ago. I just couldn't find it.

Anyway, thanks for the help. It looks like I can move ahead with what I found.

Eddie

You're right, had a brain frack! You have to use an Application object to run DoCmd like that.

I'm not sure about the Visible property, but it'd be simple enough to test it out.

No points awarded?