Vba - pull data from Access DB

[Siyanna]

Hi All -

I have some code that usees ADO to pull back data from Access to Excel spreadsheet

I have 2 questions and hoping you can help me with this

1) How can I password protect this access database
2) how can i pull back data from this password protected database

Many thanks

 

[Siyanna]

Hi Xenou - hope you are all good

What I mean is that the access database will only be open manually by me

When a user opens up a workbook in excel - I update this access database with the login details. So they don't physically open the spreadsheet but update the this access database in the background when they open the spreadsheet

Multiple users can open up this spreadsheet so that means this database can potentially be updated simultaneously with log in details and this is what I mean

With the password - the reason I wanted to protect is because just encase someone had access to where this access database is stored - I don't want them to be able to go into it manually - all this is done through the workbook open code hence why I wanted to password protect it and change the connection to allow me to open up this password protected database

I hope this makes sense

 

[Siyanna]

Hi Xenou - again whats the difference with these 2 accdb / mdb databases are they both not access?

 

[Norie]

When the database is closed and all connections to it closed is there a file in the folder where the database file is stored with the same name as the database but an extension of ldb or laccdb?

 

[Siyanna]

What does this mean? I'm not sure how a password will help - if other users have it open you can't update it (design-wise, naturally updating data in the data tables is possible with or without a password).

this is the provider I can find when it comes to password protected - cant fine the ACE Provider

[h=3]With database password[/h]This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.
<code>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword;</code>

 

[Siyanna]

When the database is closed and all connections to it closed is there a file in the folder where the database file is stored with the same name as the database but an extension of ldb or laccdb?

when I step through my VBA code - when it opens the connection string - it creates another file (type of file: Microsoft Access Record-Locking Information (.laccdb)) and once the this code is finished - it closes that file

here is my full code to update this log database which is not protected - my aim is to protect it so no one else can phycally manually open it without knowing the password - thanks

Sub ExportDataToAccess()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strQuery As String
    Dim LogDate As Date
    Dim LogTime As Date
    Dim myDB As String
    
    Application.ScreenUpdating = False
    'Initialize Variables
    LogDate = Date
    LogTime = Format(Now(), "hh:mm:ss")
    UserId = Environ("username")
    
    'myDB = "replace with the fully qualified path to your Access Db"
    myDB = "C:\Users\my.DESKTOP-8BKSK49\Desktop\Access Log Files\LogDatabase.accdb"
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
        .ConnectionString = myDB
        .Open
    End With
    
    On Error GoTo CloseConnection
    
    With rs
        .ActiveConnection = cn
        .Source = "LogTable"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        .Open
            
        On Error GoTo CloseRecordset
        .AddNew
        .Fields("Log_Date").Value = LogDate
        .Fields("Log_Time").Value = LogTime
        .Fields("UserID").Value = UserId
        .Update
    End With
    
CloseRecordset:
    rs.Close
    
CloseConnection:
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[Norie]

Have you tried opening the database exclusively?

Encrypt a database using a password..

 

[Siyanna]

Ive tried this step and still no luck

if i do a save as and go to the tools drop down at the bottom - i only get option for Map Drive

 

[Norie]

Why are you mentioning Save As?

1 Open Access.

2 Select Open Other Files.

3 Select Browse.

4 Browse to and select the database.

5 Click the arrow next to Open in the file dialog and select Open Exclusive - the database should now open in Exclusive 'mode'.

6 Select File, select Encrypt with Password, enter and verify your password.

 

[Siyanna]

Thank you - I finally managed to get around that - what exactly is opening it as exclusive?

I get a message saying Encrypting with a block cipher is incompatible with row level locking - what does that mean?
and lastly whats the connection string i need to have in my code to allow me to update the database as Xenou mentioned that my Jet provider code wont work and need an ACE provider?

Again thank you

 

[Siyanna]

This is the message on the connection string website with the Jet provider

[COLOR=rgba(0, 0, 0, 0.701961)]Note![/COLOR][COLOR=rgba(0, 0, 0, 0.8)] Reports say that a database encrypted using Access 2010 - 2013 default encryption scheme does not work with this connection string. In Access; try options and choose 2007 encryption method instead. That should make it work. We do not know of any other solution. Please get in touch if other solutions is available![/COLOR]

I understand that mdb is the older versions of Access so what connection string will work

again thank you