Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Don't know how to procede....

    I have an Access FE and SQLSE BE. We did nave it in the cloud for around 4 years, but currently, it is on a local server.

    The dB was developed for us to have multiple clients, that would send us their data in a CSV file. We import the data, do the calculations and sent the reports back to the clients.
    The Main table has about 1.5m records and the employee table has about 8,000 records.

    I keep reading about DNS-less connection between the Access FE and the SQL BE. I watched the videos by Stephen Bishop and can now display records in a new form without using an ODBC connection.




    But I want to move the queries from Access to the BE. Now, how do I use a SQL query/SP/View as the record source for a report? Or a form?
    There are tons of videos/examples on how to connect Access/SQL for beginners, but I can't find any more advanced videos/dBs on HOW to accomplish this.


    Where this is headed.... The Big Dog (the guy with the check book) wants to have the clients do all of the work (data entry and pulling their own reports).
    At some point, the FE/BE would be moved back to the cloud. There would be a VM for SQL server and each client would have their own VM,
    The dB was designed to have multiple client's data in the same dB - now I have to secure the data, so NO client can do anything to see any other client's data.

    Would that be no linked tables or queries in the Access FE? And the FE made into an accde.

    Thoughts, directions, warnings, questions, help??

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use DSN to add another layer of security that only those PC's can get in.
    but you can use DSNless if you want. I just find it harder to edit the app.

    the tUser table has settings so they only see their data.
    But every user must have their own FE.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Steve,
    You could use a pass-through query based on a SQL view or an ADO recordset as a form's recordsource using the user id as criteria to ensure data security.
    https://flylib.com/books/en/2.401.1.363/1/
    https://stackoverflow.com/questions/...cted-recordset
    https://www.access-programmers.co.uk...-query.232744/
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Regarding limiting users to only certain data in tables, you have to do something really special. Even in a .accde file that is supposed to restrict user access, the user can simply press Ctrl-G and voila, the entire Visual Basic editor comes up and the user pretty much has full rein. SQL Server lets you restrict table access by user, but that's an all-or-nothing access: the user can access all data or no data in a table. Your problem is much different, because you only want certain users to access certain data in a table. But since it's all-or-nothing, you can't expose your tables at all. For instance, your FE basically would have to start up totally BLANK, ask user for ID and password at startup, then programmatically create links to tables on-the-fly (via passthrough queries, for instance) that would only expose the right data to the right user. It could potentially be a headache. That's why when I had this problem years ago, I used web interface as front-ends instead, and I didn't let my users use Access at all. That way, I had much greater control of what the user could see. Of course, that would mean a paradigm shift in FE design because web interface doesn't really use "forms" per se. You look at all the web designs out there, and most use a kind of wizard-based UI. Report generation in a web interface would be a challenge as well. You could create an Access FE just for reports, with heavy restriction to data.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the entire Visual Basic editor comes up and the user pretty much has full rein.
    Q: What can you do in the vbe in an accde?
    A: Nothing.

    All you can do is see the names of the objects in the object pane.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by Micron View Post
    Q: What can you do in the vbe in an accde?
    A: Nothing.

    All you can do is see the names of the objects in the object pane.
    The immediate window is open in the VBE, where the user can run any commands and god knows what else. The user can create query, table, run query, look up password in DSN, etc. -- as I said, free rein.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was only thinking about being able to look at the code. Why would you use the immediate window to do any of that stuff when you can create a query or table with the ribbon even though the db is an accde? You don't need the vbe to see DSN either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by Micron View Post
    I was only thinking about being able to look at the code. Why would you use the immediate window to do any of that stuff when you can create a query or table with the ribbon even though the db is an accde? You don't need the vbe to see DSN either.
    With the immediate window the user has COMPLETE access to all the database objects, table names, field names, properties, etc. It is basically game over, and you may as well give the user complete access. That's why I said the OP would have to do something amazing to pull this off -- such as having a blank database at startup. If it's blank, there are no objects or properties whatsoever for the user to gain access to.

    Regarding exposing credentials in a DSN, one can mitigate it by exposing only non-admin type credentials.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by keviny04 View Post
    With the immediate window the user has COMPLETE access to all the database objects, table names, field names, properties, etc. It is basically game over, and you may as well give the user complete access. That's why I said the OP would have to do something amazing to pull this off -- such as having a blank database at startup. If it's blank, there are no objects or properties whatsoever for the user to gain access to.

    Regarding exposing credentials in a DSN, one can mitigate it by exposing only non-admin type credentials.
    I disagree with your comments about ACCDEs. Whilst no Access or even SQL Server app can ever be made 100% secure, by using a variety of security measures together with an ACCDE format, the app can be made secure enough against all but the most skilled and determined hacker with plenty of time on their hands.

    For an example of a reasonably secure Access split database, see Encrypted Spilt No Strings - Mendip Data Systems
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24
    Quote Originally Posted by isladogs View Post
    For an example of a reasonably secure Access split database, see Encrypted Spilt No Strings - Mendip Data Systems
    That looks extremely locked-down. Any metrics re: how that affects performance?

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by wvmitchell View Post
    That looks extremely locked-down. Any metrics re: how that affects performance?
    It is very strongly locked down which is why I offered it as a challenge to break the security.
    Despite that it can be used exactly like any standard database.

    I haven't noticed any effect on performance in use. However, I've only tested it with a local BE. Obviously like any split Access database, it would be slower with a network copy of the BE
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by isladogs View Post
    I disagree with your comments about ACCDEs. Whilst no Access or even SQL Server app can ever be made 100% secure, by using a variety of security measures together with an ACCDE format, the app can be made secure enough against all but the most skilled and determined hacker with plenty of time on their hands.

    For an example of a reasonably secure Access split database, see Encrypted Spilt No Strings - Mendip Data Systems
    I initially said to the OP that if he used ACCDE, he would need to do something really special. And your example database just proved my point. Rather than using Access, I suggested the OP to use something that is inherently more secure that doesn't involve Access at all, such as web interface with ASP, for instance. Once you give the user the Access file, you pretty much give him everything. So why not give him nothing at all?

  13. #13
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by isladogs View Post
    It is very strongly locked down which is why I offered it as a challenge to break the security.
    Despite that it can be used exactly like any standard database.

    I haven't noticed any effect on performance in use. However, I've only tested it with a local BE. Obviously like any split Access database, it would be slower with a network copy of the BE
    On your Access 365 32-bit front-end, when I click View/Edit Data, click View Report, close the report, click Close, and click View/Edit Data again, the app just shuts down. Is that by design or a bug?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by keviny04 View Post
    I initially said to the OP that if he used ACCDE, he would need to do something really special. And your example database just proved my point. Rather than using Access, I suggested the OP to use something that is inherently more secure that doesn't involve Access at all, such as web interface with ASP, for instance. Once you give the user the Access file, you pretty much give him everything. So why not give him nothing at all?
    I'm unclear how showing an example of a split Access database has proved your point...
    I've given you the Access FE & BE but I certainly haven't given you 'pretty much everything'

    I accept its a fairly extreme example of security. However, all of my ACCDE FEs employ several of the security features included in that example.
    See my article Improve Security - Mendip Data Systems

    Quote Originally Posted by keviny04 View Post
    On your Access 365 32-bit front-end, when I click View/Edit Data, click View Report, close the report, click Close, and click View/Edit Data again, the app just shuts down. Is that by design or a bug?
    Thanks for letting me know. Its a bug that hadn't been reported until now and is, I believe, fairly simple to fix
    In fact the app doesn't shut down - its just becomes inaccessible. If you look in Task Manager, Access is still running in background processes
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by isladogs View Post
    I'm unclear how showing an example of a split Access database has proved your point...
    I've given you the Access FE & BE but I certainly haven't given you 'pretty much everything'

    I accept its a fairly extreme example of security. However, all of my ACCDE FEs employ several of the security features included in that example.
    See my article Improve Security - Mendip Data Systems
    My point was this would take an extraordinary effort (which yours certainly is, and time is money), and it still wouldn't be 100% secure as you admit yourself in your documentation. If you give something that isn't 100% secure to someone, then you basically give him everything. A desktop client should ideally be a stand-alone app, not a DEVELOPMENT TOOL. If it is latter, it is always insecure. This is at the heart of why Access is inherently unsuitable for certain tasks. I would only do this if I absolutely must have Access, which many people have made do without.

    P.S. I don't know why Microsoft doesn't have a version of Visual Studio that is specifically designed for database. That would let us make stand-alone database applications that have no "backdoor" whatsoever to underlying design, and it would solve a lot of these problems.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums