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

 

[amcquaid]

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

Hi Siyanna,

You can password protect the database using the "Encrypt with Password" option on the file tab (Info page) within access. In order for it to work properly with ADO in Excel, you must first change the encryption method (File, Client Settings, Advanced, select "Use legacy encryption")

Next, you need to update your ADO connection string to include the database password, see https://www.connectionstrings.com/ace-oledb-12-0/with-database-password/. Remember to update the relevant text values to match your own project.

Hope this helps
Alan

 

[ranman256]

1. in access, File, Info, set password

2. in ADO code, set the password in the connect string:
cn.Provider = "microsoft.jet.oledb.4.0;Jet OLEDB:Database Password=yourpassword,Data Source=\\server\folder\my.mdb"

 

[Siyanna]

Hi All,

I tried saving it with password but I get this error message - You must have the database open for exclusive to set or remove the database password

to open the database exclusively - close and then reopen by going to file open command

This is the steps I'm following: File - Info - Encrpyt with password (I'm using access 2016)

My current access database connection is this

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

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.jet.oledb.4.0;Jet OLEDB:Database Password=MyPassword,Data Source=myDB" 'For *.ACCDB Databases
.ConnectionString = myDB
.Open
End With

But I cant get pass the 1st bit let alone getting the code bit tested

ive tried changing to this

 

[Norie]

Does anyone have the database open or are there any open connections to the database?

The latter could be a consequence of your existing code, especially if connections to the database aren't/haven't been closed properly.

 

[Siyanna]

Hi

No one else has this database open - the whole purpose is to allow me to update this database even if multiple users have it open

Not sure what else to do

 

[Norie]

So you can't manually set the password of the database?

What happens if you Compact & Repair it and try again?

 

[Siyanna]

Let me try that

 

[Siyanna]

Still get the same error message

 

[xenou]

Hi

No one else has this database open - the whole purpose is to allow me to update this database even if multiple users have it open

Not sure what else to do

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).