MS Access Handling Nulls In Query Formula
Hi all, I have this formula in Access: IIf([Init_Static_P] < 0 And (Nz([Adj_Static_P], -9999) < 0), "Resolved", "Not resolved" which keeps outputting an #Error when Adj_Static_P is null which will happen. I need the nulls in my dataset. Whenever Init_Static_P is < 0 and or Adj_Static_P is < 0, it works as it should outputting "Resolved" and vicer versa, however it does not work whenever there is any instance of Adj_Static_P as a blank value. Does anyone know how I can handle the nulls so that if the Init_Static_P is >0 and Adj_Static_P is null, then it would output "Unresolved", and if Init_Static_P is <0 and Adj_Static_P is null then it would output "Resolved" instead of #Error? Output should be as follows: Init_Static_P Adj_Static_P Result >0 Null "Not resolved" >0 >0 "Not resolved" <0 Null "Resolved" <0 < 0 "Resolved" Thanks in advance! <3
ADODB performance problem
Hi All, (Win10, Win11) To highlight a performance problem (with ADODB/ACCESS), on a heavy client, I wrote two small programs one in VB.NET (and VBA), the other in CPP/MFC. Using the same component (msado15.dll). This piece of code does nothing but connect to two ACCESS files (with a single table of 15,000 records), and via a recordset copy from one database to the other. Under VB.NET, it takes 1 second. Under CPP, on a machine that has the runtime access 2013 x64, it takes 9 seconds. (i spend 99% of my time in AddNew() )... Under CPP, on a machine that has the runtime access 2016 x64 OR Office365, it takes 20 minutes. (i spend 99% of my time in AddNew() )... The target computer must have Office365... I need an issue... PS : My two sln (exe/cpp/...) are available...There is no sensitive data... A short running test... |Nom de la fonction|Total \[unit, %\]|Self \[unit, %\]|Nombre d'appels|Module| |-|-|-|-|-||*+*D:\\users\\phili\\Downloads\\Test\_ADO\_Cpp\_VB\\test\_minimal\\x64\\Release\\test.exe \(PID : \)|115,73s \(100,00*%\)|0ns \(0,00*%\)|0|Plusieurs modules| |\|*+*\_\_report\_gsfailure|115,73s \(100,00*%\)|9,20?s \(0,00*%\)|1|test| |\|\|*+*\_\_scrt\_common\_main\_seh|115,73s \(100,00*%\)|13,20?s \(0,00*%\)|1|test| |\|\|\|*+*main|115,73s \(100,00*%\)|654,83ms \(0,57*%\)|1|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947bc40|104,82s \(90,58*%\)|104,82s \(90,58*%\)|6215|msado15| <= AddNew() !!! |\|\|\|\|*-*\[Native\] ucrtbase.dll!0x00007fff373ccad0|9,31s \(8,05*%\)|9,31s \(8,05*%\)|1|ucrtbase| |\|\|\|\|*-*ADODB::Connection15::Open|413,29ms \(0,36*%\)|8,80?s \(0,00*%\)|2|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947c010|101,83ms \(0,09*%\)|101,83ms \(0,09*%\)|2|msado15| |\|\|\|\|*-*printf|96,90ms \(0,08*%\)|157,30?s \(0,00*%\)|214|test| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947d3d0|91,69ms \(0,08*%\)|91,69ms \(0,08*%\)|74568|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947d380|50,46ms \(0,04*%\)|50,46ms \(0,04*%\)|74568|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe894d8fc0|30,92ms \(0,03*%\)|30,92ms \(0,03*%\)|149136|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947bfc0|28,68ms \(0,02*%\)|28,68ms \(0,02*%\)|6214|msado15| |\|\|\|\|*-*\[Native\] combase.dll!0x00007fff39403f10|26,94ms \(0,02*%\)|26,94ms \(0,02*%\)|5|combase| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe89513fc0|17,39ms \(0,02*%\)|17,39ms \(0,02*%\)|149137|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe894c1480|15,11ms \(0,01*%\)|15,11ms \(0,01*%\)|1|msado15| |\|\|\|\|*-*\[Native\] msado15.dll!0x00007ffe8947c230|14,46ms \(0,01*%\)|14,46ms \(0,01*%\)|149137|msado15| |\|\|\|\|*-Native\]msado15.dll!0x00007ffe8947d2b0|11,32ms\0,01*%\)|11,32ms\0,01*%\)|149136|msado15| Regards
Link a table from MS Fabric
Is it possible to link a table stored in MS Fabric Dataflow Gen2 to an Access database? The data set is roughly 500k rows of data. FYI I'm not trying to link Fabric FROM an Access database. Rather I would like to work with the data in MS Access. Any help would be appreciated.
Access 2013 32 bit runtime will not install
Okay. So, background, we use 2013 32 bit access runtime for our application. On a brand new, fresh Windows 10 64 bit pro install, for whatever reason this app will no longer install. It brings up MsiExec.exe / Kernelbase.dll errors in event viewer. Trying this on windows 11 i have zero issue. Installing this on Windows 10 64 bit ver. 1809 worked. I am hoping to see if anyone has some clues for me. I have tried installing in cmd/power Downloaded new windows 10 64 bit may 22h2 release iso tried different usb drives Updated PC bios Tried different hard drive (m.2 ssds) Different Ram (because shooting in the dark) Installing all updates / motherboard drivers Here are logs: Event Log-- Log Name: Application Source: Application Error Date: 6/19/2024 8:54:04 AM Event ID: 1000 Task Category: (100) Level: Error Keywords: Classic User: N/A Computer: DESKTOP-GO2OK8Q Description: Faulting application name: MsiExec.exe, version: 5.0.19041.3636, time stamp: 0x1c10077d Faulting module name: KERNELBASE.dll, version: 10.0.19041.4412, time stamp: 0x6d8c3da8 Exception code: 0xe06d7363 Fault offset: 0x00140b82 Faulting process id: 0x150c Faulting application start time: 0x01dac260e46114c8 Faulting application path: C:\Windows\syswow64\MsiExec.exe Faulting module path: C:\Windows\System32\KERNELBASE.dll Report Id: 8f7103e0-fa10-44bf-bd66-77a11ba8cb5e Faulting package full name: Faulting package-relative application ID: Event Xml: schemas.microsoft.com/win/2004/08/events/event"> 1000 0 2 100 0 0x80000000000000 193 Application DESKTOP-GO2OK8Q MsiExec.exe 5.0.19041.3636 1c10077d KERNELBASE.dll 10.0.19041.4412 6d8c3da8 e06d7363 00140b82 150c 01dac260e46114c8 C:\Windows\syswow64\MsiExec.exe C:\Windows\System32\KERNELBASE.dll 8f7103e0-fa10-44bf-bd66-77a11ba8cb5e System Info-- System Information report written at: 06/19/24 08:56:58 System Name: DESKTOP-GO2OK8Q [System Summary] Item Value OS Name Microsoft Windows 10 Pro Version 10.0.19045 Build 19045 Other OS Description Not Available OS Manufacturer Microsoft Corporation System Name DESKTOP-GO2OK8Q System Manufacturer ASUS System Model System Product Name System Type x64-based PC System SKU SKU Processor 13th Gen Intel(R) Core(TM) i5-13500, 2500 Mhz, 14 Core(s), 20 Logical Processor(s) BIOS Version/Date American Megatrends Inc. 1658, 5/22/2024 SMBIOS Version 3.5 Embedded Controller Version 255.255 BIOS Mode UEFI BaseBoard Manufacturer ASUSTeK COMPUTER INC. BaseBoard Product PRIME Z790-P WIFI D4 BaseBoard Version Rev 1.xx Platform Role Desktop Secure Boot State Off PCR7 Configuration Binding Not Possible Windows Directory C:\Windows System Directory C:\Windows\system32 Boot Device \Device\HarddiskVolume1 Locale United States Hardware Abstraction Layer Version = "10.0.19041.3636" User Name DESKTOP-GO2OK8Q\admin Time Zone Pacific Daylight Time Installed Physical Memory (RAM) 16.0 GB Total Physical Memory 15.7 GB Available Physical Memory 13.0 GB Total Virtual Memory 18.6 GB Available Virtual Memory 16.1 GB Page File Space 2.88 GB Page File C:\pagefile.sys Kernel DMA Protection Off Virtualization-based security Not enabled Device Encryption Support Reasons for failed automatic device encryption: TPM is not usable, PCR7 binding is not supported, Hardware Security Test Interface failed and device is not Modern Standby, Un-allowed DMA capable bus/device(s) detected, TPM is not usable Hyper-V - VM Monitor Mode Extensions Yes Hyper-V - Second Level Address Translation Extensions Yes Hyper-V - Virtualization Enabled in Firmware Yes Hyper-V - Data Execution Protection Yes [Hardware Resources] [Conflicts/Sharing] Resource Device Memory Address 0x85F00000-0x85F03FFF Standard NVM Express Controller Memory Address 0x85F00000-0x85F03FFF PCI Express Root PortAccess Europe meeting on Wed 6 Sept - Database Analyzer and other tools
The next Access Europe meeting will be onWednesday 6 Sept 2023starting at18:00 UK time (6PM UTC+1)and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central Europe and 10AM in Seattle / PST Please note that the UK is now on Summer Time (UTC+1). For local times, please checkhttps://www.worldtimebuddy.com/ In this month's session, I will be demonstrating my newDatabase Analyzer Proapplication. The free evaluation version is now available for download from the above link on my website If time permits, I will also be discussing a number of related Access examples and code samples such as: • the use of progress bars • creating a table of contents in Access reports • the undocumented SaveAsText/LoadFromText features • changing query views using command bars code For more details about this session, see:https://accessusergroups.org/europe/event/access-europe-2023-09-06/orhttps://isladogs.co.uk/aeu-19/ The meeting will again be held onZoom. When the time comes, you can connect using:Join Zoom Meeting. If you are asked, use:Meeting ID: 924 3129 5683 ; Passcode: 661210 For more connection options, please see theAccessUserGroups.orgweb page for this event All sessions are recorded and later uploaded to theAccess User Groupschannel onYouTube I hope you will join us for this month's session. As always, the session will be recorded and the video uploaded toYouTubeafter the event NOTE: I regret that the planned presentation on Better Access Charts by Thomas Moller originally scheduled for Wed 6 Sept has been postponed until further notice.
Query Issue
Originally I was attempting to perform Dsum to get query results; however, it was suggested I use something similar to the following... The SQL appears as... SELECT tblProj.CNoWBSE, tblWO.WOID, tblWO.WONo, Sum(tblEMES.EMESCst) AS SumOfEMESCst, Sum(tblELbr.ECraftCst) AS SumOfECraftCst, Sum(tblAMES.[Valin RepCurMES]) AS [SumOfValin RepCurMES], Sum(tblALbr.[Valin RepCurLbr]) AS [SumOfValin RepCurLbr] FROM ((((tblProj LEFT JOIN tblWO ON tblProj.ProjID = tblWO.WOID) LEFT JOIN tblEMES ON tblWO.WONo = tblEMES.WOOrder) LEFT JOIN tblAMES ON tblWO.WONo = tblAMES.WOOrder) LEFT JOIN tblELbr ON tblWO.WONo = tblELbr.WOOrder) LEFT JOIN tblALbr ON tblWO.WONo = tblALbr.WOOrder WHERE (((tblALbr.[Cost Element])<>890001)) OR (((tblALbr.[Cost Element])<>890002)) GROUP BY tblProj.CNoWBSE, tblWO.WOID, tblWO.WONo; The query results are not matching the information I am receiving from my Form/Subform information (which is correct). As shown, what I really need is to add the sum of the EMESCst and sum of ECraftCst to create EBudget and add the sum of the Valin repCurMES and sum of Valin repCurLbr to create AExpense. Then create a Var (expression) calculating the difference between EBudget and AExpense. Can someone assist with what I'm doing incorrectly?
