Solved How can I convert a password protected accdb to accde using VBA

[VBANewBie :)]

Hello Everyone , I use this code to convert unprotected accdb to accde :

Function Convert()
Dim sourcedb, targetdb, nametargetdb As String
Dim MyPath As String
MyPath = Application.CurrentProject.Path
sourcedb = MyPath & "\Baseet.accdb"
targetdb = "D:\tt2.accde"
nametargetdb = MyPath & "\Baseet.accde"
Dim accessApplication As Access.Application
Set accessApplication = New Access.Application
With accessApplication
.SysCmd 603, sourcedb, targetdb
End With
Kill sourcedb
Name targetdb As nametargetdb
FollowHyperlink nametargetdb
DoCmd.Quit
End Function

It works fine but I need to modify it to work with password protected accdb and pass the database password without showing the access input box for password .
Thanks

 

[VBANewBie :)]

Up

 

[CJ_London]

don't think you can - syscmd has 3 parameters and you are using all 3 - 603, sourcedb, targetdb

nowhere to open the file without manually entering the password

syscmd 603 is undocumented and there is very little to find on the web as I'm sure you know. So perhaps try a different approach?

just a suggestion, open your source using something currendb.opendatabase or dbengine.opencurrentdatabase or similar - you will need to experiment but they both enable you to pass the password.

then modify your syscmd accordingly

however no idea if it will work,

 

[arnelgp]

however no idea if it will work,

test it and i know you already did.

 

[CJ_London]

Goes to show you don’t know everything - I haven’t. Suggest you stop with the snarky comments you are hacking a lot of people off. And not just those you target.

 

[VBANewBie :)]

Take it easy my friends , Thanks for your reply

 

[isladogs]

I think the only way to do this using SysCmd 603 would be to do the following (tested and it works)
a) remove the password from the ACCDB file using code
b) convert to ACCDE (as above)
c) restore the password to the ACCDB file
d) add the password to the ACCDE file

NOTE: Although it now works, I wonder whether the extra effort is worthwhile for a password protected file
Why not just create the ACCDE in the standard way?

Anyway, if you want to do so, modify your code to use the SetDBPassword function:

Public Sub SetDBPassword(sDBName As String, sOldPwd As String, Optional sNewPwd As String = "")
    On Error GoTo Error_Handler
    Dim db  As DAO.Database

    'Password can be a maximum of 20 characters long
    If Len(sNewPwd) > 20 Then
        MsgBox "Your password is too long and must be 20 characters or less." & _
               "  Please try again with a new password", vbCritical + vbOKOnly
        GoTo Error_Handler_Exit
    End If

    Set db = OpenDatabase(sDBName, True, False, ";PWD=" & sOldPwd)    'open the database in exclusive mode
    db.NewPassword sOldPwd, sNewPwd    'change the password

Error_Handler_Exit:
    On Error Resume Next
    db.Close    'close the database
    Set db = Nothing
    Exit Sub

Error_Handler:
    'err 3704 - not able to open exclusively at this time, someone using the db
    'err 3031 - sOldPwd supplied was incorrect
    'err 3024 - couldn't locate the database file
    'err 3051 - database in use (current db)
    If Err = 3051 Then Resume Next
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SetDBPassword" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "Critical Error!"
    Resume Error_Handler_Exit
End Sub

Adapt your code as follows:

Function ConvertPwdACCDB2ACCDE()
Dim sourcedb As String, targetdb As String
Dim MyPath As String
MyPath = Application.CurrentProject.Path

sourcedb = "Full path to your ACCDB file"
targetdb = "Full path to your ACCDE to be created"

'remove PWD
SetDBPassword sourcedb, "YourPWD",""

Dim accessApplication As Access.Application
Set accessApplication = New Access.Application

'Create ACCDE
With accessApplication
      .SysCmd 603, sourcedb, targetdb
End With

'restore ACCDB password - if file isn't killed later
SetDBPassword sourcedb, "", "YourPWD"

'add ACCDE password
SetDBPassword targetdb, "", "YourPWD"

'Remove comment for any lines you wish to use
'Delete the sourcedb file
'Kill sourcedb

'Open new ACCDE - will need to enter PWD
'Application.FollowHyperlink targetdb

'Close current db
'DoCmd.Quit

End Function

 

[VBANewBie :)]

NOTE: Although it now works, I wonder whether the extra effort is worthwhile for a password protected file
Why not just create the ACCDE in the standard way?

Thanks for your reply , I used this idea in C# and worked nicely but I was wondering if I can do it by VBA directly , Your solution is perfect thanks , About your question I'm releasing a trial database online if I converted it the standard way it won't work on 64-bit or 32-bit office depending on what architecture I used for converting it , So my idea was to make use of the client's PC to convert my database then deleting the source db from his device .. What do you think , Is there another way ?

 

[isladogs]

Hmm. That does at least explain the Kill sourcedb line which I commented out.

However, I really wouldn't do that.
If the end user comments out the Kill sourcedb line they will have a copy of the code

Recommend you control the process yourself - make both bitness ACCDE files for distribution

BTW in your original code you wrote this:

Dim sourcedb, targetdb, nametargetdb As String

If you do that, the first two items will be variants. You need to define each item EXPLICITLY

I omitted the nametargetdb step as it was superfluous

 

[VBANewBie :)]

If the end user comments out the Kill sourcedb line they will have a copy of the code

He couldn't because the source file is password protected and opened by a .NET app which passes the password to open the source db and when converted to accde that's another layer of protection.

Recommend you control the process yourself - make both bitness ACCDE files for distribution

That's actually a good point I used to do but slipped out of my mind , I will consider this I think it is a safer way .

Thanks so much for this educating discussion .