DoCmd.RunSQL will not run 3

[rccline]

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

Public Sub UpdateFullPath()

Dim SQL As String

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

End Sub

 

[Andrzejek]

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.

 

[rccline]

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

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

 

[rccline]

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

 

[dhookom]

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

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

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

 

[rccline]

This source of this code come straight out of Microsoft's Dev Center.

https://msdn.microsoft.com/en-us/VBA/Access-VBA/articles/docmd-runsql-method-access

 

[rccline]

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.

 

[dhookom]

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

 

[rccline]

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

 

[Andrzejek]

As a side note, I would recommend this:

Public Sub UpdateFullPath()
Dim SQL As String

SQL = "UPDATE tblRunsheet SET ...
[blue]Debug.Print SQL[/blue]
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.

 

[rccline]

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, ...

 

[Andrzejek]

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

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.

 

[dhookom]

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:

SQL = [highlight #FCE94F][Path][/highlight] & "\" & [highlight #FCE94F][vol][/highlight] & "-" & [highlight #FCE94F][Pg][/highlight]

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

 

[MajP]

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.

 

[Andrzejek]

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 [tt]FullPath[/tt]) 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)
I rather put a break point and do in the Immediate Window:[tt]
? SQL[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

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

dim strSql as string
strSql = ......
debug.print strSql

Take a look to ensure it is good and then do the rest of my code.