Retrieve data from password protected Access back-end from Excel using vba

[MilaK]

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:

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

 

[isladogs]

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]

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]

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]

Why the back-end can't contain queries? Is it going to cause problems?
It seems to work well that way, Thanks

 

[Frothingslosh]

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]

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]

Have you considered exporting your data from Access to Excel?

 

[The_Doc_Man]

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]

The data is already in Access. All MilaK wants to do is get it into Excel.

 

[The_Doc_Man]

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.