Docmd.TransferDatabase with a password-protected database

Posted on 2006-05-27
Last Modified: 2008-03-17
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.

Question by:jcampanali
    1 Comment
    LVL 58

    Accepted 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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now