locked
MS Access with Windows Virtual Desktop RRS feed

  • Question

  • My organization just switched to WVD (Windows Virtual Desktop) for all remote access (we all now work remotely).

    When they did this the version of Access changed to Office 365 which is 64-bit. This will not open any of our existing Access databases which are all 32-bit, created in Access 2010. The Office 365 version does not allow the creation of any new databases. So I guess this spells the end of Access in our organization. Most of our databases have been converted to web applications but there are still a few left that are used every day by multiple people.

    To get around this issue, they installed a 32-bit version of Access as a 'remote app', which requires us to logon again (to some other machine I guess). We can no longer just click on an Access database and have it open with 32-bit Access, so none of our shortcuts or icons now work.

    Does anyone know if it's possible to have 32-bit MS access installed with WVD without it having to be a 'remote app'. The Desktop Admin agents gave the impression this was the only possible solution.

    Tuesday, September 15, 2020 4:10 PM

Answers

  • >I have no control over the software on our network. I am a small cog in a large engine.

    Sure, that is fair. And most of us have often found ourselves in such a case. So, sure, not a new occurrence in our industry.

    However, that team and group of people getting paid to make these decisions does have a responsibility here - and that includes things like making a significant decision to upgrade office from x32 to x64 bits. Such planning and decisions should not occur in a vacuum. So, sure, not your plate or decision, or your fault. But someone is responsible here.

    >However, I have now found that any code that refers to DAO objects will not compile and when I select the DAO 3.6 library, it says 'Error in loading DLL'. E.g.

    Since Access 2007, you don't need, nor want to have a DAO reference (set in VBA editor - it not required anymore). If you using 2007 or later, and ALSO using the accDB format? Then DAO is now built into access, and is not required. 

    So, the code should compile and work as before. And that includes all DAO code - as that part is built into access.

    It is possible that you also have a ADO reference. I would remove it, and try to compile. 

    Now, as a general rule?

    Well,

    dim rstData   as recordset

    The problem with above? you don't know if that going to give you a ADO reocrdset, or a DAO one?

    So, as a habit I use:

    dim rstData as DAO.Recordset

    However, the above is little help for existing systems. So, I would consider removing the DAO reference, and removing the ADO reference. (if this is a accDB file).

    The BEST way to see the "min" required references? Create a blank new accDB database. Now ctrl-g to jump to debug window/VBA IDE. Now go tools->references. That blank database will give you the min required references. You see WHEN you do this, that no ADO and no reference to DAO is required or will appear. 

    However, you can (and will) use dim rst as recordset, and you find that even dim rst as DAO.recordset code will continue to compile, and continue to work. This is despite the fact that no DAO reference appears or is required. So, since 2007 (13 years now), a DAO reference is not required or even recommend when using accDB formats.

    The only real issue here is if ADO was/is being used. If you using both ADO and DAO? Well then which ever reference comes first will be what kind of recordset you get (assuming no prefix before recordset).

    So, you always want to try and compile the application BEFORE you attempt to run. This will catch bad code, or the very least ensure that you above code sample compiles.

    So, the ORDER of the references can thus matter - especially if ADO was used. But, if your remove the ADO reference (and DAO if accDB), then a compile will tell you instant if ADO was being used.

    If the application does not compile, then some errors and code can fool you, and even simple dim rst as recordset can break until such time you find/remove all compile errors.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Thursday, September 17, 2020 6:42 PM

All replies

  • Try:

    1. Open a 32-bit Database using the Shift+Enter method which bypassess all auto macros and VBA code.
    2. Replace all Public and Private Declare statements with Declare PtrSafe
    3. Office 365 has no problem opening older versions as long as you change all the Declare statements to 64-bit statements using PtrSafe

    So for example:  Public Declare Function becomes Public Declare PtrSafe Function.

    See if that helps.


    Tuesday, September 15, 2020 5:08 PM
  • What Lawrence suggested is a necessary first step. However, converting API declarations to work in 64-bit requires more than just adding PtrSafe.

    In addition, all instances where Long is the datatype need to be converted to LongPtr where this is referencing a pointer/handle such as hWnd. 

    However there nay be additional issues with some older VBA libraries and ActiveX controls as not all work in 64-bit Access. 

    Unfortunately, this scenario requires someone to work through the code in each app to check it runs in 64-bit.

    That is, unless the network staff can be persuaded to swop all Office 365 installations back to 32-bit. It is highly unlikely that anyone actually needs the 64-bit functionality


    • Edited by isladogs52 Tuesday, September 15, 2020 5:45 PM
    Tuesday, September 15, 2020 5:45 PM
  • Once I've made this change to include PtrSafe, can the database still be opened by 32-bit Access?
    Tuesday, September 15, 2020 9:49 PM
  • Yes (Access 2010 or later). No (Access 2007 or earlier)

    One more thing. If your front end is an ACCDE file it will only work in the bitness it was created in

    Tuesday, September 15, 2020 10:07 PM
  • Any other changes needed to this?

    Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Wednesday, September 16, 2020 2:15 AM
  • Well, there is no special reason for having adopted x64 bit office.

    like any company, when doing a office upgrade, and especially from x32 to x64 bits? That can take a significant amount of planning and testing.

    There is NO REAL reason as to why someone out of the blue decided jump from x32 to x64. (yes, office x32 will run fine with your setup).

    However, even for word, Excel and other office applications? A company can't just out of the blue decide to move to x64 bits and not plan for such a change. I mean, several of the popular accounting packages (Sage and Simply) do NOT have x64 bit versions. As a result some of the office integration with outlook etc. can totally break down.

    So, who would out of a blue change office on you, and not be part of the significant planning process to determine if accounting packages, and TRUCKLOADS of software that can break as a result? Someone failed to plan here. 

    While some companies can spend significant testing and time and creating a inventory of software systems when JUST upgrading to the NEXT version office? Well a jump from x32 bits to x64 bits is as significant as a upgrade say from windows 3.1 (16 bits) to windows 95 (x32 bits).

    So, someone has to be held accountable here. So, for sure the virtual desktops could have run x32 bit versions of office (office 365 does not change this issue), and then a test group of people would be put together to plan and test for a upgrade to office x64. so even word add-ins, any ActiveX controls used even in Excel will not work either.

    And then there are companies that have built all kinds of software in office that works with QuickBooks or Sage or Simply accounting. And NONE of those vendors are offering x64 bit versions of their accounting packages. 

    A whole architecture change in office is not something that is done without planning or out of the blue. So, those remote desktops could have certainly upgraded to office v-next versions, but should have (and can) remained x32 until such time your company puts together a team and group of people to plan for a upgrade to office x64 bits. This required planning is by no means only limited to Access.

    You can certainly take existing applications, and convert them to run as x64 bits, but you need some planning here. And if any ActiveX plug ins or 3rd party tools are being used? They all tend to not work as x64 bits.

    So, planning and some organizational  skills and resources are required for such a upgrade. I can't imagine that any IT company or group of people would just "decide" out of the blue that such a significant change can be done at the flip of the switch. Someone in a serious way has dropped the ball here.

    How easy to convert Access x32 applications to x64 bits? Well, it will depend. If there is no VBA code, and you have just some data? Then in general those databases should work just fine. The more code, the more features, then the greater efforts and planning is required. So, most VBA code will work un-changed. The only parts that require changes are windows x32 API calls. I have for about 10 years now avoided API calls. So to pop a file dialog, there is a built in VBA filedialog, and thus no need for windows api. 

    So how many API calls, or do you use any 3rd party ActiveX controls or tools? (those are really a challenge, since a x64 bit tree view for example is not available right now). So, how easy? Well as noted, it depends. But then again there was a transition team and planning team put in place before this change? Right?

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, September 16, 2020 8:26 AM
  • There is no reason to use an API to get user name.

    Both of the following are far simpler (one line of code) & work in both bitnesses:

    Environ("UserName")

    CreateObject("WScript.Network").UserName

    For more details, see http://www.mendipdatasystems.co.uk/get-user-name/4594424315

    =========================

    However, the modified code  by Dev Ashish that the OP gave will work in 32/64-bit Access from 2010 onwards

    If any users have older Access versions, you need to use conditional compilation:

    #If VBA7 Then
             Public Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
                      "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long      
    #Else
             Public Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                      "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long  
    #End If


    Wednesday, September 16, 2020 8:30 PM
  • I have no control over the software on our network. I am a small cog in a large engine.

    We just switched from remote access via Citrix to remote access via WVD.

    We are also completely switching from in house servers to MS Azure Cloud storage.

    MS Access (and Office as a whole) is being phased out, hence the decision to not allow the creation of any new databases. There are just a couple of databases left that have not been transitioned yet. 

    The new WVD remote connection gives a predefined desktop with Access 365 with the 'create database' option disabled. They begrudgingly gave us access to a 32-bit version (Access 2016 I believe) which requires an additional logon.

    I have found I can open and compile with the 64-bit version if I make the changes described in this thread.

    However, I have now found that any code that refers to DAO objects will not compile and when I select the DAO 3.6 library, it says 'Error in loading DLL'. E.g.

    Dim qd As QueryDef
    Dim db As Database



    Wednesday, September 16, 2020 9:17 PM
  • Do remember that making the changes to APIs described above will allow your project to compile in 64- bit but it doesn't necessarily mean each API will continue to function correctly. Suggest you download a copy of the Windows API Viewer for Excel utility to help get the conversions correct.

    Thursday, September 17, 2020 9:13 AM
  • >I have no control over the software on our network. I am a small cog in a large engine.

    Sure, that is fair. And most of us have often found ourselves in such a case. So, sure, not a new occurrence in our industry.

    However, that team and group of people getting paid to make these decisions does have a responsibility here - and that includes things like making a significant decision to upgrade office from x32 to x64 bits. Such planning and decisions should not occur in a vacuum. So, sure, not your plate or decision, or your fault. But someone is responsible here.

    >However, I have now found that any code that refers to DAO objects will not compile and when I select the DAO 3.6 library, it says 'Error in loading DLL'. E.g.

    Since Access 2007, you don't need, nor want to have a DAO reference (set in VBA editor - it not required anymore). If you using 2007 or later, and ALSO using the accDB format? Then DAO is now built into access, and is not required. 

    So, the code should compile and work as before. And that includes all DAO code - as that part is built into access.

    It is possible that you also have a ADO reference. I would remove it, and try to compile. 

    Now, as a general rule?

    Well,

    dim rstData   as recordset

    The problem with above? you don't know if that going to give you a ADO reocrdset, or a DAO one?

    So, as a habit I use:

    dim rstData as DAO.Recordset

    However, the above is little help for existing systems. So, I would consider removing the DAO reference, and removing the ADO reference. (if this is a accDB file).

    The BEST way to see the "min" required references? Create a blank new accDB database. Now ctrl-g to jump to debug window/VBA IDE. Now go tools->references. That blank database will give you the min required references. You see WHEN you do this, that no ADO and no reference to DAO is required or will appear. 

    However, you can (and will) use dim rst as recordset, and you find that even dim rst as DAO.recordset code will continue to compile, and continue to work. This is despite the fact that no DAO reference appears or is required. So, since 2007 (13 years now), a DAO reference is not required or even recommend when using accDB formats.

    The only real issue here is if ADO was/is being used. If you using both ADO and DAO? Well then which ever reference comes first will be what kind of recordset you get (assuming no prefix before recordset).

    So, you always want to try and compile the application BEFORE you attempt to run. This will catch bad code, or the very least ensure that you above code sample compiles.

    So, the ORDER of the references can thus matter - especially if ADO was used. But, if your remove the ADO reference (and DAO if accDB), then a compile will tell you instant if ADO was being used.

    If the application does not compile, then some errors and code can fool you, and even simple dim rst as recordset can break until such time you find/remove all compile errors.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Thursday, September 17, 2020 6:42 PM