Retrieve data from password protected Access back-end from Excel using vba (1 Viewer)

MilaK

Registered User.
Local time
Today, 02:23
Joined
Feb 9, 2015
Messages
285
Hello,

I wrote vba code in Excel that retrieves data from Access db. It was working well until I split and password encrypted the database. The back-end is on the network. So now when I tried to import data, I get an error message "Run time Error 3031. Not a valid password"

I tried changing File/Options/Client Settings/Use Legacy Encryption and then splitting and password protecting the database but it still throws the same error message.

Is there a way to feed the password to the OpenDatabase statement and get this to work?


Here is my current code:

Code:
Dim db As Database
     Dim qdf As QueryDef
     Dim rs As Recordset
     Dim ws As Worksheet
     Dim strConnection As String
     Dim j As Long
     Dim i As Long
     Dim xls As Object   
    
     Set ws = ThisWorkbook.Worksheets("Data")
    
     Set db = OpenDatabase("L:\Database\database.accdb")
     Set qdf = db.QueryDefs("graph_variant_final_Crosstab")
     Set rs = qdf.OpenRecordset
Thanks
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:23
Joined
Jan 14, 2017
Messages
18,186
Use the following line with the correct password for your db:

Set db = OpenDatabase("L:\Database\database.accdb"), False, False, "MS Access;PWD=YourPasswordGoesHere")
 

MilaK

Registered User.
Local time
Today, 02:23
Joined
Feb 9, 2015
Messages
285
I was able to connect but my back-end has only tables and not the queries. Should I import the desired queries into the back-end?

I've tried to reference a copy of the front-end that has the queries but it produced an error message that the file was already opened exclusively by another user.

Thanks
 

isladogs

MVP / VIP
Local time
Today, 09:23
Joined
Jan 14, 2017
Messages
18,186
I was able to connect but my back-end has only tables and not the queries. Should I import the desired queries into the back-end?

I've tried to reference a copy of the front-end that has the queries but it produced an error message that the file was already opened exclusively by another user.

Thanks

No! The BE file should ONLY contain tables.

There is no reason why you can't connect to linked tables or queries in the FE database.

However if the table(s) are in use, you may need to do this process when no other users are logged in
 

MilaK

Registered User.
Local time
Today, 02:23
Joined
Feb 9, 2015
Messages
285
Why the back-end can't contain queries? Is it going to cause problems?
It seems to work well that way, Thanks
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:23
Joined
Oct 17, 2012
Messages
3,276
The point of a split database is to have the data in one file and everything else in another. That helps reduce corruption, collisions, you name it.

Also, Access doesn't allow you to link to queries, only tables. If you really want a query to be run by the back end, then create a passthrough query.

In addition to that, each user should get their own local copy of the front end - having multiple users run the same network copy actually defeats the purpose of splitting the database in the first place.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 28, 2001
Messages
27,001
MilaK, the problem is LOCKS. When you open something, it gets a usage lock. Multiple users hitting the same item all take out usage locks - but Windows has to negotiate with all other users hitting that object. For a table, you can write a query or form to minimize the locking, for example either setting NoLocks or OptimisticLocking. Can't do that for a query. So you DRASTICALLY increase the overhead involved. Further, Access understands sharing but Excel isn't quite so database-savvy and is more restrictive in the way it shares things.

As noted, you could open a query by opening a front-end and opening the query that way. But just remember, by opening a database from Excel, you are "going backwards." It usually is done the other way - opening a spreadsheet from Access.
 

isladogs

MVP / VIP
Local time
Today, 09:23
Joined
Jan 14, 2017
Messages
18,186
Have you considered exporting your data from Access to Excel?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 28, 2001
Messages
27,001
Actually, ridders makes a good suggestion. IF the amount of data to be managed is not that much, perhaps importing the data to Access first and THEN processing it with a series of well-filtered queries might be faster.
 

isladogs

MVP / VIP
Local time
Today, 09:23
Joined
Jan 14, 2017
Messages
18,186
The data is already in Access. All MilaK wants to do is get it into Excel.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:23
Joined
Feb 28, 2001
Messages
27,001
Oops... Export, then. Not import. Guess I misread it. Sometimes when I'm on line at night, I might not be the sharpest tack in the drawer.
 

Users who are viewing this thread

Top Bottom