It can be useful at times to determine the bitness (32, 64, who knows what will be next!) of the installed Operating System (OS). This can be useful for your error reporting for instance.
Below are a couple approaches to that allow us to determine, with relative ease, whether we are dealing with a 64-bit system or not. In most cases, this is what is of importance to us.
I thought I’d cover the subject of creating/adding/inserting new records into a table using VBA.
As per pretty much anything with Access, there are a number of way to do this and I thought I’d cover three in this post
Just a quick post. Have you ever needed to determine the bitness (x32 vs. x64) of Microsoft Office?
Yes, you can go File -> Account -> About Access and get the information from the pop-up dialog, but what if you want to determine this in your code, using a VBA function?
I started digging, thinking I could easily extract such information from the registry, boy was I wrong there. With all the variations of Office (MSI, CTR, …, x32, x64, …) it’s a nightmare to navigate! What a mess Microsoft has made for developers to have a hope in hell to keep all the registry keys and variations straight. Anyways, I ended up going back to basic, and the answer was Conditional Compilation Directives. With a simple Conditional Compilation Directives I had my answer.
'Determine if the current application, thus Office, is running in x32 or x64 bitness.
Function Office_Isx64() As Boolean
#If Win64 Then
Office_Isx64 = True
' #Else
' Office_Isx64 = False
#End If
End Function
Nothing more to it than that.
Based on Andreas Harke’s comment, you can also determine the bitness of an application by reading the 21st character of the application GUID. So you can do something like:
Function IsOffice32Bit() As Boolean
'https://docs.microsoft.com/en-us/office/troubleshoot/office-suite-issues/numbering-scheme-for-product-guid
' p => 0=32-bit, 1=64-bit
IsOffice32Bit = (Mid(Application.ProductCode, 21, 1) = 0)
End Function
I just finished a call from my father who has a Windows 10 PC with Office 2010 installed. All of a sudden he became unable to use Office and didn’t understand why, so he called me. Much to my amazement, and his, Microsoft had pushed upon him Office 365 and now, by default, was using Office 365 which would then prompt for an account login.
Now, I initially thought I’d reset the default application associated with each file type, but the interface in Windows 10 is horrific! and with the number of file types to modify I’d be at it until the New Year, so I decided it would simply be easier to uninstall Office365 altogether especially since we never installed it in the first place.
Well, that’s where the real problems occurred. After removing Office365, I found Office 2010 no longer worked, all my previous file associations were not restore and I had a real mess now thanks to some great uninstalling processes from Microsoft!
I have since repaired Office, recreated file associations, …, but what a mess and a couple hours I’ll never get back, little alone the hours of frustration that my father has had thinking it was his mistakes… the last thing any retiree needs!
Microsoft should be ashamed of themselves! Pushing products in updates, overriding setting and not restoring them properly and of course they never have to deal with any of this! The home user is supposed to figure all of this out on their own.
So how many hundreds, thousands, or even million users have found themselves in this state thanks to Microsoft’s aggressive approach of shoving Office365 down their throats?!
The next question now is how can I block this from occurring again?! and to that I still have no answer.
I’m starting to believe this may be related to what is described in the How-To Geek article Hey Microsoft, Stop Installing Apps On My PC Without Asking because I also see all sorts of extra 3rd-party CrapWare having been installed without any form of user consent (I know MS will say they have consent from the EULA that gave them every right in the world!). Once again, thank you Microsoft for the CrapWare and respect you show towards your users.
Yep, another update, so another bug has surfaced, this time with sending e-mails with database objects in which Access reports
Microsoft Access can’t open the mail session.
and this appears to occur in 1807, although the official Microsoft page states 1806 and later, running on Windows 7 PCs, although I have seen reports of people claiming to have the issue on Win10.
Continue reading
Have you ever needed to scrable, mix up, shuffle a string? Well I did for a project and surprisingly couldn’t quickly find anything when I Googled the subject so below is a very simple function that I devised to do exactly that. The function simply takes an input string, reorders the characters and outputs the resulting string. So the output is of the same length and contains the exact same characters, just in a different order.
Better late to the party …
Well, it would seem Microsoft has realized we are now using large displays, high DPI monitors and their Ribbon was a huge waste of space. Only took them 10+ years! So they are finally looking to give us back a leaner/meaner Toolbar Style Ribbon.
I think at this point in time, after more than 3 years, one has to seriously question continuing to use Access back-ends at all! Maybe even question using Access at all considering the support we are seeing from Microsoft.
Recently, the past few weeks, we have been seeing numerous reports of:
and typically these are longstanding databases, that suddenly demonstrate such problems.
Are you running Access 2016 Runtime and getting the error
The database you are trying to open requires a newer version of Microsoft Access.
when you try to run your database. Well, this may be caused by the use of the ‘Large Number’ (BigInt) field data type.
Sadly, Runtime does not support ‘Large Number’ . Yes, you heard right. So although Microsoft added it to the full version, it is not supported by runtime.
I was helping a user in a French forum who wanted to retrieve an Excel WorkBook’s cell/range value and thought that I’d share the code for anyone else looking to do this.
'---------------------------------------------------------------------------------------
' Procedure : Excel_GetRangeVal
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve an Excel Range's cell value
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Fully qualified path & filename of the Excel Workboon to get the value from
' sSht : Worksheet name to retrieve the value from
' sRangeAdd : Range, B21 (for instance), you wish the get the value of
'
' Usage:
' ~~~~~~
' Excel_GetRangeVal("C:\Users\Monkey\Banana.xlsx", "5 Little Monkeys", "C12")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-06-01 Initial Release -> Forum Help
'---------------------------------------------------------------------------------------
Function Excel_GetRangeVal(ByVal sFile As String, _
ByVal sSht As String, _
ByVal sRangeAdd As String) As Variant
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim oExcelWrSht As Object
Dim bExcelOpened As Boolean
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
Err.Clear
Set oExcel = CreateObject("Excel.Application")
Else
bExcelOpened = True 'GetObject worked -> Excel was already running
End If
On Error GoTo Error_Handler
oExcel.Visible = False 'Keep Excel hidden until we are done with our manipulation
oExcel.ScreenUpdating = False
Set oExcelWrkBk = oExcel.Workbooks.Open(sFile) 'Open the Workbook
Set oExcelWrSht = oExcelWrkBk.Sheets(sSht) 'Get the right WorkSheet to work with
Excel_GetRangeVal = oExcelWrSht.Range(sRangeAdd).Value 'Get the Range value we are interested in
oExcelWrkBk.Close False 'Close the WorkBook without saving now that we're done.
If bExcelOpened = False Then 'Close excel if is wasn't originally running
oExcel.Quit
End If
Error_Handler_Exit:
On Error Resume Next
If Not oExcelWrSht Is Nothing Then Set oExcelWrSht = Nothing
If Not oExcelWrkBk Is Nothing Then Set oExcelWrkBk = Nothing
If Not oExcel Is Nothing Then
oExcel.ScreenUpdating = True
oExcel.Visible = True 'Make excel visible to the user
Set oExcel = Nothing
End If
Exit Function
Error_Handler:
'9 -> can't find the worksheet
'1004 -> can't find the file
If Err.Number = 9 Then
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Excel_GetRangeVal" & vbCrLf & _
"Error Description: Unable to locate the specified WorkSheet '" & sSht & "'" & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
GoTo Error_Handler_Exit
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Excel_GetRangeVal" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End If
End Function
If you need to retrieve more than one cell/range then, instead of using this code multiple times, you’d be best to build an array of the values within a single call and return an array (or collections, …) so you only make one call to Excel to open the file.