Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1809
  • Last Modified:

Docmd.TransferDatabase with a password-protected database

I have code in a hidden form that opens upon startup of a users front-end Access DB.  That code looks for the appropriate linked tables.  If they are not there, a dialog box lets them know they need to select the "Back End" data and an explorer window opens.  The user navigates for, locates, and double-clicks the appropriate Back End DB.  No problems with this code for years.  Now, I am linking to a third party app DB from a custom front-end I developed.  Their DB is pwd protected and I have the pwd (legally, of course).  When the following code runs, I am prompted for the DB password.  I enter the password and click "OK", but the table never links.  When I unset the DB Pwd on the Back End, the code runs fine.  I've tried ODBC, but, of course, Access doesn't like to link to Access through ODBC (Runtime 3423).  Any thoughts?  I'm sure I need to elaborate on my code with Pwd-protected DB.

DoCmd.TransferDatabase acLink, "Microsoft Access", tableName, acTable, "Accounts", "Accounts", False

tableName is a defined variable, just so you know.

1 Solution
Hello jcampanali

Apparently, there is a problem, yes. I did not find a way to make DoCmd work. I guess you will have to create your linked tables like this:

    Dim strConnect As String
    Dim tdf As DAO.TableDef

    strConnect = "MS Access" _
        & ";PWD=guesswhathisis" _
        & ";DATABASE=" & strPathAndNameOfMDB

    With CurrentDb
        ' .TableDefs.Delete "Accounts"   ' if needed (delete old)
        Set tdf = .CreateTableDef("Accounts")
        tdf.Connect = strConnect
        tdf.SourceTableName = "Accounts"
        .TableDefs.Append tdf
    End With

You can also refresh the link of an existing tabledef (with a new path and/or password) like this:

    With DBEngine(0)(0).TableDefs("Accounts")
        .Connect =strConnect   ' same as above
    End With

Hope this helps!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now