ACCDE disable table design view

[bilalo]

I have an accde file, I want to allow my users to have full control on the tables data without changing the table design. So I allowed the shift key bypass.
But the problem is when using the shift key, the table design is enabled. What am I missing here?
Should I disable the shift key and use another approach?
Should I split my database, does this solve the issue?
Is there a better method than what I am doing?

[CJ_London]

What am I missing here?

your db is not set up correctly. tables should be in a separate back end file, everything else in the front end file (i.e split). Create Linked tables to link the back end to the front end. The back end is stored on the server where everyone can access it, each user has their own copy of the front end. Your current setup will lead to corruption at some point. Note users will still be able to view table design through the linked table, but they won't be able to change it.

To prevent users being able to access the back end directly, password protect it. If you do this after linking, you will need to recreate the linked tables in the front end.

Note that experienced users will still be able to discover the password if they know where to look, There are means to hide the password so if you need a higher level of protection you would need to do this - but then users will not be able to access the data in the tables. Ideally users should not be working directly with the tables anyway.

[bilalo]

Thank you very much for this informative answer, you helped me a lot. I split the database and made it password protected and I disabled the shift key so that users do not work directly with tables. Just one more question regarding the database splitting, tables are linked to the database with a local path. Now when I copy my database to another computer, it will have a different path. Is there a way to use a relative path for links that works on all PCs?
And if there is no way for relative paths, then I need to refresh the links manually, but since the the shift key is disabled, I have no option to refresh the links.
What do you suggest?

your db is not set up correctly. tables should be in a separate back end file, everything else in the front end file (i.e split). Create Linked tables to link the back end to the front end. The back end is stored on the server where everyone can access it, each user has their own copy of the front end. Your current setup will lead to corruption at some point. Note users will still be able to view table design through the linked table, but they won't be able to change it.

To prevent users being able to access the back end directly, password protect it. If you do this after linking, you will need to recreate the linked tables in the front end.

Note that experienced users will still be able to discover the password if they know where to look, There are means to hide the password so if you need a higher level of protection you would need to do this - but then users will not be able to access the data in the tables. Ideally users should not be working directly with the tables anyway.

[CJ_London]

tables are linked to the database with a local path

to clarify - are you saying each user is working independently of the other users? i.e. they each have their own backend and do not share their data?

[bilalo]

I do not have a problem with users, only one user will use the back end, and to be more specific the front end and the back end both exist in the same folder, the problem is that the back end link on my PC (C:\Users\Bilal\Desktop\be.accdb) will not work on customer's PC because they have a different Desktop path than mine, so I need to re-link the front end to the back end every time I copy my files to a new PC.
My question is, since both files exist in the same folder, how can I link to be.accdb directly instead of C:\Users\Bilal\Desktop\be.accdb

to clarify - are you saying each user is working independently of the other users? i.e. they each have their own backend and do not share their data?

[Micron]

since the the shift key is disabled, I have no option to refresh the links.

Sounds like you will have to incorporate a folder dialog to allow the user to navigate to their db folder for the first time. Then you use that path to relink the tables in code.
OR set things up so that Users isn't involved; e.g. C:\Databases\MyNiftyDatabase - but obviously you'd have to convince the end user to go along with that location, which they won't like if it's a shared pc.
OR you can get their user name on first use and concatenate it into the be path, but this will only work if they place it where you told them to; i.e. what follows their login name must be what you allowed for. Then code to relink the tables.

I think the folder dialog is the way to go, unless you got real fancy and are using an actual installer. In that case, the path for the db is known by the installer.

[bilalo]

It is so disappointing that MS Access doesn't have an option to link front end and back end (in case they are in the same folder) using a direct path without going through a full path.
However, your solution to use a path like C:\Databases\MyNiftyDatabase is a nice idea.

Sounds like you will have to incorporate a folder dialog to allow the user to navigate to their db folder for the first time. Then you use that path to relink the tables in code.
OR set things up so that Users isn't involved; e.g. C:\Databases\MyNiftyDatabase - but obviously you'd have to convince the end user to go along with that location, which they won't like if it's a shared pc.
OR you can get their user name on first use and concatenate it into the be path, but this will only work if they place it where you told them to; i.e. what follows their login name must be what you allowed for. Then code to relink the tables.

I think the folder dialog is the way to go, unless you got real fancy and are using an actual installer. In that case, the path for the db is known by the installer.

[Micron]

using a direct path without going through a full path.

I don't understand what that means - unless you are talking about the table linking dialog. Maybe you mean "relative path" rather than direct. If you are referring to paths in code, then I'd say yes there is. One can use the CurrentProject.Path property, which will return the folder path that the fe is located in (that makes it relative, not direct) You can also use UNC paths but that is meant for where different users on the same network have different native drive letters. That is to say, the db is located in F:\Purchasing\Databases\NiftyDb for me, but it is M:\ for you. In that case, you use \\theServerNameGoesHere\thenTheFolderHierarchy.

My vote is on the folder dialog - then you don't have to strong arm them into placing it somewhere they don't want it to be. Remember, if not in the user's subfolder tree, it would be exposed to anyone who uses the pc.

[isladogs]

If both FE and BE are in the same folder you can just reference the BE path using

CurrentProject.Path & "\BE.accdb"

substituting the actual name of your BE file.

If its in a separate folder, then you can just save the folder path as a variable e.g. strPath then use

strPath & "\BE.accdb"

[bilalo]

Yes I mean relative path, sorry for the confusion.
I know about the CurrentProject.Path property but I don't know how to use it in table linking because I use a dialog for linking.
Can table linking be done on Form_Load using vba?

I don't understand what that means - unless you are talking about the table linking dialog. Maybe you mean "relative path" rather than direct. If you are referring to paths in code, then I'd say yes there is. One can use the CurrentProject.Path property, which will return the folder path that the fe is located in (that makes it relative, not direct) You can also use UNC paths but that is meant for where different users on the same network have different native drive letters. That is to say, the db is located in F:\Purchasing\Databases\NiftyDb for me, but it is M:\ for you. In that case, you use \\theServerNameGoesHere\thenTheFolderHierarchy.

My vote is on the folder dialog - then you don't have to strong arm them into placing it somewhere they don't want it to be. Remember, if not in the user's subfolder tree, it would be exposed to anyone who uses the pc.

[bilalo]

I know about the CurrentProject.Path property but I think in this case table linking should be done using vba on Form_Load, right?

If both FE and BE are in the same folder you can just reference the BE path using

CurrentProject.Path & "\BE.accdb"

substituting the actual name of your BE file.

If its in a separate folder, then you can just save the folder path as a variable e.g. strPath then use

strPath & "\BE.accdb"

[isladogs]

First of all apologies to Micron. I hadn't read his previous posts and inadvertently repeated what he was saying.

Secondly I'm unclear what you're saying in the last post.
Once you have linked your tables you can use them in the form/report record source just like any local table (or query or sql statement)
If you want to hide that info from the user you can set the record source using code in the Form_Load event using something like

Me.RecordSource = "SELECT * FROM YourTableName IN ' ' [MS Access;PWD=" & YourBEPassword & ";DATABASE=" & CurrentProject.Path & "\BE.accdb];"

A more complex version of this is shown in this example application http://www.mendipdatasystems.co.uk/e...-db/4594566347

But to repeat an earlier comment, you really should not allow your user(s) direct access to the tables at all.

[Micron]

I know about the CurrentProject.Path property but I think in this case table linking should be done using vba on Form_Load, right?

I would agree with that, or at least a message box saying they are invalid and relink yes or no. There are plenty of examples on how to relink tables via vba. For your situation, maybe do a DLookup test on a be record if you have any records such as db settings, or attempt to retrieve a db property value such as bypass. If it fails, assume the reason is because table link paths are invalid. Prompt to relink tables and just use the CurrentProject.Path property as the linking path. I suppose this would work in any case, such as if they alter the folder hierarchy or move the db. The closest I came to this is when I wanted to ensure users could not copy the db and use it for production related data entry. I had a be table of expected paths and compared the path of one on startup. If it didn't match the table value, their nefarious deed was exposed and they weren't allowed to start. That was a case where I had to use UNC paths.
Hope that helps.