Swapped DAO recordset update with SQL Action Query. Result? Broken.

HerrSonntag · #1 10-01-2009, 06:19 AM

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?

HerrSonntag · #2 10-01-2009, 12:21 PM

AND resolved, logic error :-D

RuralGuy · #3 10-01-2009, 01:39 PM

Great! Do you want to go ahead and mark this thread Solved with the thread tool?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Controls go blank on empty query result	kevdfisch	Programming	4	08-25-2009 06:07 AM
Option Group broken out	dcecil	Reports	3	04-21-2009 08:30 AM
Add new Recordset using Recorset by Query	gailoni	Programming	1	10-22-2008 09:52 PM
Result of Count Query not known elsewhere	Carole	Access	1	09-07-2008 07:39 AM
criteria to limit # of characters in a query result.	valdense	Queries	2	08-04-2008 02:16 PM