Using the Access program installed on Terminal Server (1 Viewer)

[amorosik]

I have a computer that we will call PC1 that installs Windows 2022 server as the operating system
The terminal server service has been activated on this PC
There are 10 different users on PC1, user1, user2, user3, ...
In the directory C:\PUBLIC\ERP2\BIN there is the ERP2.ACCDE file created with Access 2013
The ERP2.ACCDE program connects via odbc to a SQL Server database that is also physically installed on PC1
The Microsoft Access 2013 runtime is installed on PC1 and therefore ERP2.ACCDE starts and runs correctly
Let's suppose that an operator uses PC1 locally, logs in as user1 and starts the ERP2.ACCDE program, which starts correctly

The questions is:
1- It is possible for other users, for example user2, user3, user4, etc., to connect from other PCs via rdp to PC1 and use the program ERP2.ACCDE?
2- The various instances of the Access runtime would use the same ERP2.ACLDB lock file, is this possible?
3- Is there something else that is shared between different sessions that could hinder this way of using ERP2.ACCDE?

 

[The_Doc_Man]

Answering your primary questions in order...

1. Absolutely, yes. RDP is the protocol used by (among others) Citrix, about which you can find dozens if not hundreds of articles on this forum. Look for articles by Pat Hartman, who has significant experience on this subject.

2. Yes, they can share the lock file - but the problem is that if it is a monolithic .ACCDE file (all elements in one file) you run a massive risk of file corruption. (See next answer.)

3. Yes... and no. This answer is trickier, but stay with me.

When user1 open a monolithic DB, that opens the tables, query list, documents list (forms & reports), macro list, module list... and the working space of the DB. (Workspace(0) is its name in any session.) The workspace data is exposed within the working memory of user1's session. That ALSO opens MSACCESS.EXE - which has pointers into the DB file and workspace(0), also in user1's session.

Now user2 comes along and opens the same monlithic file - but in a different session. (Has to be different because of Windows security rules about inter-process isolation.) So that different session has active copies of the same pointers as user1 - but due to Windows isolation rules, the two users DON'T (CAN'T) KNOW EACH OTHER IS THERE.

The first time EITHER of them attempts to allocate something within the context of the MSACCESS.EXE "heap" or "stack" they are dealing with potentially overlapping process-critical structures. The first time either of them inserts, updates, or deletes a record that the other user is also using, they now have different views of the same internal object. Doing two different things to the same structure at the same time without checking for interference will eventually cause Access to attempt to simultaneously change the internal pointers that link records to each other in a table structure to two different values at once. The technical name for this is "destructive interference" but the common phrases used for this? Too many cooks spoil the broth; Left hand doesn't know what the right hand is doing.

Note that the difference is not only in the stored table structure but also because while the table is active, copies of its structure are open in the memory for each session. THERE is where the problem lies. The lock file can protect the tables but there is no lock structure for the actively open copies of data structures in session memory.

Therefore, the precise answer to your #3 question is that you don't worry about what is shared; rather, what isn't shared but should be.

The correct way to do this is:
(a) Split the DB into a front-end and back-end. The back-end, if properly split, doesn't need to be an .ACCDE, could be a .ACCDB instead. But the front-end can/should be a .ACCDE if that is what you wanted.
(b) Each potential user must have a separate remote login to that server, to include a private folder in which they can store their COPY of the FE file.
(c) Note that your EULA for Office - for most "normal" installations - is single-user. To stay legal, you need to EITHER restrict this DB to one user at a time (in which case the split isn't really necessary) OR you need to get a multi-user license for Access with as many "seat credits" as the number of simultaneous users (in which case the split is HIGHLY necessary.)

Failure to do this will result in database corruption. Sharing the back-end file is something Access knows how to do. Sharing the front-end file, however, isn't so smooth because the actual memory modifications are being done through the front-end's session. And there is where the corruption would originate.

 

[amorosik]

.....
The correct way to do this is:
(a) Split the DB into a front-end and back-end. The back-end, if properly split, doesn't need to be an .ACCDE, could be a .ACCDB instead. But the front-end can/should be a .ACCDE if that is what you wanted.
(b) Each potential user must have a separate remote login to that server, to include a private folder in which they can store their COPY of the FE file.
(c) Note that your EULA for Office - for most "normal" installations - is single-user. To stay legal, you need to EITHER restrict this DB to one user at a time (in which case the split isn't really necessary) OR you need to get a multi-user license for Access with as many "seat credits" as the number of simultaneous users (in which case the split is HIGHLY necessary.)

Failure to do this will result in database corruption. Sharing the back-end file is something Access knows how to do. Sharing the front-end file, however, isn't so smooth because the actual memory modifications are being done through the front-end's session. And there is where the corruption would originate.

Many thanks for the always useful technical advice
Maybe you didn't read that Erp2.accde contains only the code, while the data is on a Sql Server

So basically you say that it is better to make many separate directories like:
c:\public\erp1 for user user1
c:\public\erp2 for user user2
......
c:\public\erp100 for user user100 ???

As for the Office license, in reality only the Access 2013 runtime is used, there is no complete Office installed
In this sense I think it can also be used in this type of system

 

[The_Doc_Man]

OK, I missed that there is an active back-end server. Guess I read that part too fast. So that takes care of the "split" part. But yes, you should have separate folders and COPIES of the front end file, one for each user. That way everyone has their own PRIVATE copy of all those internal pointer structures. The back-end takes care of synchronization of the tables. Separating the multiple copies of the front-end takes care of all file locking and internal data pointer issues. I'm not up on the delicacies of a run-time only situation, but as I recall you don't need a license for the distributed run-time version. So your last paragraph does make sense.

 

[Pat Hartman]

YOU don't create all those separate directories. Each user is allocated a personal directory as part of the startup of the session. Therefore, you create a batch file that the user opens from a shortcut on his desktop. He NEVER opens the actual .accde file. The batch file copies the FE to the user's personal directory and opens it from there.

Here is a copy of a Citrix batch file. I think the RDP version is the same but I can't find one so the token names might be different.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdr
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdr" %USERPROFILE%\DwgLog
start %USERPROFILE%\DwgLog\DrawingLog.accdr

1. create the directory - no error raised if it already exists, who cares
2. deletes the file - no error raised if there is no file there, who cares
3. copy the file from the master directory - this will automatically raise an error if the file is not found. You can make your own error message if you prefer
4. starts the db, shouldn't be any error if we get this far.

I also have version tables in both FE and BE which are compared on startup to ensure the correct FE is being used to open the BE. The app raises an error and closes if the two are out of sync.

 

[amorosik]

YOU don't create all those separate directories. Each user is allocated a personal directory as part of the startup of the session. Therefore, you create a batch file that the user opens from a shortcut on his desktop. He NEVER opens the actual .accde file. The batch file copies the FE to the user's personal directory and opens it from there.

Here is a copy of a Citrix batch file. I think the RDP version is the same but I can't find one so the token names might be different.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdr
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdr" %USERPROFILE%\DwgLog
start %USERPROFILE%\DwgLog\DrawingLog.accdr

1. create the directory - no error raised if it already exists, who cares
2. deletes the file - no error raised if there is no file there, who cares
3. copy the file from the master directory - this will automatically raise an error if the file is not found. You can make your own error message if you prefer
4. starts the db, shouldn't be any error if we get this far.

I also have version tables in both FE and BE which are compared on startup to ensure the correct FE is being used to open the BE. The app raises an error and closes if the two are out of sync.

Ok, then a 'master' used only as a template
And each user who uses their own copy from their own directoryes
Everyone who communicates via odbc with the only existing dsn and the only existing db

 

[Pat Hartman]

Correct. The setup with RDP or Citrix is exactly the same as it is with a LAN. Every user uses his own personal copy of the FE. You can use any one of the suggested methods to make that happen. I use the batch file I posted because I want the user to download a fresh copy of the FE each time he opens it. the FE's are not large and so the overhead is small. It also eliminates the possibility of any bloat. Comparing the FE/BE versions when the FE opens ensures that the users never use an old FE.

If you understand how Access works, you understand why this is the recommended method. There are dozens of posts here explaining these concepts. Your Access FE is like any other "document" file. A word doc, an excel spreadsheet, a pdf, a text file ALL = the .accdb/.accde. They are opened and loaded into memory on the computer that opens the file. When two computers have the same file opened in memory on two different computers, each computer can separately update the version in memory and when Access goes to save the changes, discrepancies must be resolved. Whether your code updates objects or not, Access updates the FE file. You can observe this yourself quite easily. Check the datetime of the file. Open the file. Open a few forms. Don't change any objects. Close the FE. Notice that the file's datetime changed. That means that Access updated the file. This doesn't happen when you open word or excel, etc. If you don't actually change the document, its datetime does not change.