Databound form fields not populating after DAO switch

[tylersontag]

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:

 [/FONT][/COLOR][COLOR=black][FONT=Verdana]Set dbOrgDB = DBEngine.Workspaces(0).Databases(0)
            Set snTbl = dbOrgDB.OpenRecordset(sTable, DB_OPEN_TABLE)
            ....
            snTbl.AddNew
            ...
            snTbl.Update[/FONT][/COLOR][COLOR=black][FONT=Verdana]

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[/FONT][/COLOR][COLOR=black][FONT=Verdana]

Do I need to "Commit" these executed statements to the local buffer or something?

 

[MarkK]

I find your post a bit confusing in that the code you posted shows no ADO nor any connection to SQL Server.

Do you mean that you changed a recordset update to an action query update using SQL?

 

[tylersontag]

Yes, that is what i meant, i suppose i didn't know how to phrase it properly.

 

[MarkK]

No, that's cool. Just making sure I understand.

A couple of things...
- The DBEngine.Workspace(0).Databases(0) reference is obsolete. Current versions of an Access.Application expose the CurrentDB method, which makes you code considerably tidier.
- It doesn't make sense to me that the change you describe should cause the error you indicate. It could be that the SQL changes, which I think of as more direct, might not be reflected yet in the recordsource of the form? Maybe you need to requery the form. I'd be more curious though to see the code for the procedure in which the error occurs, and the line where the error occurs.

 

[Banana]

Is this running on a unbound form?

 

[tylersontag]

Hmmm.... the actual change simply went from:

For every column (i) in table:
(ADO) tblsNewRow(i) = value
to...
(Exec) insertStatement += ". ' " + value + " ' "
next

So its essentially building the new rows the same way....

The error occurs on the code behind a form (Whereas these statements are run in a module) and its simply at CInt(textField)....

The call to open the form is made after all these execture statments, so theres not a concurrency problem. The form is bound to one of my linked tables... when the form is open theres a 'where' condition that could be leaving 0 rows behind if the executed statements weren't 'commited' yet... if thats something that's needed.

 

[tylersontag]

bump? Any ideas?

 

[LPurvis]

I received your bump merely because I'm subscribed to this thread. Watching it go by.
(I must have had a spare couple of hours at the time to actually subscribe to a spare thread :-s).

I'd say your previous post doesn't help to be honest.
You were asked to provide some actual code.
You've provided some anecdotal / metaphorical code.

For precise help, you're always better off giving the precide code.
Otherwise if you abstract you get, at best, abstract answers.

Cheers.

 

[tylersontag]

Figured it out, it was the form open 'whereas' condition... its always fun fixing someone else's code who doesn't comment things... Why populate fields based on all 10 values extracted from a file when you could simply use 9 of them and substitute a syntactically correct but totally different one for the last field?

So ya, the problem arose that the form would open with no record meeting the record condition and bad things followed. Thanks for the help everyone!