Cannot resolve Error 3075

[kirkm]

Run-time error '3075':
Extra ) in query expression

DoCmd.RunSQL ("UPDATE tblmain4 SET [AComment] = " & Dat & " WHERE [Prefix] = '" & Serial & "'")

Dat is a String
Serial is a String

AComment is Long Text in the table
Prefix is Short text

What am I doing wrong ?

 

[Uncle Gizmo]

I don't think it should have the brackets around it.

 

[Galaxiom]

You have left off the single quote delimiters from around Dat.

DoCmd.RunSQL "UPDATE tblmain4 SET [AComment] = '" & Dat & "' WHERE [Prefix] = '" & Serial & "'", dbFailOnError

Always use the second argument so that an error is thrown if the command fails.

DoCmd.RunSQL doesn't need the parentheses. It runs as a Sub because there is no return required. Putting parentheses around the first argument just causes it to be passed ByVal which of course makes no difference in this case.

Edit: Actually I have stuffed up here big time by confusing CurrentDb.Execute and DoCmd.RunSql.

Been working in SQL Server too long so long.

 

[kirkm]

Aah.. got it, thanks very much.

 

[Uncle Gizmo]

Putting parentheses around the first argument just causes it to be passed ByVal which of course makes no difference in this case.

Useful to know, thank you Greg.

 

[kirkm]

Galaxiom, what is the action of 'dbFailOnError?

At run time I find Set Warnings False is needed, and just wondering if that will cause any issues?

 

[arnelgp]

dbfailonerror will give u an Error message when the Update cannot push through (for all records)
and the update will not continue entirely.
it's either it update all affected record or none.

 

[kirkm]

So that error will present even if Set Warnings = False

 

[Galaxiom]

So that error will present even if Set Warnings = False

Sorry this is my stuff up. I have failed to differentiate between RunSql and CurrentDb.Execute. dbFailOnError is a an argument of CurrentDb.Execute.

CurrentDB.Execute avoids the need to Set Warnings False. It doesn't raise the Warning about changing the data and is the benefit of CurrentDb.Execute rather than Application.DoCmd.RunSQL.

But CurrentDb is direct to the database engine, bypassing the Application scope so any values from Access objects have to be concatenated into the command string along with the appropriate delimiters which are not required for RunSQL because Access manages them for you.

Using DoCmd.Execute there is actually no point concatenating the values because Access will manage them.

You are on the threshold of getting it with concatenation. Time to move over to Execute.

 

[Pat Hartman]

It is best for debugging if you construct the SQL into a variable. That way, you can print the variable and hopefully see the syntax errors. Or, you can print the SQL to the debug window and copy it into the QBE and run it there where you might get a better error.

Regarding Set warnings OFF --- this is extremely dangerous because if you forget to turn them back on, you could easily loose development work if you close something without specifically saving the changes. Access will silently discard them. Sooooooooooo:

I create two macros in all my apps. SetWarningsOff and SetWarningsOn. In addition to the warnings command, the macro sets the hourglass ON when warnings are off and OFF when warnings are on. This gives you a visual clue that warnings are off so you can easily just run the macro to turn them back on.