TransferDatabase Password?

[Evan_S]

DoCmd.TransferDatabase acImport, "c:\myfolder\Test.mdb", acTable, "tblTest", "tblTest"

The above code works fine, with the exception that upon execution it prompts for a password, since "c:\myfolder\Test.mdb" is password protected.

I really wouldn't like to remove the password protection if at all possibe.
I can't figure out a way to somehow insert the password into the code.
We would like the application to be scheduled to run unattended at night.

Can anyone point me in the right direction.

Thanks,
Evan

 

[namliam]

If you build up a connection to your database using ADO or DAO in VBA you can specify a password.

This probably isnt enough info for you, try searching the forum. If its not clear enough still. I can dig up an example for ya...

 

[Evan_S]

Thanks for the quick reply.

What do you mean by "build up a connection?" Do I have to open the
database first, before I attempt the TransferDatabase?
I'm a little unclear....but that's not unusual.

Thanks again.
Evan

 

[namliam]

No you would do it yourself (in VBA code offcourse) line by line...

 

[ghudson]

Why not link the table? The first time you link the table you will prompted to enter the password, but never again unless you delete the table link. Then Access will store the password with the link but a you will not be able to see or find the password.

 

[Evan_S]

Originally, I did try to link the tables, however I couldn't use the "seek"
command with the linked tables. I also couldn't create a needed "index" on
the linked table.
I did find a solution to that problem posted somewhere, that was supposed to let you use "seek" and "index" but I couldn't get that code to work. There was some cryptic message about not being able to find a file.

Thanks for the reply.

Any insight would be greatly appreciated.

 

[BrettM]

Just found this old thread. Have you got a solution yet? If not then try removing the password first, then do your transfers, then recreate the password.

    Set dbsNew = DBEngine.OpenDatabase("c:\myfolder\Test.mdb", True, False, ";pwd=yourpassword")
    dbsNew.NewPassword "yourpassword", ""
    dbsNew.Close
    Set dbsNew = Nothing
 
    DoCmd.TransferDatabase acImport, "c:\myfolder\Test.mdb", acTable, "tblTest", "tblTest"
 
    Set dbsNew = DBEngine.OpenDatabase("c:\myfolder\Test.mdb", True, False, ";pwd=yourpassword")
    dbsNew.NewPassword "", "yourpassword"
    dbsNew.Close
    Set dbsNew = Nothing

Regards Brett

 

[DCrake]

GHudson:

Then Access will store the password with the link but a you will not be able to see or find the password.

This is not true. If you have existing tables linked to a pasword protected mdb you can discover the password quite easily. How? Well go to Tools & Options and tick to view system objects.

Then open up the MSysObjects table and look in the Connect column and you will see something like MS Access;PWD=Test; against any table that has links to a password protected database. Where "Test" is the password.

That's how secure you back end protection is. Therefore if you distribute an mdb you need to be able to prevent the user from viewing the System Objects.

David