Cannot resolve Error 3075 (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 06:22
Joined
Oct 30, 2008
Messages
1,257
Run-time error '3075':
Extra ) in query expression

Code:
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 ?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:22
Joined
Jan 20, 2009
Messages
12,849
You have left off the single quote delimiters from around Dat.

Code:
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.
 
Last edited:

kirkm

Registered User.
Local time
Tomorrow, 06:22
Joined
Oct 30, 2008
Messages
1,257
Aah.. got it, thanks very much.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:22
Joined
Jul 9, 2003
Messages
16,244
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

Registered User.
Local time
Tomorrow, 06:22
Joined
Oct 30, 2008
Messages
1,257
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:22
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Tomorrow, 06:22
Joined
Oct 30, 2008
Messages
1,257
So that error will present even if Set Warnings = False
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:22
Joined
Jan 20, 2009
Messages
12,849
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:22
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom