Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2018, 10:31 PM   #46
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

Dear ridders,

which connection shall I use between FE and BE? I mean which one has the high speed,

by default I used linked table and I don't know is good way or not

ahmedaliazad is offline   Reply With Quote
Old 08-12-2018, 11:06 PM   #47
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,994
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: How to protect my tables and queries in access from importing

All methods use linked tables and all are AFAIK roughly the same in terms of speed.

If you have more questions about linking tables in split databases, please start a new thread.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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
Previously known as ridders : 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.
isladogs is offline   Reply With Quote
Old 08-13-2018, 01:07 AM   #48
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

Dear ridders I did the linked query,

But I have three problems which are:

1. the textbox that I putted to count the record on form is #Error, before using linked query I wrote in control source of textbox
"=DCount("*","QueryName")"
, but now what should I write because I deleted the query in FE.

2. I have some combobox which I using them to find record on my form, the row source of it is like
"SELECT [CompanyQ2].[Cname] FROM CompanyQ2; "
I moved the selected query into BE, so what should I write in the row source of combobox to see the query in BE,

3. When I changed the record source of forms to linked query in BE the size of form was changed and it will show one record, each time I should scroll bar one by one,

thanks for help

ahmedaliazad is offline   Reply With Quote
Old 08-13-2018, 03:45 AM   #49
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,994
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: How to protect my tables and queries in access from importing

For one solution to questions 1 & 2, see updated example attached.

This time, I've included a continuous form as well as a single form
If you want to use a datasheet instead, this would need to be a subform with the combo and totals textbox on the main form

Basically use the same idea for the SQL in the combo row source (or if using a listbox) as for the form and report.

I've used a recordset count for the total records

For item 3, change the size of the form in design view then save it.
It should open at the same size each time.

As previously requested, any more questions. please start a new thread.
Doing so will also make it more likely that others will contribute

UPDATE: 23/08/2018
Attachment removed - I found a security flaw!
Updated version added to post #55
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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
Previously known as ridders : 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.

Last edited by isladogs; 08-23-2018 at 09:00 AM.
isladogs is offline   Reply With Quote
Old 08-13-2018, 04:33 AM   #50
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

Thanks,
but what is the difference if I write the path of linked query in record source at open form event and default record source field under Data tab?
ahmedaliazad is offline   Reply With Quote
Old 08-13-2018, 04:44 AM   #51
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,994
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: How to protect my tables and queries in access from importing

Please start a new thread.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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
Previously known as ridders : 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.
isladogs is offline   Reply With Quote
Old 08-13-2018, 05:12 AM   #52
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,473
Thanks: 62
Thanked 1,175 Times in 1,075 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: How to protect my tables and queries in access from importing

ahmedaliazad -

My response is focused on your question about which linking method is faster.

In essence it is not how fast a method you use but WHEN you use it that is more obvious in terms of speed. I will try to explain.

Access uses one of two protocols to link to a back-end. If the back-end is in Access, it uses Server Message Block (SMB) protocol, which is a member of the TCP group of protocols under TCP/IP, which is THE family of protocols that everyone uses.

If the back-end is an active SQL engine such as MySQL, SQL Server, ORACLE, and a few more of that type, they use a connection protocol sometimes called SQLnet, which is ALSO a member of the TCP group under TCP/IP. There is no mix-and-match with those. The nature of the back-end file uniquely determines which protocol must be used.

Both protocols have similar characteristics but use different "connection ports" as the "listener" side of the protocol and both use "negotiated ports" for individual network connections. They are nearly identical in that aspect of the network "handshake" that they use. So there is NO PRACTICAL DIFFERENCE between the two.

Factors that DO affect the speed? Whether the connection will be encrypted or not, and whether the chosen back-end requires a password or not.

SMB is not encrypted but CAN ride a VPN. However, if the BE is Access and is encrypted using a password then an encryptor/decryptor component is part of every transaction every time. SQLNet has an encrypted variant (different port number). However, those factors will be constants no matter how or when you make the connection.

The main difference in your perception of the speed is WHEN you establish the connection and whether you have a persistent or non-persistent "session" connection between FE and BE.

The type of connection that is apparently fastest (i.e. apparent to you, the user) is a statically bound table, where WHATEVER you are going to do, you do it when the FE database comes up and verifies connectivity to the BE file/server. So the delays associated with the connections are all bundled into the time it takes for the FE/BE pair to be ready for use. If your database is slow to be ready, it is because of these initial steps caused by the static connection.

The type of connection that is apparently slowest is a dynamic non-persistent connection where WHATEVER you are going to do, you do it EVERY TIME you open a table in the BE file/server. Then your speed is a series of speed bumps while you perform a series of handshakes for each thing that uses a recordset in the BE. It is the repetition of reconnecting each time that causes the slowness.

There are techniques that one can use that would permit you to establish a persistent connection from FE to BE, because Access will use that connection to "ride" other traffic besides the event that opened the persistent connection. That makes subsequent connections appear faster because the connection is already open and any protocol handshaking is minimized. I don't know if ArnelGP's "IN" clause can take advantage of a persistent connection because I have not had occasion to try that particular syntax.

So when you ask which factors are faster or slower:

- type of BE file (Access or SQL server) is NOT significant - though if the BE is an active SQL server and the queries are of type "pass-through" you will see a speed difference based on the AMOUNT of traffic between FE and BE. But that is again an essentially constant factor and not related to the initial connection speed.
- persistent or intermittant connection IS significant
- WHEN you make the connection affects apparent start-up time OR apparent operation time. (The old "pay me now or pay me later" syndrome.)

You are in good hands with Ridders and ArnelGP for the security side of the discussion so I don't want to take way from what they are telling you. However, I felt I could contribute to the "speed" issue by explaining the factors. That is my only intent of this post.

__________________
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.

Last edited by The_Doc_Man; 08-13-2018 at 05:18 AM.
The_Doc_Man is offline   Reply With Quote
Old 08-13-2018, 10:55 PM   #53
ahmedaliazad
Newly Registered User
 
Join Date: Aug 2018
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
ahmedaliazad is on a distinguished road
Re: How to protect my tables and queries in access from importing

Thanks for your reply,

I used microsoft access as BE?

I will put the BE on sharing folder that all clients can access over the network so I need your suggestion about connection between them,

The FE include Forms and Reports,
The BE include Tables and Queries and encrypted with password
ahmedaliazad is offline   Reply With Quote
Old 08-14-2018, 05:58 AM   #54
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,473
Thanks: 62
Thanked 1,175 Times in 1,075 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: How to protect my tables and queries in access from importing

Your intent as described seems appropriate for an Access BE file. The combination of shared BE and distributed FE is the preferred way to go. However, there is more to it than just the FE/BE configuration.

The "connection" I mentioned depends on something I don't recall you mentioning in this thread. I recommend some kind of switchboard or dispatcher form that everyone sees. You use it to block people from seeing the infrastructure of your FE file, which would be the first method of infiltration if they wanted to hack your system.

Ridders and others commented in passing about hiding the ribbon, hiding the navigation pane, disabling the various short-cuts and shift-key bypass, things like that. With a properly locked-down switchboard, anything that anyone wants to do must be supported as an option in the "dispatcher" form, which will always be open and will hide the structure of its parent DB. I leave it to you to search this forum for the myriad articles that deal with the "Switchboard" concept. I will only discuss it here in passing because it is a wheel I don't need to re-invent.

In that switchboard form you can establish a "persistent" connection to the BE by using code to open a recordset to some "trash" table (or it could be significant in some way). The code would run when the switchboard form opens (i.e. Form_Open event). In my case, I made the back end's version number the only record in a little table, and I used it to compare the FE version to the BE version. I tested it but then didn't close it. You do not close the "keep alive" recordset until your user initiates the "Application exit" sequence according to your rules on shutting things down.

You can effectively disable the little X control in the upper right of the window by trapping the Form's attempt to shut down. The Form_Unload event has a Cancel parameter, which if you set it to TRUE will prevent the form from closing even if they click the X. (This isn't perfect since Task Manager and some persistence could still shut it down.) When I had to do this, I had an internal Boolean in a public module that was initialized as FALSE when the switchboard opened. If the user clicked my "Exit" command button, the button-click code set the the variable to TRUE. When the Form_Unload event fired, the code saw the flag had been set so allowed the Exit to happen. But if someone tried any other method to close things down, the flag would still be FALSE and the Unload would be canceled. That enabled me to perform an orderly shutdown which included closing that persistent connection.

The point here is that Access optimizes its network connections. If you open the network link to the BE and then close it again, then (using Arnel's "IN external-database" syntax) you have to log in and set up encryption for every action that opens a recordset in that BE file. But if Access sees that you have a connection already open and set up for encryption/decryption, it will "ride" the connection and not go through the overhead of opening the port, establishing the encryption, performing one transaction, and closing the port again. It will re-use the open connection. Less overhead, and that is good for you to know since you expressed concern about performance.

As to the security aspect of this: The information regarding that connection is only visible in one of the hidden Access system tables, and only for as long as that connection is open. Windows versions after WinNT conform to the U.S. Dept. of Defense standards regarding isolation and object re-use, so no other process can see that information unless they have some extremely elevated Windows privileges. In which case all security bets are off anyway.

If your switchboard or dispatcher form is robust, then nobody can look INTO your process and your user can't break out of the switchboard to "peek" while running the process, which means that the dynamic connection is about as secure as is possible.

I don't know if this has been enabled for MS Access, but here is how you add security to the dynamic network link between client and server processes:

https://docs.microsoft.com/en-us/win...r/smb-security

Since this is something you do at the server end of the connection (essentially, declare a Share folder to require encrypted connections), it would simply be part of the network overhead associated with establishing the connection. By using a persistent connection as described earlier, you would reduce the overhead for each time you open a recordset. There IS a performance warning in the article because ANY time you involve encryption, you encounter delays. You pay a price for increasing security and the currency of that price is performance.

Let me be clear:

If you use Arnel's method which puts a password on the database to effectively encrypt it, you are addressing security at rest. This is meant to stop people from reading the file on the server by hacking into it. If they steal a copy of the file but don't have the password, they have to hack it, which could take a long time.

If you attempt to use SMB encryption on the server-side Share folder that holds the BE file, you are addressing security in motion. This is meant to stop people from running a "sniffer" or other network eavesdropping method to grab your data as it passes across the network. This MIGHT not be necessary if the BE is encrypted because I believe that decryption occurs AFTER the data reaches the FE file. In which case the network SMB encryption would be overkill, i.e. encrypting an already-encrypted transfer.

Note also that the method requires that the BE file would reside on a system running something like Windows Server 2012. I think all of us would be interested in knowing if you could get Access to work this way because I know the security theory but I have not seen anyone try this method of protection. Part of that might be that the network-level of encryption isn't necessary for encrypted BE cases. Please understand that I do not know what is transmitted across the connection in this case.

I mention this only because you are so emphatic about trying to protect your data. It might be more trouble than it is worth even for your extreme needs since it might indeed be a case of overkill. But I would be remiss if I didn't explain it to you as part of your concern over system security.
__________________
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 08-23-2018, 09:06 AM   #55
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,994
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: How to protect my tables and queries in access from importing

I offered a solution to the OP's question back in posts #43 & a modified version in post #49.

I asked anyone who found a flaw in these to let me know.
Nobody did so ... but whilst working on a similar idea for another purpose, I found a loophole which meant the earlier versions could be hacked after all.

I'm deliberately not going to explain here what the loophole was.
However, I've hopefully fixed the issue(s) in the updated version (v3) attached.
I've also removed the earlier versions

In the example supplied, you have full access to code in the FE and have been given the BE password (ridders)
Obviously in a real world situation the FE would be a password protected ACCDE and the BE password would not be circulated

If anyone can see a security flaw in this latest version, please let me know by email or PM!
Don't tell the world in a future post!!


Once again, let me reiterate this point:
No matter what you do, Access databases can NEVER be made 100% secure
A capable and determined hacker can break any Access database given time
Attached Files
File Type: zip LinkedNoTables_v3.zip (461.8 KB, 6 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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
Previously known as ridders : 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.

Last edited by isladogs; 08-23-2018 at 11:05 PM.
isladogs is offline   Reply With Quote
Old 09-09-2018, 03:55 AM   #56
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: How to protect my tables and queries in access from importing

I don't really understand the issues with a user getting at the tables.

In most commercial databases the developer will normally provide a way to reach the data tables - eg Sage ODBC Link. After all the data is the user's. Without the form design and code, they can't do so much with it.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 09-09-2018, 06:06 AM   #57
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,473
Thanks: 62
Thanked 1,175 Times in 1,075 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: How to protect my tables and queries in access from importing

Dave, not to throw a stinkbomb towards the OP, but he apparently has not considered that with Windows, you can get at the data via screen capture anyway, although doing so would be tedious. If the person can see the data, they can get copies of the data using only the secure database functions that allow data display. That is because the Windows PrintScreen function isn't under Access control. It isn't functional under Access (that I recall, though it has been a while.)

Now, if he had underlying proprietary data that was never displayed but that allowed for some kind of conversion or computation, I COULD understand wanting to protect that intellectual property. But if his concern was just tables in general, there is an old security adage: The only truly secure system on your network is the one that isn't turned on at the moment.
__________________
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 09-09-2018, 09:33 AM   #58
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,473
Thanks: 62
Thanked 1,175 Times in 1,075 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: How to protect my tables and queries in access from importing

ahmedaliazad

Reading over this, I owe you an apology for an error of omission. There is one more step that is possible IF you / your company can do it.

If you have a private subnet within your company then be sure that your back end resides on a machine on the private subnet but for which there is no general internet access. The CLIENT machines that will use the BE can be exposed to the internet. But if there is a way to have a SERVER-side network that has no exposure to the world, you can still use ordinary router technology to isolate the machine hosting your BE. And in so doing, you wipe out huge numbers of potential external threats.

This method DOES NOT affect internal threats. But your company policies on firing or other penalties may deter those potential internal threats. It is all about risk. Splitting a network between in-house services and ... out-house services, for lack of a better metaphor, will add another layer of protection.

Your in-house IT people can tell you in a heartbeat whether this can be done on your company's network. But if there is any size at all to that company, I would be highly surprised to learn they had not already set up such a split network.

Again, I apologize for overlooking this method of help to secure your database BE.

__________________
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

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to protect tables in Access 2010? hannlapp General 1 03-06-2012 05:35 AM
importing excel to access keeping relationships in access tables emshim Tables 19 09-14-2007 01:18 PM
[SOLVED] Access, protect tables khaledtafech Tables 2 08-27-2005 01:13 PM
Protect Queries mark curtis General 1 07-08-2003 03:20 AM
Protect (hide) talbles and queries Herb General 2 01-14-2003 07:19 PM




All times are GMT -8. The time now is 05:46 AM.


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

Featured Forum post

Is Political Correctness Toxic?

Sponsored Links


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