Front End Database and Users

[isladogs]

Citrix is a proprietary name for a package that uses TS. Like Kleenex is a proprietary name for facial-quality paper tissues.

LOL
But that doesn't answer my question.
As I understand it, Terminal Services can be run from Windows Server without needing another package to 'host' it

 

[Pat Hartman]

I don't believe it it is just a nomenclature difference. Here's a few articles that explain the differences.

https://www.pluralsight.com/blog/it-ops/microsoft-rds-vs-citrix-xenapp-xendesktop
http://www.firstattribute.com/en/news/microsoft-windows-remote-desktop-services-vs-citrix-xenapp/
http://msandbu.org/so-why-choose-citrix-over-microsoft-rds-2/

 

[isladogs]

Thanks Pat

Looks like I was right about TS (now called Microsoft Remote Desktop Services).

Basically that can be used on its own & is 'relatively' simple to setup.
Citrix builds on this, providing additional functionality & is a total PITA to setup

I understood the first article but having no network management experience, I started to glaze over on the other two with words like hypervisor etc.

 

[Lilly420]

Thank you all for this information. I have a lot to think about. Another Access database that was being utilized when I started here was never split, and the users have minimal issues with it as they are all in the same location (same server). The one I am currently building, all the users are on the same server except me and two others on my team so maybe not splitting it is a better way...I am not sure. I am not familiar with Citrix installation at all. Again, thank you all so much.

Lilly

 

[The_Doc_Man]

Not splitting a DB only (stress ONLY) works if you can guarantee non-simultaneous use. The moment two users open the same FE or combined DB, you expose yourself to lock collisions that can become fatal to the DB. Particularly if there is one feature that both/all of your simultaneous users are most likely to touch.

If you know the term "destructive interference" as it relates to programming, that is the technical term for what you invite with a shared FE or combined DB.

 

[Lilly420]

Yes, I hear you on that. Where I work, we are not allowed to change anything on our computers--add programs, etc. everything is locked down-...and MS Access is not supported by our IT team, so it all falls on me...and I am trying to do the right thing for the users but at this point I am not sure how to go about doing that with my limited knowledge of the other ideas posted here and am not a programmer. The users will be accessing the same form in the DB but very unlikely the same record as they have different customers and maybe 2 or 3 of the users in there at a time. Again, thanks for the help.

Lilly

 

[The_Doc_Man]

"Same record" doesn't matter. "Same form" matters because it implies "same recordsource" (table OR query). There is where the lock collisions will occur.

Since Access must be installed on a computer for it to be used, that would imply that it is already on the workstations of all potential users. If so, then the rest of it is usually pretty easy. You merely COPY files - not actually INSTALL files - to distribute DB application files to a given user's workstation.

If you want to talk "locked down" then consider that my last job before I retired was with the Naval Enterprise Data Center in New Orleans. We had both Sensitive and Classified systems at that site. We had U.S. Dept. of Defense security standards in place. Yet I was able to manage my own Access project for the system administrators and was able to work on (consult with) a team doing an Access FE / SQL BE for the NAVMED group. So don't worry so much about "locked down." You'll hit a barrier now and then, but most of what you might need is eminently possible.

 

[Pat Hartman]

An Access database front end is a DOCUMENT. It is NOT an executable. If your people can copy files, then then can copy the FE.

Regardless of whether people are accessing the same record or not (Access takes care of this just fine), Access MODIFIES the FE. That is why it is dangerous to share it. When user A opens the FE file, Windows loads some of it into memory. Then user B opens the same file and windows loads some of it into memory. As each person works, different forms and modules are used and so more of the objects get loaded into memory. If User A changes the sort order on a form, now his version of that object is different from that of User B. If User A saves the form when he closes it, that updates the FE on disk. Now the version of that form that user B has in memory is not what was originally loaded.

You don't even have to change anything. Access simply always updates the FE. Pick any Access database. Look at the Windows updated timestamp. Open the database. Do not change anything. Close the database. Notice that the timestamp changed. The point is that it is dangerous to have multiple people updating the same FE simultaneously. There is not a danger with the BE because people are not updating database objects. They are updating rows in a table which is a different process and is managed correctly.

 

[Lilly420]

That makes sense what you are saying.

I have split the database, backend on the NJ server, I sent the frontend to two users that are on the NJ server as well. They saved it to their local Drive (C: Database). I had them each open it and make some changes, worked great--quick, audit trail capturing in the backend. I had them both go into their local copies at the same time and make changes...seemed fine. So because I am not on that server, it is slow for me opening from my local drive but that is OK, as I am not the main user of this database ... all the folks on the NJ drive are and two on my team are there as well.

I have changed the options on the front-end to not allow special keys, the navigation panel, and full menus from appearing, the only way to open the front-end to allow these to reappear is by holding the Shift key as they open and none of them know this so hoping this is enough.

Thanks again for all the help. Hopefully I am on the way.

Lilly

 

[The_Doc_Man]

You can disable that Shift Key trick as well. What I did was I had a "switchboard" or perhaps you would call it a "Dispatcher" form. There was a command button on that form that I could click to have it undo all of the protections and close the switchboard. But the real trick is, never do that. Instead, always work from an unsecured copy as your developer platform.

Then, when a new version comes out, COPY that and secure the copy. NEVER EVER let the unsecured copy out of its cage. If YOU need to work on the DB, give yourself a secured copy too.

That way, you never have to question whether your copy is correct or not. If it is the secured copy, it is the "official" version. If it is unsecured, it is the "unofficial" version.

 

[Lilly420]

Yes, I know that you can disable that. When you say a secured copy ...what do you mean? Please forgive my ignorance, I haven't done this before.

I read in an article that I should make the FE a ACCDE file and then distribute for security but the FE I have would not allow it--it said it was unable to create-This error is usually associated with compiling a large database, a considerable number of TableID references created for each table. The Access DB engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects.

There are a lot of tables in the DB...and it has an audit trail on the forms.

So, I did not do this...are there any other ways to secure the DB other than my trick?

Thank you for all the help.

Lilly

 

[CJ_London]

but the FE I have would not allow it--it said it was unable to create

do you have Option Explicit at the top of every module? And did you compile the code (VBA window Debug>Compile). If there are any compile errors, you cannot create a .accde

 

[The_Doc_Man]

When you say a secured copy ...what do you mean?

You already mentioned that you do some things to try to hide the inner workings from your people by disabling full menus and preventing other activities. This is part of securing a database file. You start with a wide-open DB file. You then use the File > Options path to Current Database to start locking stuff down. You can also search this forum for other steps to prevent tampering.

So to keep you sane, you develop from your private 'dev' copy. Then you copy that to another file. Then you do the final "current database" settings to prevent user diddling ON THE COPY. You keep your unsecured 'dev' copy private. You keep the public production copy on the server in the appropriate place that you have set aside for this purpose. Whatever procedures you have in place for updating the FE, they center on that 'appropriate place.'

As CJ points out, if you can't compile, something is usually wrong, and I would make it a priority to get a clean compile.

 

[Lilly420]

Good morning,

I went through my modules and one was missing the Option Explicit so I added that in and then I ran the debug on my code, came up with an error on my audit trail code, it was missing a reference so I Googled the error and it said to check the Microsoft ActiveX Data Objects 2.5 Library, which I did and then I ran again and no errors. It then let me make the database an ACCDE...but when I tried to open it, it gave me a security issue message and that some content would be disabled but I could open...so I did. My switchboard (which is for the user to open forms, etc.) and my audit trail code no longer works...did I do something wrong? Sorry for all of this ...I am trying and appreciate all your help.

I will do more research on ways to lock it down without making in an ACCDE if that is the case.

Thanks again all.

Lilly

 

[CJ_London]

the security warning as about trusted locations - you should have a message about trusting the file.

if you don't want people messing with your forms, reports and code, .accde is the only way to go.

Always helps when you get this sort of message to screenshot it so others can see what you mean

 

[The_Doc_Man]

The good news is that if you instruct your users to trust the .ACCDE the first time they run it (or a new version of it), they won't see that message again because they have the option to trust it. Once trusted, always trusted. But once updated, start the cycle over again.

 

[Frothingslosh]

The good news is that if you instruct your users to trust the .ACCDE the first time they run it (or a new version of it), they won't see that message again because they have the option to trust it. Once trusted, always trusted. But once updated, start the cycle over again.

Unless you have them trust the LOCATION instead.

 

[isladogs]

When I distribute applications, I always specify the location of the FE.
I also include script to set that location as trusted in the registry for all versions of Access from 2007 onwards. That means the location remains trusted when updates are released.
Neither end users nor admins ever need to set anything.

 

[Frothingslosh]

Yeah, that's an option you have as an independent developer distributing your own products.

As a staff programmer, I'm not actually allowed to alter the registry via code since I'm not actually in IT. It would make my life a lot easier if I were, but that's an easy way to get shitcanned. Instead, we either set up new users with a trusted location (all my team's apps go into subfolders inside a department folder inside C:\Users\Public) ourselves, or else step the users through doing so if they're not within walking distance.

By setting the department folder and all its subfolders as trusted, we only have to do it once. It's the best compromise we could set up without involving IT (and thereby invariably having to wait 4 to 8 weeks for them to 'test' every time we want to push an update).

 

[The_Doc_Man]

Colin,

Frothy and I have experienced the same problem. If you aren't on the IT / network security staff, you haven't got the authority to trust a location. And in fact depending on the group security templates that get downloaded when your machine logs in to the network, that ability might be blocked. Therefore, I gave the more commonly used answer. I'm not saying that you were wrong - but in this case, your answer isn't always right either, depending on just how persnickety your network wonks happen to be.