Link to home
Start Free TrialLog in
Avatar of acotgreave
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
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

I think the SQL itself is likely the most important part to see.

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?
Avatar of acotgreave
acotgreave

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
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
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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