Problem using TransferDatabase method from Password Protected database

[ria.arora]

My both databases (Source database and destination database) are password protected. While importing it's prompting for the password which I don't want. I have tried lot of options but no success. Please help...
Below is the code for your reference.

Private Sub ImportFromAccess(sDatabaseID As String, sSourceTblNm As String, sDestTblNm As String)
    On Error GoTo ErrHandler
    Dim objApp As Object
    Dim strDeleteSQL As String
    Dim priorYear As String
    
    Dim strSourceDatabasePathNName As String 'Source Database Path and Name, from where data need to imported
    Dim strSourceDatabasePassword As String
    
    Dim strDestDatabasePathNName As String 'Destination Database Path and Name, where data need to exported
    Dim strDestDatabasePassword As String
    
    Dim oDestDB As DAO.Database
    
    strSourceDatabasePathNName = ExtractDatabaseDetails.GetDatabasePath(sDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(sDatabaseID)
    strSourceDatabasePassword = GetDatabasePassword(sDatabaseID)
    
    strDestDatabasePathNName = ExtractDatabaseDetails.GetDatabasePath(gsPnPDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(gsPnPDatabaseID)
    strDestDatabasePassword = GetDatabasePassword(gsPnPDatabaseID)
'    If gsPnPDatabaseOpened = False Then
'        ConnectPnPDatabase
'    End If
    Set objApp = New Access.Application
    'Open current database to export the table from this opened database to destination database
    objApp.OpenCurrentDatabase strSourceDatabasePathNName, , strSourceDatabasePassword
'    objApp.OpenCurrentDatabase strSourceDatabasePathNName, False
'    objApp.Visible = False
    
    Set oDestDB = DBEngine.OpenDatabase(strDestDatabasePathNName, _
                    False, False, "MS Access;PWD=" & strDestDatabasePassword)
    Me.Repaint
    'Export Login Form one database to another database
'    DoCmd.CopyObject strDestDatabasePathNName, "frmLogin", acForm, "frmLogin"
    'Export the needed tables and queries
    'Parameters
    'strDestDatabasePathNName - Destination database file name with full path
    'sSourceTblNm - Table Name from Source Database
    'sDestTblNm - Table Name from Destination Database
    'False - You're importing all the data as well, True would just import the structure
    objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _
                                        "Microsoft Access", _
                                        strDestDatabasePathNName, _
                                        Access.AcObjectType.acTable, "" & sSourceTblNm & "", "" & sDestTblNm & "", , False
    Me.Repaint
'    DoCmd.TransferDatabase acExport, _
'        "Microsoft Access", _
'        strDestDatabasePathNName, _
'        Access.AcObjectType.acTable, _
'        "" & sSourceTblNm & "", _
'        "" & sDestTblNm & "", , False
    Set objApp = Nothing
ExitHandler:
    DoCmd.Hourglass (False)
    Exit Sub
ErrHandler:
    MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
    lblStatus.Caption = Err.Description
    DoCmd.Hourglass (False)
    varStatus = SysCmd(acSysCmdClearStatus)
    Resume ExitHandler
End Sub

 

[boblarson]

Take a look in the Access VBA help file for the TransferDatabase command. There is a setting at the very end of it which, if set to TRUE, should let you run it one time - put the password in, and it will store it from that point on out. One thing to note, you will want to completely remove anyone's access to the Database Window / Navigation Pane because that password is stored as open text in the MsysObjects table.

 

[ria.arora]

I have changed this to True but still asking for the password.

    objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _
                                        "Microsoft Access", _
                                        strDestDatabasePathNName, _
                                        Access.AcObjectType.acTable, "" & sSourceTblNm & "", "" & sDestTblNm & "", , True

 

[boblarson]

I have changed this to True but still asking for the password.

    objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _
                                        "Microsoft Access", _
                                        strDestDatabasePathNName, _
                                        Access.AcObjectType.acTable, "" & sSourceTblNm & "", "" & sDestTblNm & "", , True

A couple of things -

1. Did you type in the password when it asked? You have to do that at least the first time so the TRUE part about storing it will then let it store the password.

2. On an unrelated note, why do you have quotes like this - "" - around your sSourceTblNm and sDestTblNm? You don't need them. If those variables are strings then the quotes are not needed, in fact you do not want them or it can mess things up.

 

[ria.arora]

Hi Bob

Yes I entered password manually and when I tried running the same code again then its asking for the password

I'll try to run the code with "

 

[spade]

Hi Ria, I have the same problem. Did you ever find a resolution to this?