Rabu, 21 Desember 2016

Re: [MS_AccessPros] Run Sql statement in vba question

 

Jim,


You could use the SQL as the Row Source of a list box or as the record source of a form or subform. Run SQL is not what you need.


Duane Hookom
Back in good ole Minnesota (briefly)

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, December 21, 2016 2:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Run Sql statement in vba question
 


the context was to get the list of queries from clicking a button. Because I was getting a list of objects, I did not think creating another query was a good way to keep the database clean or to see a query name of a query  to list the queries


Jim Wagner


On Wednesday, December 21, 2016 1:01 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
I agree with Darrell since the SQL isn't an action query. If there was some context provided regarding the intention of the query we could help. 

Duane Hookom
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, December 21, 2016 12:55:51 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Run Sql statement in vba question
 


I don't feel comfortable using sql as a variable.  It seems like it would be a reserved word.
 
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, December 21, 2016 11:43 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Run Sql statement in vba question
 



John,
DoCmd.RunSQL is for action queries and DDL.
I'm not sure what he is trying to do with the returned rows but I believe he needs to use OpenRecordset.
 
Darrell
 
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, December 21, 2016 1:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Run Sql statement in vba question
 
 
Jim-
 
It's a mystery.  I get the same error when I try your code in Northwind 2007.
 
Sub TestSQL()
Dim SQL As String
     
    SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>""~"") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
    Debug.Print SQL
    
    DoCmd.RunSQL SQL
End Sub
 
But if I copy and paste the SQL resulting from the Debug into the SQL view of a new query, it works!
 
This works:
 
Sub TestSQL()
Dim SQL As String
Dim db As DAO.Database, qd As DAO.QueryDef
     
    SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>""~"") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
    Set db = CurrentDb
    Set qd = db.CreateQueryDef("zTestQuery", SQL)
    DoCmd.OpenQuery "zTestQuery"
    CurrentDb.QueryDefs.Delete "zTestQuery"
 
End Sub
 
 
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)
 
 
 
On Dec 21, 2016, at 7:16 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
 
 
John,
 
I now get an error of 
A runsql action requires an argument consisting of an sql statement
 
 
Dim SQL As String
     
    SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>""~"") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
 
    DoCmd.RunSQL SQL
 
 
Jim Wagner
 
On Wednesday, December 21, 2016 11:06 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
 
Jim-
 
Did you do this?
 
SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>""~"") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
 
 
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)
 
 
 
On Dec 21, 2016, at 6:54 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
 
 
I tried  both quotes and I am getting an error with 
 
single quotes and double double quotes of Invalid sql statement expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
 
 
 
 
Jim Wagner
 
On Wednesday, December 21, 2016 10:49 AM, "'Embrey, Darrell' DEmbrey@bcbsm.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
 
Jim
 
It isn't the tilde, it' the quotation marks inside the string. In order to use quotation marks inside the string, you must double them like so: ""~"" Or you could use the apostrophe '~'.
 
What is happening is the SQL engine thinks the quotation mark before the tilde is terminating the string.
 
 
Darrell
 
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] 
Sent: Wednesday, December 21, 2016 12:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Run Sql statment in vba question
 
  
Hello all,
 
I am trying to run a sql statement below and I am getting a syntax error on the "~" part of the statement. Is there are problem with "~" in vba?
 
Dim QSQL As String
 
SQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;"
 
DoCmd.RunSQL QSQL
 
Thank You for your help.
 
Jim Wagner
 
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
 
 
 
 
 
 
 



 
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.


This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.






__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (17)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)