I recently migrated some backend data from a local access MDB to SQL Server. Because of the use of link tables, the existing ADO style updates were horribly slow.
So I swapped out the code for DAO and reduced the insert time from 11 seconds to less than one.
However, later in the execution, when the VBA code for one of the forms attempts to reference a field within the same form, the txtField.Value renders "You entered and expression that has no value"
In an "all things equal" branch that keeps the ADO there is no such problem.
the code changed from the form:
Code:
Set dbOrgDB = DBEngine.Workspaces(0).Databases(0)
Set snTbl = dbOrgDB.OpenRecordset(sTable, DB_OPEN_TABLE)
....
snTbl.AddNew
...
snTbl.Update
Code:
Set dbOrgDB = DBEngine.Workspaces(0).Databases(0)
Set snTbl = dbOrgDB.OpenRecordset(sTable, DB_OPEN_DYNASET) --only used to determine # of fields....
tempSQL = "INSERT INTO .............."
CurrentDb.Execute tempSQL
Do I need to "Commit" these executed statements to the local buffer or something?