473,839 Members | 1,411 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to programmatical read the current sql statement if cuurent query ins't saved?

Hi All,
I'm new to this group and quite new to access/vba.
So, shortly after beginning to write a simple application for my wife,
I came across a blocking problem: I need to intercept the sql statement
that stay behind a current, but not yet saved query.
When I work on saved queries I use:

strCurrentName = CurrentObjectNa me
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
sqlCurrent = dbsCurrent.Quer yDefs(strCurren tName).SQL

But I need the same info when I work on query already designed but not
yet saved.
Is there a simple way to achive this?

Thank you for your time and efforts...
Ciao
Stefano

Feb 7 '06 #1
6 2249

sghi wrote:
Hi All,
I'm new to this group and quite new to access/vba.
So, shortly after beginning to write a simple application for my wife,
I came across a blocking problem: I need to intercept the sql statement
that stay behind a current, but not yet saved query.
When I work on saved queries I use:

strCurrentName = CurrentObjectNa me
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
sqlCurrent = dbsCurrent.Quer yDefs(strCurren tName).SQL

But I need the same info when I work on query already designed but not
yet saved.
Is there a simple way to achive this?


As far as I know, if you're working with an Access back-end, there are
only two ways to "design" a query - i.e. a current, but not yet saved
query.

In the query designer window, in which case you can drop down the "SQL
view" from the "View" button.

In code, writing in-line SQL. In which case, you can intercept it with
a breakpoint and examine it in the Immediate pane.

HTH

Edward

Feb 7 '06 #2
thank you Teddysn...,
but my problem is that i'd like to read in a string in a vba module the
sql statement that you can read in the "..."SQL view" from the "View"
button."

Ciao
Stefano

Feb 7 '06 #3

"sghi" <st************ **@getronics.co m> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
thank you Teddysn...,
but my problem is that i'd like to read in a string in a vba module the
sql statement that you can read in the "..."SQL view" from the "View"
button."


Help me understand why it is too much trouble to save the query.

Objects being designed but not yet saved are not as accessible as you might
like, but apparently The Boys And Girls In Redmond did not intend them to
be - they have had quite a number of iterations of Access to make them so
and haven't done it.

Larry Linson
Microsoft Access MVP
Feb 7 '06 #4

Sorry, I can't help you solve your problem but I still wonder what the
underlying reason for wanting to do this might be?

Stefano wrote:
I need to intercept the sql statement that stay behind
a current, but not yet saved query.


*** Sent via Developersdex http://www.developersdex.com ***
Feb 7 '06 #5
I can't for the life of me understand why you would need to do this but have
a look at my SQLComment project. It demonstrates how to read the contents of
the SQL View window.
http://www.lebans.com/addsqlcomments.htm
NEW - May 16/2004 A2KAddSQLCommen ts.zip is an MDB containing functions to:

1) To allow the saving of Comments in the SQL View window.
2) To allow the saving/restoration of Comments in the SQL View window.

Here is the A97 version:A97AddS QLComments.zip

History

Version 1.3 May 16, 2004

As per a suggestion from Dimitri Furman, modified parsing function to allow
for multiple instances of the Access SQL string delimiter character(";")

Version 1.1 May 16, 2004

First release!

Here is the source code:

Option Compare Database
Option Explicit

'DEVELOPED AND TESTED UNDER MICROSOFT ACCESS 97, 2K, and 2K2 VBA
'
'Copyright: Stephen Lebans - Lebans Holdings 1999 Inc.
' Please feel free to use this code within your own projects,
' both private and commercial, with no obligation.
' You may not resell this code by itself or as part of a collection.
'
'
'Name: Add/Save/Modify Comments for the SQL View window
'
'Version: 1.1
'
'Purpose: 1) To allow the saving of Comments in the SQL View window.
' 2) To allow the saving/restoration of Comments in the SQL View window.
'
'Requires: The table named "SQL-Comments" included with this sample MDB
'
'Author: Stephen Lebans
'
'Email: St*****@lebans. com
'
'Web Site: www.lebans.com
'
'Date: May 16, 2004, 11:11:11 AM
'
'Credits: Yours for the taking!<grin.
'
'BUGS: Please report any bugs to:
' St*****@lebans. com
'
'What's Missing:
' DAO error handling
' All other Error handling
' Add it yourself!
'
'How it Works:
' Walk through the source code!<grin>
'
' Enjoy
' Stephen Lebans

Private Declare Function FindWindow Lib "user32" Alias _
"FindWindow A" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias _
"FindWindow ExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Private Declare Function apiGetClassName Lib "user32" _
Alias "GetClassNa meA" _
(ByVal hWnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) _
As Long

Private Declare Function GetFocus Lib "user32" () As Long

Private Declare Function GetParent Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA " _
(ByVal hWnd As Long, ByVal lpString As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA " _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function IsWindowVisible Lib "user32" _
(ByVal hWnd As Long) As Long


Public Function fShowComments()
On Error GoTo Err_ShowComment s

' This function can only be called from a
' ToolBar/Menu when the SQL Design window
' is in SQL view.

Dim varLength As Variant
Dim lngRet As Long
Dim hWndMDI As Long
Dim hWndOQry As Long
Dim hWndOKttbx As Long
Dim hwndODsk As Long
Dim hWndOSUI As Long
Dim s As String
Dim sComment As String
Dim sCaption As String
sComment = "/Comments Start Here: Remember to HIDE the comments before
Saving this Query!!!!!" & vbCrLf
' Recordset stuff
Dim rst As DAO.Recordset
Dim sSQL As String
Dim sSel As String
Dim db As DAO.Database

hWndOKttbx = GetFocus
If fGetClassName(h WndOKttbx) <> "OKttbx" Then
fShowComments = False
Exit Function
End If

' Get Parent
hWndOQry = GetParent(hWndO Kttbx)
' If not "OQry" then we are not in Query Design window
If hWndOQry = 0 Then Exit Function

If fGetClassName(h WndOQry) <> "OQry" Then
fShowComments = False
Exit Function
End If

' Get the Caption of the SQL Design window
sCaption = Space(512)
lngRet = GetWindowText(h WndOQry, sCaption, 256)
sCaption = Left(sCaption, lngRet)
If Len(sCaption & vbNullString) = 0 Then Exit Function

' Find the window of class ODsk
hwndODsk = FindWindowEx(hW ndOQry, 0&, "ODsk", vbNullString)
' If does nto exist then we are not in the Query Design window
If hwndODsk = 0 Then Exit Function
' If this Window is Visible then we are not in the
' SQL View window!
lngRet = IsWindowVisible (hwndODsk)
If lngRet <> 0 Then Exit Function

' Get the Text of the SQL Design window
s = Space(4096)
lngRet = GetWindowText(h WndOKttbx, s, 2048)
s = Left(s, lngRet)
If Len(s & vbNullString) = 0 Then Exit Function
' See if there are already Comments in place.
' If so REMOVE THEM
varLength = InStr(1, s, "/Comment", vbTextCompare)
If varLength <> 0 Then
' Remove comments
' Save Comments to Disk first!!

' Grab a ref to the CurrentDB
Set db = CurrentDb()
' Setup our SQL string
sSQL = "SELECT [SQL-Comments].QueryName, [SQL-Comments].Comment FROM
[SQL-Comments] WHERE [SQL-Comments].QueryName = " & """" & sCaption & """"
Set rst = db.OpenRecordse t(sSQL, dbOpenDynaset)

' Empty?
If rst.RecordCount = 0 Then
' No existing record. Create one.
rst.AddNew
Else
rst.Edit
End If
rst.Fields("Que ryName") = sCaption
rst.Fields("Com ment") = Mid(s, varLength)
rst.Update

' Free our RecordSet
Set rst = Nothing
db.Close
Set db = Nothing

' Remove our Comment from the SQL View window
' Remove everything after the SQL EOF marker ";"
s = Left(s, InStr(1, s, ";", vbTextCompare))
lngRet = SetWindowText(h WndOKttbx, s)
Exit Function
End If

' If we arrive here then no comments are displayed.
' Let's check our table and see if there is an existing
' entry for this saved Query. If so then display it!
' Grab a ref to the CurrentDB
Set db = CurrentDb()
' Setup our SQL string
sSQL = "SELECT [SQL-Comments].QueryName, [SQL-Comments].Comment FROM
[SQL-Comments] WHERE [SQL-Comments].QueryName = " & """" & sCaption & """"
Set rst = db.OpenRecordse t(sSQL, dbOpenDynaset)

' Empty?
If rst.RecordCount = 0 Then
' Add our New Comment Header
s = s & vbCrLf & vbCrLf & sComment
lngRet = SetWindowText(h WndOKttbx, s)
Else
' Load and display our stored comment
s = s & vbCrLf & vbCrLf & rst.Fields("Com ment")
lngRet = SetWindowText(h WndOKttbx, s)
End If

' Free our RecordSet
Set rst = Nothing
db.Close
Set db = Nothing

'DoCmd.Beep
Exit_Err_ShowCo mments:
Exit Function

Err_ShowComment s:
MsgBox Err.Description
Resume Exit_Err_ShowCo mments

End Function
' From Dev Ashish's Site
' The Access Web
' http://www.mvps.org/access/

'******* Code Start *********
Private Function fGetClassName(h Wnd As Long)
Dim strBuffer As String
Dim lngLen As Long
Const MAX_LEN = 255
strBuffer = Space$(MAX_LEN)
lngLen = apiGetClassName (hWnd, strBuffer, MAX_LEN)
If lngLen > 0 Then fGetClassName = Left$(strBuffer , lngLen)
End Function
'******* Code End *********
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

"sghi" <st************ **@getronics.co m> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
thank you Teddysn...,
but my problem is that i'd like to read in a string in a vba module the
sql statement that you can read in the "..."SQL view" from the "View"
button."

Ciao
Stefano

Feb 7 '06 #6
Thank you for the answer, i'll make good use of it.
The reason for my request is that i want to transform "on the fly" a
query in a make table query (putting a "INTO tempTable" before "FROM")
depending on the query content.

Ciao
Stefano

Feb 8 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
40295
by: francis70 | last post by:
Hi, I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the user commits. But in Informix there is this thing called ISOLATION LEVELS. For example by setting the ISOLATION LEVEL to DIRTY READ, a user will read dirty data, i.e. the last uncommited updated value of a field by some other user. Is
7
35679
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the fields. Can this be done in an Access db? I am trying to learn how to do these things in Access vs Visual Dim strIIF As String Dim sstrIIF As String Dim strNewDate As Date
2
1857
by: Melissa | last post by:
I have a single (not continuous) form with an Undo button for entering finished projects. On the form is also a subform that lists all finished projects for reference. When I enter the CompletionDate which is near the beginning of all the fields, I would like the current project to appear in the subform which lists all the finished projects. I can't save the current project at that point because I wouldn't be able to use the Undo button...
13
6594
by: MLH | last post by:
Suppose I have this simple SQL string... SELECT tblDrivers.DriverID, tblDrivers.DName FROM tblDrivers WHERE (((tblDrivers.DName) Like "N*")) ORDER BY tblDrivers.DriverID; And suppose that its not a saved querydef - just an SQL string that I cooked up in code and assigned to a global var strMySQL.
6
2695
by: FayeC | last post by:
I really need help figuring this out. i have a db with mostly text fields but 2. The user_id field is an autonumber (key) and the user_newsletter is a number (1 and 0) field meaning 1 yes the person wants to receive a newsletter and 0 no, don't want to receive it. Now.....when trying to create an UPDATE statement I am running into problems writing the code for it. I had issues before with missing commas but now that the commas are there...
0
839
by: Dominique | last post by:
Hello, I have the following code: ===================================================== Public Sub LoadWriteNetworkDetails(ConnectString As String) ' Write the information to the table Inv_Network_Details_Custom ' the task is done with a Stored Procedure called sp_Inv_Network_Details_Custom_update
2
3202
by: deekay | last post by:
Im trying to update a database where our users have been entering/editing all data using queries to now use forms instead. The first step that I want to take is to convert the queries to forms in datasheet view so that they look exactly the same. I just want to know whether it is better to bind the forms record source to the queries or to a sql statement that would include the relevant tables and fields. Please advise thanks
3
1139
by: israphelr | last post by:
Hi all. I have to put together some code that reads high scores from a saved file, then gives the user the opportunity to add their name and score to the high scores list, which is then saved. Trouble is, I can't tell the program to read a file that doesn't exist, that generates an error. So I must have a file created, problem HERE is everytime the program
1
3738
by: danibecr | last post by:
I'm trying to make a table that will daily count the records imported and save them to a seperate table along with the date imported. But as of now after all the processing is complete I delete everything off of the original import tables. I have tried using a form to get the correct counts and dates which work but in order for it to run somewhat smooth the form HAS to be open otherwise it asks for paramater values. Is there an easy way to do...
0
9856
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9698
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10914
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10597
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9434
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7834
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4071
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.