• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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
0
acotgreave
Asked:
acotgreave
  • 3
  • 2
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
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?
0
 
acotgreaveAuthor Commented:
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
0
 
rockiroadsCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Leigh PurvisDatabase DeveloperCommented:
Okay..

And in code you just build that up with several lines of concatenation...
strSQL = strSQL & " ..."
And it clearly comes out fine.

I too wouldn't expect the double quotes to make any difference.
Simply because a) it just shouldn't ;-) as there are no single quotes in the text and b) the query runs fine from a query window.

You could play with

Docmd.RunSQL strSQL
or
CurrentProject.Connection.Execute strSQL

(Naturally a compact and repair is worthwhile - to make sure there's no problem with the memo fields... possible - but again they're working from a query... odd!  But I'm sure you've done that plenty already)

0
 
acotgreaveAuthor Commented:
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
0
 
acotgreaveAuthor Commented:
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now