Have VB open Access db with pw, import objects from another

[Ahliana]

I am trying to have a vb app open one access database, read a table that is a list of modules and forms in another database, then import those objects from a second access database. Even though the objects already exist in the first db, I want to import them, rename the originals, rename the new copies to the original names, then delete the originals (per the instructions I have been given).

I don't know if I need to open an object for the second database or not.

Right now I'm just trying to get to the first database. They are password-protected. I can get code to open it, but it will always ask for the password.

I have tried using the Microsoft Access 10.0 Object Library, as well as the Microsoft DAO 3.6 Object Library.

I have tried instructions as they appear on the Microsoft site regarding "How to open a password-protected database through Automation in Access 2000" (

http://support.microsoft.com/kb/235422/EN-US/).

'This is the Microsoft code
Option Compare Database
Option Explicit

Sub OpenPasswordProtectedDB()

   'Define as Static so the instance of Access
   'doesn't close when the procedure ends.
   Static acc As Access.Application
   Dim db As DAO.Database
   Dim strDbName As String
   strDbName = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   Set acc = New Access.Application
   acc.Visible = True
   Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=nwind")
   acc.OpenCurrentDatabase strDbName
   db.Close
   Set db = Nothing
End Sub

My current attempt produces User-defined type not defined at the declaration, even though I have both references set.

My current attempt at code:

    Dim oAccessApp_ftlui As Access.Application
    Dim db As DAO.Database
    Dim sFTLUImdb As String
    Dim sTemp As String

    'strings get set in other code not shown, but should be good


            Set oAccessApp_ftlui = New Access.Application
            oAccessApp_ftlui.Visible = False
            Set db = oAccessApp_ftlui.DBEngine.OpenDatabase(sFTLUImdb, False, False, ";PWD=" & sTemp)
            oAccessApp_ftlui.OpenCurrentDatabase sFTLUImdb

I haven't managed to find something that will open it without having to log in, much less how to manipulate the objects.

Any help is very much appreciated.

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach

 

[TheAceMan1]

How are ya OpenPasswordProtectedDB . . . . .

I ran a simulation using the 1st code block you provided (Access 2K, Access 9.0 & Microsoft DAO 3.6 Object Libraries), opening a [blue]password protected[/blue] database of my own, and . . . . [blue]No Problemo![/blue]

Now, [blue]DBEngine[/blue] is a [blue]property[/blue] of the [blue]Application Object[/blue] and therefore requires your [purple]Access 10.0 Object Library[/purple]. Your database object (Dim db As DAO.Database) requires the [purple]Microsoft DAO 3.6 Object Library[/purple]. [blue]So you need both libraries for it to work![/blue]

Double check your paths and remember the password is case sensitive (you'd get a PassWord Invaild Error if ts wrong anyway).

Try steping thru the debugger and see [blue]which line fails[/blue].

See Ya! . . . . . .

 

[Ahliana]

Hmmm, I'm still having problems. It still asks me for a password. However, parts of the puzzle that I tend to forget about when I focus on a specific aspect:
[ul]
[li]Access security is in place, with a Security.mdw. I didn't write this part, and although I've read a bit about Access security, I'm no expert there.

[/li]
[li]There is a UserID being used. Access automatically brings it in based on the last user in the registry, but I can also provide it manually.

[/li]
[li]There is code that runs when the Access app opens normally that I don't want to run when I go to copy these objects from the update mdb into the main one. Having it running means I can't copy the objects I want to, which is the point of my whole exercise in going to VB to create this executable.

[/li]
[/ul]

This is an Access app we have deployed worldwide. We send updates automatically. We have code to update the objects while the app is open, since this is done without user intervention. Unfortunately, any objects that are loaded into memory from the startup code can't be copied in, Access throws a Duplicate Definition error. Hence the external VB app to update those objects (several modules and a form).

I am trying to do this invisibly to the user. The Access app is open, they request an update, and we process the file without them having to log back in again.

In another VB app that deals with this database (it backs up the database and related files), I have been able to shell to the Access app by finding the lnk file and passing it the id and password. Now I want to import objects into this Acccess database from the mdb with the updated objects. I can't see a way to do that without a handle to an Access.Application object.

Thanks again for any suggestions (well, anything more helpful that take hike...). If you can suggest a better way to do this, I'm all ears (which makes it hard to find clothes to fit, but what the hey).

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach