Can't open db from Excel due to Access security

  • Thread starter Thread starter Ian Baker
  • Start date Start date
I

Ian Baker

I have a mde back end (Access 2000, 2002, & 2003) which has been secured
using my own mdw but in a way that allows the default admin user using the
default system.mdw to open the db and read most of the tables and its
records. But, when they try to access a tables records via Excel they
receive a "Record(s) can not be read; no read permission on <table name>".

It seems strange that although the security allows them to open and read the
records directly in Access they can't do it from Excel - so much for all the
Office interconectivity hype ;-)

Any suggestions - thanks in advance
 
Hi Ian,

From your descriptions, I understood that you would like to open a secured
mdb file by means of Excel. Have I understood you? If there is anything I
misunderstood, please feel free to let me know:)

Based on my scope, you will have to use codes opening it or ODBC
datasource. For example, the following codes may be helpful

Sub TestCxn()

Dim ws As Workspace
Dim db As Database

DBEngine.SystemDB = "Disk:\Path\secured.mdw"
Set ws = CreateWorkspace("", "userID", "password")
Set db = ws.OpenDatabase("Disk:\Path\your.mdb ", True)
Debug.Print db.TableDefs.Count

db.Close
ws.Close

End Sub


Additionaly, I think the following KB may be helpful

Using Microsoft Access as an Automation Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;147816

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Mingqing
I am sorry but like other responses you have given to my posts in other
groups you have not understood the problem. As an MSDN subscriber a response
to a post from a representative of MS is promised but unfortunately the
responses have never been helpful.
I said
"I have a mde back end"
You said
"open a secured mdb file"
I said
"which has been secured
using my own mdw but in a way that allows the default admin user using the
default system.mdw to open the db"
You said
"> Based on my scope, you will have to use codes opening it"
etc etc

I will try and make what I have said simpler:
1. I have a secured mde (secured by using my own .mdw)
2. A user can open the mde using their own default system.mdw
3. A user can then open a table a view its records but can't add or edit
them.
4. A user opens Excel
5. A user then either opens the mde file in Excel or "Get external data"
6. The user sees all the tables in the mde
7. When the user clicks to open a table they get an error "Record(s) can not
be read; no read permission on <table name>".

So my problem is what have I done wrong that allows a user to open a db and
view table records in a secured mde but in Excel they can open the db, see
the tables but can't see the records?
 
Ian said:
I said
"I have a mde back end"
Click to expand...

Just a comment. Usually a back end has only tables/relationships. What is
the point in making it a mde?
I will try and make what I have said simpler:
1. I have a secured mde (secured by using my own .mdw)
2. A user can open the mde using their own default system.mdw
3. A user can then open a table a view its records but can't add or
edit them.
Click to expand...

Are you certain that they are using system.mdw?
4. A user opens Excel
5. A user then either opens the mde file in Excel or "Get external
data"
Click to expand...

You cannot open a mde/mdb in Excel, but you can use Get external data.
6. The user sees all the tables in the mde
7. When the user clicks to open a table they get an error "Record(s)
can not be read; no read permission on <table name>".
Click to expand...

They may not be using the workgroup you think, or the Admin user/Users Group
doesn't have permission to read the table.
So my problem is what have I done wrong that allows a user to open a
db and view table records in a secured mde but in Excel they can open
the db, see the tables but can't see the records?
Click to expand...

I went through the process to test, and was able to do this successfully.
Double check the workgroup in use.
 
Hi Ian,

I wanted to post a quick note to see if you would like additional
assistance or information regarding the information MVP Joan has sent to
you on this particular issue. We appreciate your patience and look forward
to hearing from you!

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Joan
As always thankyou for your response.
Just a comment. Usually a back end has only tables/relationships. What is
the point in making it a mde?
Click to expand...
- Just wanted to stop any type of form or code development within the db
itself
Are you certain that they are using system.mdw?
Click to expand...
- Yes, they are and in fact I tried it myself using my own system.mdw
(although the db was secured using developer.mdw - which you told me how to
secure a db but allow users to access certain areas using their own
system.mdw in another posting) and I can't open it via Excel either so whist
it has been secured in a way that enables the db to be opened and the table
records viewed (not edited, added or deleted) I must have done something
wrong when securing it but on checking the security settings all looks ok.
You cannot open a mde/mdb in Excel, but you can use Get external data.
Click to expand...
- Yes you can Joan by File-Open-File Type is Access Databases-Enter/Select
file name etc
They may not be using the workgroup you think, or the Admin user/Users Group
doesn't have permission to read the table.
Click to expand...
- In the Developer.mdw the Users group has Read Design & Read Data
privileges on all the tables I want them to be able to access. The Admin
user has no privileges other than being a member of the Users group. The
same is seen when using the system.mdw
I went through the process to test, and was able to do this successfully.
Double check the workgroup in use.
Click to expand...
- I have Joan and as I said all looked ok but the thing is as you even once
said what you see is not always what you get in Access Security (I think it
was A2000 where there was a problem with incorrect settings being shown)

So. I am no further advanced unless there something that I have said in
here.

Thanks
 
Ian said:
- I have Joan and as I said all looked ok but the thing is as you
even once said what you see is not always what you get in Access
Security (I think it was A2000 where there was a problem with
incorrect settings being shown)

So. I am no further advanced unless there something that I have said
in here.
Click to expand...

I'm sorry, I don't know what else to suggest. I don't see anything in what
you've posted. As I said, I was able to get it to work.

Sorry.
 
Back
Top