Fink’s answer gets your main problem right, which is that your first loop is adding multiple references to the same instance of ‘clsMatch’ to your collection. I’ll just elaborate on why your fix works. In VBA, a line like: Dim c As New Collection doesn’t actually create a new collection. The ‘Dim’ statement is always … Read more
To the best of my knowledge the shortest path for a Windows Scheduled Task to “do something useful in Access VBA” is: Create a Public Function (not Sub) in the database. For example: Option Compare Database Option Explicit Public Function WriteToTable1() Dim cdb As DAO.Database Set cdb = CurrentDb cdb.Execute “INSERT INTO Table1 (textCol) VALUES … Read more
It is possible in Access to create a query to find the root of your given job. Don’t forget the power of VBA functions. You can create a recursive function in a VBA module and use its result as an output field in your query. Example: Public Function JobRoot(Id As Long, ParentId As Long) As … Read more
using the /passive switch you can install 64-bit ace drivers even if 32-bit ms office is present: http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/ Just be warned that installing the 2010 64-bit ACE engine on a machine with 2010 32-bit Office already installed CAN lead to some wacky behavior in your already existing Office 2010.
From Microsoft: This is due to today’s (Patch Tuesday) update to Office. The problem was introduced by a security fix, so it impacts all active versions of Access. We are working on a fix, and will deliver it as quickly as possible. The update has only been set to automatically update a very small percentage … Read more
Take a look at this very thorough list of reasons why recordsets are and aren’t updateable: When Recordsets Are Always Updateable A recordset is always updateable when: It is based on a single table. It is based on a query based on a single table. It is based on a query based on tables with … Read more
You need to use Option Explicit at the top of each VBA code module including forms and reports. You can set this for all future created modules and VBA code behind forms and reports by going into the VBA editor >> Tools >> Options >> Editor tab and ensuring Require Variable Declaration is checked. From … Read more
It’s mainly a matter of history, effectively ACE has superceded JET: Wikipedia answers your question in great detail. The most relevant sections are: With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine is fully … Read more
yes and no. You can’t do: insert into foo (c1, c2, c3) values (“v1a”, “v2a”, “v3a”), (“v1b”, “v2b”, “v3b”), (“v1c”, “v2c”, “v3c”) but you can do insert into foo (c1, c2, c3) select (v1, v2, v3) from bar What does that get you if you don’t already have the data in a table? Well, you … Read more
Since Jet 4, all queries that have a join to a SQL statement that summarizes data will be non-updatable. You aren’t using a JOIN, but the WHERE clause is exactly equivalent to a join, and thus, the Jet query optimizer treats it the same way it treats a join. I’m afraid you’re out of luck … Read more