acotgreave
asked on
VBA Execute command not UPDATING record (no error reported)
Hi,
In Access VBA, I execute an update query, and execute it as follows:
Dim strSQL as String
Dim db as Database
Set db = CurrentDb
[...snip...]
strSQL = "UPDATE [...snip...]"
db.Execute strSQL
The update does not take place, but there is no error reported. The code has worked previously, but I have had to rebuild the backend database (another MDB) following a data corruption. I don't know if this is relevant.
The UPDATE string is valid: I created a new query, and pasted the code into the SQL view. It ran without a problem and updated the record correctly.
Any ideas?
Running:
Access 2003
Windows XP Sp2
Thanks,
Andy
In Access VBA, I execute an update query, and execute it as follows:
Dim strSQL as String
Dim db as Database
Set db = CurrentDb
[...snip...]
strSQL = "UPDATE [...snip...]"
db.Execute strSQL
The update does not take place, but there is no error reported. The code has worked previously, but I have had to rebuild the backend database (another MDB) following a data corruption. I don't know if this is relevant.
The UPDATE string is valid: I created a new query, and pasted the code into the SQL view. It ran without a problem and updated the record correctly.
Any ideas?
Running:
Access 2003
Windows XP Sp2
Thanks,
Andy
ASKER
Yes, I did a Debug.Print, copied the string from the Immediate Window straight into a query window. It runs fine from there. I snipped the UPDATE string to save space
Here's an example UPDATE string:
UPDATE LeagueResearch SET Paragraphs = 'ADMIN:
Phone interviewer: Cathy James
Interviewee: thyme
Interview date:
IT case study? -> -1
PwC case study? -> -1
Microsoft relationship: thyme
Pwc relationship: thyme
PRODUCTS AND SERVICES:
thyme
Platform:
thyme
Customers:
thyme
INTERNAL TECHNOLOGY:
Internal IT systems: thyme
Internal information management: thyme
Operating systems: thyme
R&D:
Using R&D tax credits:
thyme
HISTORY:
Year founded: 1992
thyme
SOURCES OF FINANCE:
Main funding sources:
Details of VC investment: thyme
OWNERSHIP:
Shareholders:
thyme
Woman owned? 0
Directors:
thyme
MISCELLANEOUS:
Reasons for growth:
thyme
Plans for international expansion:
thyme
Management incentives:
thyme
Staff incentives:
thyme
Staff retention challenges:
thyme
Future plans:
thyme' WHERE CompanyNumber = '02672530' AND TableName='Tech' AND TableYear=2006
Here's an example UPDATE string:
UPDATE LeagueResearch SET Paragraphs = 'ADMIN:
Phone interviewer: Cathy James
Interviewee: thyme
Interview date:
IT case study? -> -1
PwC case study? -> -1
Microsoft relationship: thyme
Pwc relationship: thyme
PRODUCTS AND SERVICES:
thyme
Platform:
thyme
Customers:
thyme
INTERNAL TECHNOLOGY:
Internal IT systems: thyme
Internal information management: thyme
Operating systems: thyme
R&D:
Using R&D tax credits:
thyme
HISTORY:
Year founded: 1992
thyme
SOURCES OF FINANCE:
Main funding sources:
Details of VC investment: thyme
OWNERSHIP:
Shareholders:
thyme
Woman owned? 0
Directors:
thyme
MISCELLANEOUS:
Reasons for growth:
thyme
Plans for international expansion:
thyme
Management incentives:
thyme
Staff incentives:
thyme
Staff retention challenges:
thyme
Future plans:
thyme' WHERE CompanyNumber = '02672530' AND TableName='Tech' AND TableYear=2006
just a thought, though probably wont make much difference
have u tried wrapping it in double quotes instead?
currentdb.execute "update table set paragraphs = " & chr$(34) & sVar & chr$(34) & " where ....
Im assuming u are using a variable to update it - or are you using a value from somewhere else, like another recordset
How have u defined the variable
have u tried wrapping it in double quotes instead?
currentdb.execute "update table set paragraphs = " & chr$(34) & sVar & chr$(34) & " where ....
Im assuming u are using a variable to update it - or are you using a value from somewhere else, like another recordset
How have u defined the variable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi rockiroads,
I tried wrapping it in quotes, but nothing changed!
I build up strSQL by taking values from a bunch of text boxes on a form. The text boxes are all bound to fields in the database, but I don't think that's relevant.
Andy
I tried wrapping it in quotes, but nothing changed!
I build up strSQL by taking values from a bunch of text boxes on a form. The text boxes are all bound to fields in the database, but I don't think that's relevant.
Andy
ASKER
Bingo!
I tried the DoCmd.RunSQL option, and it failed due to a locked record. Now I've seen the error, it's blindingly obvious, because my bound form dutifully locks the field that the UPDATE query is trying to update.
When I was testing the UPDATE string in a query window, I had always closed the form that had the lock, so I wasn't seeing the problem.
Why doesn't Access throw an error on .Execute...? (don't worry, that's a rhetorical question!)
Thanks for your help
Andy
I tried the DoCmd.RunSQL option, and it failed due to a locked record. Now I've seen the error, it's blindingly obvious, because my bound form dutifully locks the field that the UPDATE query is trying to update.
When I was testing the UPDATE string in a query window, I had always closed the form that had the lock, so I wasn't seeing the problem.
Why doesn't Access throw an error on .Execute...? (don't worry, that's a rhetorical question!)
Thanks for your help
Andy
But you've done a Debug.Print strSQL
And then copy and pasted that SQL into a query window - and it runs fine from there?