Results 1 to 3 of 3
  1. #1
    HerrSonntag is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Posts
    2

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

    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?

  2. #2
    HerrSonntag is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Sep 2009
    Posts
    2
    AND resolved, logic error :-D

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Do you want to go ahead and mark this thread Solved with the thread tool?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Controls go blank on empty query result
    By kevdfisch in forum Programming
    Replies: 4
    Last Post: 08-25-2009, 08:07 AM
  2. Option Group broken out
    By dcecil in forum Reports
    Replies: 3
    Last Post: 04-21-2009, 10:30 AM
  3. Add new Recordset using Recorset by Query
    By gailoni in forum Programming
    Replies: 1
    Last Post: 10-22-2008, 11:52 PM
  4. Result of Count Query not known elsewhere
    By Carole in forum Access
    Replies: 1
    Last Post: 09-07-2008, 09:39 AM
  5. Replies: 2
    Last Post: 08-04-2008, 04:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums