Access Error on File Import through VBA
We are running a custom VBA routine in Microsoft Access that imports and parses text files into a database. Starting around September 9th, some machines began showing an error dialog during this process. The dialog mentions a failure in the VBA Regular Expression engine. The error does not occur consistently. On the same machine, the import may succeed several times and then fail (roughly 1 out of 4 attempts). The behavior appears to vary depending on the input file. The VBA code itself has been unchanged for years and continues to function correctly on older builds. We are wondering if this is related to a recent update in Access or the VBA runtime. Has anyone else encountered this behavior, and are there known workarounds or fixes? Going to leave version numbers out of this post because it keeps getting deleted and I'm not sure why. This is my 4th or 5th attempt to write this post and it keep getting auto-moderated.
What's the cleanest way to skip NULLs when looping through Recordest in VBA?
I've been working on a legacy Access app, and I find myself looping through a DAO recordest to process a bunch of fields. The thing is , some of those fields occasionally contain NULLs, and I've noticed they can easily break logic when you're not careful, especially when doing string concatenation, arithmetic, or conditional checks. I'm doing something like this : If Note IsNull(rs!MyField) Then ' Do something with rs!MyField End If Which works, but when you've got multiple fields or are writing more compact logic, it starts to get messy real fast. I'm wondering. Is there a cleaner or more elegant way to handle or skip NULLs ? PS ; I am having issues with the code editor
SSMA For Access not recognising DateTime/Extended fields
I have already posted up about this here: https://www.access-programmers.co.uk/forums/threads/ssma-bug-with-date-time-extended-fields.334442/ The current version of SSMA fails to recognise fields set to the DateTime/Extended when trying to migrate to Azure SQL It fails to match the data type and therefore fails to migrate any of the data from any table with any of that type of field. I'd appreciate it if this could be fed back to the SSMA team.
keep group footers together on one page
I have a report that has a fixed number of detail lines per grouping. There is one page per set of detail records. It needs to be grouped to three levels. I can see how to skip to a new page for each group. I can't see any way of making sure that group totals always appear together at the bottom of the required page and not on a page by themselves. Any ideas? Grouped A, B, C At the end of group C, include totals for group C at the foot of the page. At the end of group B, suppress the new page for group C and print the totals for groups B and C. At the end of group A, suppress the new page for groups B and C and print the totals for groups A, B and C. On the last page of the report, suppress the new page for groups A, B and C and print the totals for groups A, B and C along with the report totals. This is such an obvious requirement that I suspect the answer is, as ever, suck it, loser. Optimism rules! Well, usually the first replies are boilerplate botted responses using keywords that are entirely useless. "Here's how to create an Access report." "Here's how to create grouping in Access reports." "Here's how to throw a page after a group in Access reports." "Here's how to stop access reports ending with a blank page." It's only one downvote but it makes me feel better.
Access Northwind Dev Ed - Error GetRandomPkValue
Just downloaded the newest MS Access and selected a new Northwind Dev Ed as the first DB to create. It fails to open due to an error in the code as it searched for a non existing function GetRandomPkValue... The purpose of the missing function is to return a random primary key value by passing in the variables for a query name and a field name and then it should return a "random" numerical ID from those possible based upon the passed in variables... My question is, Is the new Northwind Dev designed to fail on purpose or did I manage to download a corrupted incomplete version or???MS Access Forum Web Design Irritation
🙃 Can we take a vote and get a show of hands for how many MS Access Forum users find the little social media bar on the side of the screen not only unnecessary but also an annoying waste of virtual real estate? 😉 Any chance the site admins could reconsider the location of that annoying little intrusion to possibly find someplace else to try and recruit new members to your OTHER social media outlets? 🤣 A wise old saying that so many members of the current human population seem to have forgotten or never learned is, "Just because you can do something, doesn't necessarily mean that you should." If more people understood those words I would not be here right now typing these words... 🤪 LMAO Have a wonderful day everybody!
Linking Access to Excel does not work
I have created a simple Access that is connected to a linked table to an Excel file as a data source. I can run the Access without any problems. It accesses the data and everything is fine. But when my colleague wants to run the Access, she gets the following error message "Microsoft Access “C:\Users\User\ [Source Path] .xlsx” is not a valid path. Please ensure that the path is entered correctly and that you are connected to the server where the file is located." We use OneDrive/SharePoint in our company and the colleague has access to the folder where the Excel and Access files are located. But it doesn't work. I think it's the path, as it seems to be local. But I don't understand why. This Access in turn serves as a source for another Excel (the target file). There it has a similar error message: ‘’Microsoft Excel [DataFormat.Error] “ [File Name] .accdb” is not a valid path. Please ensure that the path is entered correctly and that you are connected to the server where the file is located." I can open and run both the Access and the target Excel without any problems. Thanks a lot in advance. I appreciate your help a lot.
Access changing lettercase - redux
This has probably been beaten to death, but I'm fuming, so here goes. I want Microsoft to DO SOMETHING to help. Access has the highly annoying habit of changing lettercase on identifiers, seemingly at random. This is a real pain when using add-ins like MSAccessVCS, which makes it possible to use git for source code control. Here' what I've deduced is happening: There exists a dictionary/symbol-table containing every identifier used in a project. The symbol table contains ALL identifiers, including those defined by MSAccess, referenced libraries, and your code. Searching that table is case-insensitive, but it stores the canonical version of the identifier with case preserved. Normally, when you type an identifier that already exists in the table, the VBA editor "corrects" the lettercase of your entry to match the table's entry. The extremely annoying part is that sometimes that last step works in reverse. You type an existing identifier, but with a different lettercase. Instead of correcting what you typed to match the table, it instead updates the table with the version you typed, and decides that's the way future and existing identifiers should be spelled. Here's a recent example. I tried to create a class module constructor (Class_Initialize) but typed it "class_initialize" implicitly expecting that the VBA editor would "fix" it. Instead, it updated the symbol table and set the canonical lettercase to the all-lowercase version... and then proceeded to change the lettercase on the Class_Initialize() method in all my class modules. It has now decided the canonical lettercase is "class_initialize" and that's what it "corrects" to from now on. Of course, when I did the next MSAccessVCS export, git showed a bunch of unwanted (but cosmetic) changes. This pollutes the changeset and makes source control more difficult. THIS. IS. A. BUG. My workaround is, when starting a checkin, I go through the list of changes and group all such changes into one commit called "VBA Artifacts". But come on, this shouldn't be necessary. At least let us know WHY this happens, and give us some control of the process. A way to specify/correct the canonical casing would help.Msaccess Enhanced Message Box - Office update issue
This Code has been working for 10 years. Then, an Office update causes multiple instances of the form to remain open. If I revert to 16.0.17531.20120 all is well. This creates multiple instances: ' Create the MessageBox Dim F As New Form_frmEnhancedMessageBoxFormDialog This is supposed to close the form. ' cleanup Set F = Nothing Every time a message is displayed, a new instance of the form is opened, and after the user responds, it is hidden. The cleanup does not work. I tried unloading and closing the form but they are not part of the forms collection that can be referenced. I waited to see if a Microsoft update would fix the problem, but so far, no luck. Has anyone experienced the same issue? The code came from here: https://datenbank-projekt.de/projekte/improved-enhanced-message-box-ms-access However, a request for paid help was unanswered. After spending a morning with ChatGPT trying various fixes that did not work, I wondered if any other developers had any ideas or perhaps had experienced a similar issue. Thanks, Finlay McMillan '----------------------------------------------------------------------------- ' Plain Text Replacement for the standard MsgBox '----------------------------------------------------------------------------- Public Function Box(ByVal Prompt As String, _ Optional ByVal Buttons As VbMsgBoxStyle = vbOKOnly, _ Optional ByVal Title As String, _ Optional ByVal HelpFile As String, _ Optional ByVal HelpContextId As Long, _ Optional ByVal ButtonDelay As Long = -1, _ Optional ByVal AllowBeep As Variant, _ Optional ByVal AllowCopyToClipboard As Variant, _ Optional ByVal AllowSaveToFile As Variant, _ Optional ByVal LabelButton1 As Variant = Null, _ Optional ByVal LabelButton2 As Variant = Null, _ Optional ByVal LabelButton3 As Variant = Null, _ Optional ByVal NoStrEsc As Variant, _ Optional ByVal DismissID As Variant = Null, _ Optional ByVal AutoCloseSec As Long = 0, _ Optional ByVal DefaultButton As Long = 0, _ Optional ByVal BGColorButton1 As Long = -1, _ Optional ByVal BGColorButton2 As Long = -1, _ Optional ByVal BGColorButton3 As Long = -1, _ Optional ByVal BoxIsModal As Boolean = True, _ Optional ByVal BoxIsOnTop As Boolean = True, _ Optional ByVal DelayShow_Countdown As Boolean = False) _ As VbMsgBoxResultEx ' If the dialog was previously dismissed, don't display anything at all If (Buttons And 7) = 0 And GetDismissIDValue(DismissID) Then Box = VbMsgBoxResultEx.vbOK Exit Function End If ' Create the MessageBox Dim F As New Form_frmEnhancedMessageBoxFormDialog F.ParenthWnd = GetParentWindowHandle() F.Title = Title F.Buttons = Buttons F.HelpFile = HelpFile F.HelpContextId = HelpContextId F.ButtonDelay = IIf(ButtonDelay <= 0, DefaultButtonDelay, ButtonDelay) F.AllowBeep = IIf(IsMissing(AllowBeep), DefaultBeepAllowed, AllowBeep) F.AllowCopyToClipboard = IIf(IsMissing(AllowCopyToClipboard), DefaultCopyToClipboardAllowed, AllowCopyToClipboard) F.AllowSaveToFile = IIf(IsMissing(AllowSaveToFile), DefaultSaveToFileAllowed, AllowSaveToFile) F.SavedTextFileFolder = DefaultSavedTextFileFolder F.IsRichText = False F.LabelButton1 = LabelButton1 F.LabelButton2 = LabelButton2 F.LabelButton3 = LabelButton3 F.DismissID = DismissID F.AutoCloseSec = IIf(IsMissing(AutoCloseSec), DefaultAutoCloseSecDelay, AutoCloseSec) F.DefaultButton = IIf(IsMissing(DefaultButton), DefaultDefaultButton, DefaultButton) F.BGColorButton1 = IIf(BGColorButton1 < 0, DefaultBGColorButton1, BGColorButton1) F.BGColorButton2 = IIf(BGColorButton2 < 0, DefaultBGColorButton2, BGColorButton2) F.BGColorButton3 = IIf(BGColorButton3 < 0, DefaultBGColorButton3, BGColorButton3) F.BoxIsModal = IIf(IsMissing(BoxIsModal), DefaultBoxIsModal, BoxIsModal) F.BoxIsOnTop = IIf(IsMissing(BoxIsOnTop), DefaultBoxIsOnTop, BoxIsOnTop) F.DelayShow_Countdown = IIf(IsMissing(DelayShow_Countdown), DEFAULT_DELAYSHOW_COUNTDOWN, DelayShow_Countdown) If IIf(IsMissing(NoStrEsc), DefaultNoStrEsc, NoStrEsc) Then F.Prompt = Prompt Else F.Prompt = UnEscStr(Prompt, toHtml:=False) End If ' Make it visible and wait for the user until we get the result Box = F.ShowModal() ' Keep the last result just in case the user needs it again later m_Result = Box ' Set focus back to the parent form On Error Resume Next WinAPISetFocus F.ParenthWnd On Error GoTo 0 ' cleanup Set F = Nothing