×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

DoCmd.RunSQL will not run
3

DoCmd.RunSQL will not run

DoCmd.RunSQL will not run

(OP)
I am trying to create a simple SQL command in a module.

The SQL string passes to the SQL following the DoCmd.RunSQL, but my code stops at the line DoCmd.RunSQL SQL

I get no error... It just doesn't run. What could be wrong?

Thank you.

Robert


CODE --> code

Public Sub UpdateFullPath()

Dim SQL As String

SQL = "SELECT * FROM tblRunsheet;"
    DoCmd.RunSQL SQL

End Sub 

RE: DoCmd.RunSQL will not run

What do you expect to happen?
Where do you expect to see the results of your query?


---- Andy

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

(OP)
The code I was originally trying to run was an UPDATE query, but my code stops on the SQL= statement.

I don't see what is wrong with either this statement or the SELECT statement above.

Any help would be appreciated.

Thank you.

Robert

CODE --> code

Public Sub UpdateFullPath()

Dim SQL As String

SQL = "UPDATE tblRunsheet SET tblRunsheet.FullPath = [Path] & " \ " & [vol] & " - " & [Pg] WHERE (((tblRunsheet.FullPath) Is Null));"
   
DoCmd.RunSQL SQL
    
End Sub 

RE: DoCmd.RunSQL will not run

(OP)
Thank you for replying Andrzejek.

I am expecting the code to run an update to the field "FullPath" in my table tblRunsheet, i.e.
to run an update on tblRunsheet.FullPath where FullPath field is null.

It works from an update query created in design view. That's where I got the SQL statement from.

My code stops on the SQL= line. So, I tried running a simple SELECT * FROM tblRunsheet. That didn't work either.


Robert

RE: DoCmd.RunSQL will not run

I would try this since you need to adjust all of your quotes from a standard SQL statement when run in VBA:

CODE --> vba

SQL = "UPDATE tblRunsheet SET FullPath = [Path] & ' \ ' & [vol] & ' - ' & [Pg] WHERE FullPath Is Null" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DoCmd.RunSQL will not run

(OP)
Thank you dhookom!

That did something. When it first runs, it opens as a parameter query. Even in I put something in as a parameter, I get the error message that the field is not updatable.


I can update from the query created in the query grid.

RE: DoCmd.RunSQL will not run

The source you mentioned states it's for "action queries" and also uses the single quotes like I provided. The article could have been more explicit regarding the quotes since the mistake is very common.

What is the parameter prompt?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DoCmd.RunSQL will not run

(OP)
I had an typo in the name of the field. Adding the correct quotes got the code to run.
Another "DUH" for me! Kudos to you!

THANK YOU again for coming to my rescue. This path to coding is indeed a challenge for the challenged. :)

Robert

RE: DoCmd.RunSQL will not run

As a side note, I would recommend this:

CODE

Public Sub UpdateFullPath()
Dim SQL As String

SQL = "UPDATE tblRunsheet SET ...
Debug.Print SQL
DoCmd.RunSQL SQL
    
End Sub 

This way you can actually see the SQL that you are about to execute, and determine if it is correct and makes sense.


---- Andy

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

(OP)
And yes... it wanted the code as an UPDATE statement. I didn't get that message until I got the Quotes right. Only then did it the error message tell me it needed an action. It named, UPDATE, DELETE, ...

RE: DoCmd.RunSQL will not run

"got the Quotes right" - you may try this 'trick' smile :

CODE

Public Sub UpdateFullPath()
Dim SQL As String

SQL = [Path] & "\" & [vol] & "-" & [Pg]
  Debug.Print "The Path is " & SQL
SQL = "UPDATE tblRunsheet SET FullPath = '" & SQL & "' Where FullPath Is Null"
  Debug.Print SQL
DoCmd.RunSQL SQL
    
End Sub 


---- Andy

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

Thanks Andy for the additional tips. I have be creating database apps for decades and still include "debug.print" in my code to find my frequent errors.

I believe Path, Vol and Pg are fields in the table so this statement will probably result in an error:

CODE --> vba

SQL = [Path] & "\" & [vol] & "-" & [Pg] 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: DoCmd.RunSQL will not run

only action queries can be run by the docmd.runsql or the currentDb.execute stringSql. If you want to open a select query you need to use docmd.openquery method. If doing a select query dynamically then you would have to first create the querydef to open a select query.

RE: DoCmd.RunSQL will not run

Duane,
You are right - if "Path, Vol and Pg are fields in the table so this statement will probably result in an error:"

But, by the same token, I would not keep the calculated field (like FullPath) in my table.
I would create a query where I would concatenate Path, Vol and Pg and show FullPath in this query.

As for Debug.Print - too much work (code) smile
I rather put a break point and do in the Immediate Window:
? SQL


---- Andy

There is a great need for a sarcasm font.

RE: DoCmd.RunSQL will not run

Quote:

still include "debug.print" in my code to find my frequent errors.
With you on that. Even now, after a writing a bazillion lines of Access code, I still do

CODE -->

dim strSql as string
strSql = ......
debug.print strSql 
Take a look to ensure it is good and then do the rest of my code.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close