Access table protection from connections and DBreplications

[Sarel]

Hi all I have successfully protected my DB "ACCDE" tables in Access, If open a new Access DB and connect to my DB no tables or Queries are visible, the forms, reports and modules are visible but not accessible, I have a stand alone DB, there are no server connections or excel sheet connections, please see attached the Access connection at. I cant say the same for the excel connection also connecting to the same DB where all the tables are visible and thus not protected, My My what to do to protect my tables from excel.

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

First, hello and welcome to the forums.

Second, understand that neither Access nor Excel are designed for tight security. They BOTH started as "small business" solutions in a simpler time when hackers and security were less common than they are today. We have some members whose expertise is in securing Access as much as possible. Not my specialty, so I will defer to them when they chime in. But the simplest solution already seems at odds with your setup. May I ask WHY you want Excel involved at all? Because Access can do all the computations for you that Excel could. A spreadsheet is different in concept than a relational database, so the approaches are different. What is it about Excel that you even want to bring it to the party?

Third, since you have asked a technical question in an "Introductions" forum, it is possible that we might have to move the question (as a matter of site policy). This happens all the time and the moderators can move threads around as needed, so don't worry about it.

 

[tvanstiphout]

Hiding tables is not really possible in Access. You'll need to look at a server-class database engine like SQL Server if that is your requirement.
Also, your "tblQ*" tables seem in conflict with a proper relational database design, so maybe Excel is a better environment for you.
Make sure you understand your requirements and your tools strengths and weaknesses before going much further.

 

[Sarel]

First, hello and welcome to the forums.

Second, understand that neither Access nor Excel are designed for tight security. They BOTH started as "small business" solutions in a simpler time when hackers and security were less common than they are today. We have some members whose expertise is in securing Access as much as possible. Not my specialty, so I will defer to them when they chime in. But the simplest solution already seems at odds with your setup. May I ask WHY you want Excel involved at all? Because Access can do all the computations for you that Excel could. A spreadsheet is different in concept than a relational database, so the approaches are different. What is it about Excel that you even want to bring it to the party?

Third, since you have asked a technical question in an "Introductions" forum, it is possible that we might have to move the question (as a matter of site policy). This happens all the time and the moderators can move threads around as needed, so don't worry about it.

Hi Doc Man

I do not like excel I only import data from excel spreadsheets into access where i do all my magic, My DB is locked up so that if any one opens a new DB and connects to my DB they have no access to my tables, and queries. How ever if you connect to my DB from within excel via the get data tab all my tables and queries are available for any one that i do not want, I have 2 major tables 81K and 75K lines of product cross reference material for electrical products that took years to gather.

 

[Sarel]

Hiding tables is not really possible in Access. You'll need to look at a server-class database engine like SQL Server if that is your requirement.
Also, your "tblQ*" tables seem in conflict with a proper relational database design, so maybe Excel is a better environment for you.
Make sure you understand your requirements and your tools strengths and weaknesses before going much further.

Hi Tom

I did hide and protect my tables and queries, did you see my screen shots? the DB I created is a stand alone DB and is not a convectional DB. It is a technical support tool "App" with large tables and hundreds of links to PDF's and documents, there are no linked tables, spread sheets or relationships of any kind. When you open a new DB and connect to my DB you can't import any of my tables, Queries, forms, reports or modules, but if you do the same from excel you can see and extract all my tables, this I do not want. I have attached some screen shots.

 

[tvanstiphout]

Hi Tom

I did hide and protect my tables and queries, did you see my screen shots? the DB I created is a stand alone DB and is not a convectional DB. It is a technical support tool "App" with large tables and hundreds of links to PDF's and documents, there are no linked tables, spread sheets or relationships of any kind. When you open a new DB and connect to my DB you can't import any of my tables, Queries, forms, reports or modules, but if you do the same from excel you can see and extract all my tables, this I do not want. I have attached some screen shots.

I was not clear enough. What you call "hiding", Excel calls "bah humbug" and ignores the Hidden attribute. That is not really hiding then, is it? That is what I meant with my comment "Hiding tables is not really possible in Access".

 

[tvanstiphout]

product cross reference material for electrical products that took years to gather

I sympathize with this dilemma, but trying to hide the tables in Access is not the way to protect your intellectual property.
Maybe you can open a new thread: "How to protect my intellectual property residing in Access tables" and we can suggest some options.

 

[Sarel]

I was not clear enough. What you call "hiding", Excel calls "bah humbug" and ignores the Hidden attribute. That is not really hiding then, is it? That is what I meant with my comment "Hiding tables is not really possible in Access".

Hi Tom
Thanks for the communication and insight
Fair enough from an excel point of view I have a problem, extraction from my DB with Access is no longer a problem for me, in any case I will revert back to my old habit by encrypting all the text in my tables and decrypt every time it is used in Access, or simply find a software package to replace Access maybe Libreoffice, will see if Libre has a table protection setup.

 

[isladogs]

There are two ways of hiding objects in Access:
a) any object can be hidden in the nav pane but that is easily reversed. They can also be seen / imported from another database if that has hidden objects ticked
b) tables can also be 'deep hidden' so they are never visible from the nav pane. They are also invisible from other Access databases. Doing that isn't possible for queries.
As Tom has stated, even deep hidden tables can still be viewed from Excel. Unfortunately, that cannot be prevented unless the database is itself encrypted with a password not known to the end user.

I doubt you will find that Libre Office (or similar) is any more secure than Access. Probably the converse will be true.
However, you mentioned encrypting the data so may be interested in my example app:

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

 

[The_Doc_Man]

As a side note, I have moved this thread to "GENERAL" because it has gone far beyond the intent of the "INTRODUCTIONS" forum. Don't worry, Sarel, because I left a link that will remain for 7 days so you could find this thread in its new place. And there is no need for us to warn you about the thread getting complex. Trust me, we've seen it a gazillion times. It's kind of like golfers say, "par for the course."

 

[theDBguy]

extraction from my DB with Access is no longer a problem for me,

Maybe you're not yet aware of how to do it, but someone else might still be able to get to your table from another Access DB.

or simply find a software package to replace Access maybe Libreoffice,

Maybe you can also consider using SQL Server?

 

[Pat Hartman]

or simply find a software package to replace Access maybe Libreoffice, will see if Libre has a table protection setup.

Nothing, especially not something free, has anywhere close to the functionality contained in Access. As you have been informed, Access is not secure. If you want to hide the tables, you will have better luck if you move them to SQL Server.

Another option is to "bury" the database. I can't give you specific directions, but split the db if you haven't already. The tables need to be in their own BE database and separate from your FE with the forms and reports. Explain the problem to your network admin. He can create a hidden directory on the server which the casual user will not be able to see or accidentally discover. Once the BE is there, you could still get to it from Excel but ONLY if you know the actual path. When Excel connects to your database, I'm pretty sure you only see local tables. You don't see linked tables. I don't have time to verify this right not but it is easy enough to check. If you can use Excel to open your FE and see linked tables, then this solution wouldn't work.

 

[isladogs]

When Excel connects to your database, I'm pretty sure you only see local tables. You don't see linked tables. I don't have time to verify this right not but it is easy enough to check. If you can use Excel to open your FE and see linked tables, then this solution wouldn't work.

No, thats not correct. Excel does see linked Access tables but not those from ODBC databases such as SQL Server

However, the standard approach to importing Access tables in Excel doesn't allow you to enter a password.
There is a way around this but even then the user would need to know the password.

So if your database is split and all tables are in a password protected BE for which the user doesn't know the password, then it starts to become more secure. Of course, if the tables are linked then the BE password can be read from the FE.....

However ,if you have an encrypted FE which uses disconnected ADO recordsets to populate the record sources of forms in code then there is no need to have linked tables. This means there is no way of users being able to find out the BE password....or import the tables into Excel.

If you go one step further and also encrypt the data, then its more secure still
That is basically how my example app linked in post #10 works . . . with a few extra layers of security thrown in.