 NeoPa  32,556
Expert Mod 16PB
Access QueryDefs Mis-save Subquery SQL
Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "[]" and (often) add an extraneous "." after it.
For instance, a simple example illustrates how it works : - SELECT subA.*
-
FROM (SELECT [fA], [fB]
-
FROM [MyTable]) AS subA
becomes (after saving as a querydef in Access) : - SELECT subA.*
-
FROM [SELECT [fA], [fB]
-
FROM [MyTable]]. AS subA
I have no idea why Access does this, but it seems to be consistent. In some versions the resultant SQL is recognised by Access but in earlier versions it fails to recognize its own SQL.
There are fundamentally two ways to access the SQL from an Access QueryDef. - When designing the QueryDef, select View / SQL View. This shows (and allows editing of) the SQL behind the QueryDef.
- Programatically, a QueryDef object has an updatable string property called .SQL. This accesses the same data.
Using the former method it's often necessary to update these brackets to parentheses if intending to update the SQL directly.
In code this is more of a problem. With this in mind I developed a short routine to read the SQL of a QueryDef safely. - 'GetSQL gets the SQL component from a named query OR a SQL string.
-
'When subqueries are specified in MS Access they are changed internally
-
'from "FROM (SELECT blah blah blah) AS" to
-
'either "FROM [SELECT blah blah blah]. AS"
-
'or "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
-
'This code assumes any effected subquery will start with " [SELECT ".
-
'This reverts SQL to correct format and loses ';' at end if requested.
-
' 11/11/2004 Updated to detect second problem and to use InStr
-
Public Function GetSQL(strQuery As String, _
-
Optional blnLoseSC As Boolean = True) As String
-
Dim intDepth As Integer
-
Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
-
-
'If param passed is already SQL then leave as is - Otherwise get from query
-
If Left(strQuery, 1) = "(" Then
-
GetSQL = strQuery
-
Else
-
On Error Resume Next
-
GetSQL = CurrentDb.QueryDefs(strQuery).SQL
-
End If
-
lngLeft = -7
-
Do
-
'Check for corrupted subquery
-
lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)
-
If lngLeft = 0 Then Exit Do
-
'To find end correctly we must treat '[' & ']' as matched pairs
-
intDepth = 1
-
lngRight = lngLeft + 8
-
lngOpen = -lngRight
-
Do
-
'Find next ']'
-
lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)
-
If lngRight = 0 Then
-
GetSQL = ""
-
Exit Function
-
End If
-
intDepth = intDepth - 1
-
Do
-
'For lngOpen negative numbers mean that item has been counted
-
'If already counted get next one - Otherwise drop through
-
If lngOpen < 0 Then _
-
lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)
-
'we're only interested (now) if it found one BEFORE the ']'
-
If lngOpen > lngRight Or lngOpen = 0 Then Exit Do
-
intDepth = intDepth + 1
-
lngOpen = -lngOpen
-
Loop
-
Loop While intDepth > 0
-
'If '].' found then be sure to drop the '.' too
-
lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)
-
GetSQL = Left(GetSQL, lngLeft) & "(" & _
-
Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _
-
Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
-
Loop
-
'Lose ";" at end if requested and it exists
-
If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
-
GetSQL = Left(GetSQL, Len(GetSQL) - 3)
-
End Function
Ok. Maybe not too short.
1 7697 
I have been driven to madness and foul language by this habit of Access to corrupt the SQL. But I always thought it was limited to the GUI display. I never encountered the problem when accessing the SQL via code. Maybe I've just been lucky. I'm going to add this to my stock code DB. Thank you!
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ray_nl |
last post by:
Hi Everyone,
I've got an Access application which has a link to an SQL 7 Server.
In my application I create a stored procedure which I send to the SQL
server via a passthrough query. I always...
|
by: Mike Knight |
last post by:
(I've also posted this problem on microsoft.public.excel.programming)
I have a MS Access 2003 Database named "AS400 Fields.mdb". This
database contains links to tables on an AS400.
In MS...
|
by: Alan Lane |
last post by:
Hello world:
I'm including both code and examples of query output. I appologize if
that makes this message longer than it should be.
Anyway, I need to change the query below into a pivot table...
|
by: MLH |
last post by:
Mainly, I want to search the saved queries.
I don't want to replace them. I just want to find them.
|
by: Tim Marshall |
last post by:
A2003, but this behaviour also occurred during the same DAO process I'm
about to describe in A97.
I have a sub procedure which takes two arguments: a querydef name; and
an SQL statement...
|
by: DraguVaso |
last post by:
Hi,
I want to make a small application in VB.NET that relinks all the query's
and tables in an Access database that are linked via ODBC to an SQL Server.
It must be able to relink all the tables...
|
by: Oceania |
last post by:
Hi All,
I did try using the sample code from the ADO.net forum to link one
table, it is working fine. But, I got a problem when I tried to link
all tables (20 tables)... Please help, thank...
|
by: Randy |
last post by:
I am trying to relink some Oracle tables in an Access database via VB.NET and ADOX. I receive the following error when executing the cat.ActiveConnection link
"Arguments are of the wrong type,...
|
by: Arun |
last post by:
Hi, I'm trying to create a dynamic query in MS-Access. I have a table
with 3 fields and my dynamic query will be constructed with those 3
fields. Since I haven't done this before, I searched for...
|
by: Greg Strong |
last post by:
Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
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...
| |
