Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-24-2017, 05:47 AM   #1
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 268
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Retrieve data from password protected Access back-end from Excel using vba

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 by MilaK; 07-24-2017 at 05:58 AM.
MilaK is offline   Reply With Quote
Old 07-24-2017, 06:02 AM   #2
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,175
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Retrieve data from password protected Access back-end from Excel using vba

Use the following line with the correct password for your db:

Set db = OpenDatabase("L:\Database\database.accdb"), False, False, "MS Access;PWD=YourPasswordGoesHere")
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 07-24-2017, 06:20 AM   #3
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 268
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Re: Retrieve data from password protected Access back-end from Excel using vba

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

MilaK is offline   Reply With Quote
Old 07-24-2017, 06:50 AM   #4
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,175
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Retrieve data from password protected Access back-end from Excel using vba

Quote:
Originally Posted by MilaK View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 07-24-2017, 07:21 AM   #5
MilaK
Newly Registered User
 
Join Date: Feb 2015
Location: United States
Posts: 268
Thanks: 33
Thanked 2 Times in 2 Posts
MilaK is on a distinguished road
Re: Retrieve data from password protected Access back-end from Excel using vba

Why the back-end can't contain queries? Is it going to cause problems?
It seems to work well that way, Thanks
MilaK is offline   Reply With Quote
Old 07-24-2017, 07:56 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,879
Thanks: 71
Thanked 402 Times in 363 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Retrieve data from password protected Access back-end from Excel using vba

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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
Old 07-24-2017, 08:12 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,174
Thanks: 57
Thanked 1,126 Times in 1,028 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Retrieve data from password protected Access back-end from Excel using vba

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-24-2017, 08:40 AM   #8
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,175
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Retrieve data from password protected Access back-end from Excel using vba

Have you considered exporting your data from Access to Excel?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 07-24-2017, 08:05 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,174
Thanks: 57
Thanked 1,126 Times in 1,028 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Retrieve data from password protected Access back-end from Excel using vba

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-24-2017, 08:15 PM   #10
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,175
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Retrieve data from password protected Access back-end from Excel using vba

The data is already in Access. All MilaK wants to do is get it into Excel.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 07-25-2017, 05:18 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,174
Thanks: 57
Thanked 1,126 Times in 1,028 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Retrieve data from password protected Access back-end from Excel using vba

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
excel

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Database connection in excel to get data from password protected Access database AccessToAcess General 2 08-06-2012 06:40 AM
Export access to multiple password protected excel files graveyard Modules & VBA 3 04-01-2011 04:28 PM
Importing data from a Password Protected Excel File rkrause Macros 0 03-31-2011 05:29 AM
Question Password protected back end niak32 General 3 08-15-2009 01:54 PM
Importing Password Protected Excel File in Access jfrink2 Macros 0 05-27-2008 05:55 AM




All times are GMT -8. The time now is 08:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World