Virtualizing an Access Solution (1 Viewer)

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Does anyone have experience virtualizing an Access database on VMWare's Horizon? I have a Access FE connected to a SQL Server. It works fine with Office 365 version of Access and the Access redistsributable engine. When it is virtualized certain code does not work and formatting within Forms is not working (I have to actually change the field type in SQL Server to get dates to display correctly).

Anyone heard of anything like this?

TIA
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:25
Joined
Feb 19, 2002
Messages
42,981
Are you trying to replace the functionality of Remote Desktop or Citrix? You need to understand how Access actually works if you are trying to share an Access application with multiple users. Doc, is are expert in these things, Hopefully, he'll chime in.
 

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Pat,.

I am not replacing all functionality. Using Horizon they have access to only this application. Not a whole desktop. It is designed to replace the use case where each user had a local installation of Access and opened the FE which connected to a Access db BE. VPN was used to connect to the BE.

The VPN can no longer be used and I have changed the BE to SQL server. Since the VPN is not available VMWare Horizon is the another alternative.

Scott
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:25
Joined
Feb 19, 2002
Messages
42,981
RD and Citrix work. This solution may but I don't know. Look for the_doc_man.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
We had many systems with the Navy running VMWare, I'd guess over 1000 in total, but they were all acting as small servers. We all were issued laptop workstations that adapted to docking stations, so everyone had physical hardwired desktops. I looked up Horizon but am not sure of what is going on with it other than it is a virtual desktop.

When you say "When it is virtualized certain code does not work and formatting within Forms is not working" can you be more specific? OK, obviously there is a Forms issue. What else doesn't work? What are the symptoms? How do the formatting issues present themselves?

Another question comes to mind. If you are replacing the idea of having multiple copies of Access (one per user thereof), do you have a properly multi-user licensed copy of Access on that Horizon VM?

Another question: In the configuration you are running, does each user have a private storage area in which they could save a copy of the FE file?

If you have actual failures like "crashing" or strange exits from Access, can you (or your IT people) find the system event logs? If so, note the time/date of a failure and get the IT folks to look for Access-based event log entries around that time.

I know that VM systems can emulate certain hardware configurations depending on how they are configured. Since this is supposed to be a limited VM instance, do you have any insights into just how limited it is?
 

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Hi,

We have an enterprise license for Office 365 does not allow for virtualization. I am using the 2016 Access engine which is freely distributed by MS. When installed locally everything works fine.

On one particular form this code in a control does not work:

=IIf((IsNull(Forms![Contracts Tabbed]![date completed])) And (Forms![Contracts Tabbed]![date contract routed]<>IsNull(Forms![Contracts Tabbed]![date contract routed])),"Routing"," ")

This code does:

Private Sub Command35_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim dtFieldValue As Date
Dim varHold As Variant

stDocName = "Contracts Tabbed Read Only2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, "Contracts Tabbed Read Only2", acGoTo, 10
dtFieldValue = Forms![Contracts Tabbed Read Only2].[Current Term Exp]
varHold = IIf(dtFieldValue < Date, dtFieldValue, "false")
MsgBox varHold

End Sub

Also even if a text box control is formatted short date the displayed date follows the database data not the formatting in the form.

I do not know about how Windows itself is virtualized (I do know it is licensed).

I think that lays it out but let me know if I missed anything.
 

isladogs

MVP / VIP
Local time
Today, 07:25
Joined
Jan 14, 2017
Messages
18,186
I use a VM called Virtual Box with all versions of Office from 1.0 through to 365.
This works absolutely fine but each VM requires its own licensed copy of Office/Access
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
Code:
=IIf( 
    ( IsNull( Forms![Contracts Tabbed]![date completed] ) ) And 
    ( Forms![Contracts Tabbed]![date contract routed]<>IsNull(Forms![Contracts Tabbed]![date contract routed] ) ),"
        Routing",
        " ")

This expression shouldn't work on any system. The "AND" second operand is comparing a date to an IsNull result, which compares a Boolean data type to a non-Boolean data type. That ain't gonna fly too far.
 

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Sorry Inherited code base. I meant to post this piece:

=IIf([Forms]![Contracts Tabbed Read Only2]![Current Term Exp]<Now(),"*Expired*"," ")
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
The item you showed in post #9 will depend on date formats. Since you are looking at a form, I'm thinking the form's date is in text format and Now() is not. Either encapsulate the form's date with CDate() OR convert Now() to "#" & FormatDateTime( Now(), vbShortDate ) & "#"

 

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Hi Doc,

I will try the CDate() solution. What would be the correct way to take the date from the record and not the form? Any idea why this works on local or networked installs but not in virtual?

Scott
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
Any idea why this works on local or networked installs but not in virtual?

Have never run in a virtual desktop of that kind so I don't know about the Personalization settings. A networked install has local settings in the registry but I have not a clue in the world where the virtualized version keeps its stuff. Probably in a per-user copy of the HKLU hive, I suppose, since the registry does support multiple users by breaking up into pieces-parts. But I would also think that in the absence of editing the "Local User" hive that your settings would be the default for the VM as a generic/vanilla desktop.

What would be the correct way to take the date from the record and not the form?

If the form is bound, why would you think they would be different? If no one has edited the date value they should be the same, I would think. IF you really had to do this, there is a way to open the form's .RecordSetClone then use the main recordset of the form to get a bookmark to then navigate to the record via the clone, then read the value of that field. There is also the possibility that if the control and the underlying field DO NOT have the same name, you could just use Me.fieldname to get it. However, if the form was generated, it is often the case that the control and field have the same name and in that case, the Me.fieldname could equally mean Me.controlname - i.e. ambiguous. Access doesn't like ambiguity.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Jan 20, 2009
Messages
12,849
However, if the form was generated, it is often the case that the control and field have the same name and in that case, the Me.fieldname could equally mean Me.controlname - i.e. ambiguous. Access doesn't like ambiguity.
Where there are controls and fields by the same name, the Control takes priority.
Fields can be referred to as Me.Recordset.fieldname
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
True that, G. - I had forgotten that particular path because I so rarely allowed the "same name" situation to occur in the first place that I never needed to use that. Out of sight, out of mind.
 

smaction

New member
Local time
Today, 00:25
Joined
Apr 6, 2021
Messages
10
Thank you both.

CDATE() works so that puts me on the right path (I think). Still not sure why control value is being converted to string in virtual environment but not non-virtual. I will experiment to Me.Recordset.fieldname to see if that sheds any light on the issue.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 28, 2001
Messages
27,001
At least you have something that works. That is good. It might be worth your while to drop at least part of this problem into the laps of the IT people because when you have problems like "works on system A, fails on system B" then there is a configuration difference between the two. If the VM environment that contains the Access GUI is not configured like the O365 Access GUI then I have to say that something is wrong with the VM setup. It is probably a configuration setting.

One difference that I think HAS to be true (as I build an image in my mind of this setup) is that almost surely, the O365 copy is using a different registry than the VM copy, and the VM copy is being used as a one-size-fits-all case. Which, in my not-so-humble opinion, is never true. One size does not fit all.
 

Users who are viewing this thread

Top Bottom