Create an MS Access database file with password from Excel VBA

0
votes

I want to create an MS Access file .mdb by Excel VBA. I want to set a password for this Access file by Excel VBA.

This below code can create Access file but without password.

Sub Example1()
    'the path to create the new access database
    Dim strPath As String
    'an Access object
    Dim objAccess As Object

    strPath = "D:\VBA\NewDB2.mdb"
    Set objAccess = CreateObject("Access.Application")
    Call objAccess.NewCurrentDatabase(strPath)
    objAccess.Quit
End Sub

How do I set a password?

2
excelvbams-access

2 Answers

0
votes

Should be something like this

Option Explicit

Public Sub CreatePasswordProtectedDatabase()
    Dim strPath As String
    strPath = "D:\VBA\NewDB2.mdb"

    'create new Access application
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")

    'objAccess.Visible = True 'show or hide Access

    'create new database
    objAccess.NewCurrentDatabase strPath

    'example to add a table
    objAccess.DoCmd.RunSQL "CREATE TABLE Test", False
    objAccess.DoCmd.RunSQL "ALTER TABLE Test add Gender char(1)", False

    'set password
    Dim DbPassword As String
    DbPassword = "your_password"
    objAccess.CurrentProject.Connection.Execute "ALTER DATABASE PASSWORD " & DbPassword & " NULL"

    'close database
    objAccess.CloseCurrentDatabase

    'quit Access application
    objAccess.Quit
End Sub
0
votes

Please note, if you are using Office 2007 or newer, you must add the FileFormat parameter, in order to have your Access file be in mdb format:

'create new database
objAccess.NewCurrentDatabase strPath, 10

10, being the value of the acNewDatabaseFormatAccess2002 constant.