Running an up-to-date Windows 10 VM with the very latest version of MS Access (O365) and MSFT 64-bit ODBC driver for Access. I'm trying to use EasyMorph to load 11 different tables, each into a corresponding Postgresql table. Actually, I'm wanting to do this multiple times - we have a client with a number of identically-structured MS Access databases (1 for each of their customers) that we need to push to the consolidated POstgresql tables which we will then profile and map for loading into a new hosted application. These are old mdb access databases, not accdb databases.
So I created an EasyMorph project consolidate_mdb.morph that opens the input WIP.mdb, queries a table to identify which customer it is for, deletes any existing data for that customer from each of the 11 target tables, and then reloads each of those 11 target tables with the data from the corresponding Access mdb table. I then created a "wrapper" project that, for each mdb file placed in a "files to process" folder, copies that mdb to a "processed files" (to keep a record), renames the file to WIP.mdb, invokes the first consolidate_mdb.morph, and then finally deletes the WIP.mdb.
This all worked pretty well while the consolidate_mdb.morph was only consolidating 3 tables and I was only processing 4 mdb files at a time. Occasionally EasyMorph was reporting a "Too many client tasks" error that supposedly originates from either the ODBC driver or MS Access, but I thought maybe this was because I was causing other errors while developing... When it happens, closing EasyMorph and reopening it appears to be necessary in order to free up these open connections.
Sadly, after I added the rest of the 11 tables to the consolidate_mdb.morph project, I can no longer run that project more than once without getting the "Too many client tasks" error. Even if I process the same database twice in a row, the second time it fails about half way through. So it seems like it hits a limit of approx. 18 or 20 MS Access actions before it runs out of connections, and I have to close and reopen EasyMorph.
I've tried everything I can think of to work around this - the latest of which was to add Synchronize actions to make sure none of the 11 tables were processing in parallel (thinking maybe the issue was too many open concurrently) but that did not help at all. I've also tried every potentially-relevant setting on the ODBC connection that I thought could possibly make a difference, to no avail. I even tried downloading and installing a devart Access ODBC driver but that didn't work at all (the "Test" function in the ODBC connection setup fails).
Before adding the Synchronize actions I enabled the ODBC trace - I'm not sure exactly what I should be looking for, but it did show SQLFreeHandle eventually being invoked for each time SQLAllocHandle was being invoked - that's what got me thinking about the concurrent connections... I haven't rerun with trace since adding the Sync actions.
The conclusion I've come to is that EasyMorph is not doing something that the ODBC driver and/or Access expects/needs in order to free up some kind of resource. Repeated testing gives me every reason to believe that those resources are being freed up any time EasyMorph closes, whether it closes gracefully, dies unexpectedly (this has happened 2 or 3 times) or is killed via Task Manager (I had to do this once after it hung). Whether or not I do restarts of the Windows 10 VM in between tests makes no noticeable difference. Similarly, whether I have any of the databases involved open in MS Access - or any other MS Access databases open - seems to make no difference to the behaviour (most of my testing was with no Access instances running). So I'm thinking that in order to make this use case work for us - and more importantly be able to scale to even larger ones - I need some way of better controlling when EasyMorph releases some of these resources.
Any assistance at resolving this would be massively appreciated!
Richard