Why is Access Changing My Query?!

[BlakeK]

I have a pass-through query that Access apparently has decided to change the SQL of for some unknown reason. After being created through code and ran, it gets an ODBC Call Failed message, due to bad syntax. If I edit the Query manually and change it back to what I originally had and rerun it, it works fine!

Here is what I am doing in code:
***********************START CODE***************************
MySQL = ""
MySQL = MySQL & " SELECT 'del ' + [master].[local_path] AS batch"
MySQL = MySQL & " FROM (SELECT tbl_server_master_drives.drive_configuration, tbl_server_master_drives.library_id, tbl_server_master_drives.version_number, tbl_server_master_drives.local_path"
MySQL = MySQL & " FROM tbl_server_master_drives"
MySQL = MySQL & " WHERE tbl_server_master_drives.drive_configuration = 2"
MySQL = MySQL & " AND tbl_server_master_drives.library_id = 31"
MySQL = MySQL & " AND tbl_server_master_drives.version_number = 7) As MASTER"
MySQL = MySQL & " LEFT JOIN (SELECT tbl_server_install_library.server_install_id, tbl_server_install_library.library_type_id, tbl_server_install_library.local_path"
MySQL = MySQL & " FROM tbl_server_install_library"
MySQL = MySQL & " WHERE tbl_server_install_library.server_install_id = 130"
MySQL = MySQL & " AND tbl_server_install_library.library_type_id = 3) As SERVER"
MySQL = MySQL & " ON MASTER.local_path = SERVER.local_path"
MySQL = MySQL & " WHERE SERVER.local_path Is Null"

varQueryName = "qryMasterDrives"
Set MyQDF = MyDB.CreateQueryDef(varQueryName, MySQL)
MyQDF.Connect = "ODBC;Trusted_Connection=YES;DRIVER={SQL Server};SERVER=SSQL;DATABASE=eNCast;"
MyQDF.ODBCTimeout = 0
Set MyRST = MyQDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)

***********************END CODE****************************

So, basically the idea of the query is to compare two result sets as derived tables.
The String (MySQL) that I build above result in the following statement:

************************START GOOD QUERY********************
SELECT 'del ' + [master].[local_path] AS batch
FROM (SELECT tbl_server_master_drives.drive_configuration,
tbl_server_master_drives.library_id,
tbl_server_master_drives.version_number,
tbl_server_master_drives.local_path
FROM tbl_server_master_drives
WHERE tbl_server_master_drives.drive_configuration = 2
AND tbl_server_master_drives.library_id = 35
AND tbl_server_master_drives.version_number = 7) As MASTER
LEFT JOIN
(SELECT tbl_server_install_library.server_install_id,
tbl_server_install_library.library_type_id,
tbl_server_install_library.local_path
FROM tbl_server_install_library
WHERE tbl_server_install_library.server_install_id = 1379
AND tbl_server_install_library.library_type_id = 3) As SERVER
ON MASTER.local_path = SERVER.local_path
WHERE SERVER.local_path Is Null
************************END GOOD QUERY********************

The problem is, if you open the Query after it is created, Access has changed it to this...

************************START BAD QUERY********************
SELECT 'del ' + [master].[local_path] AS batch
FROM [SELECT tbl_server_master_drives.drive_configuration, tbl_server_master_drives.library_id, tbl_server_master_drives.version_number, tbl_server_master_drives.local_path FROM tbl_server_master_drives WHERE tbl_server_master_drives.drive_configuration = AND tbl_server_master_drives.library_id = 35 AND tbl_server_master_drives.version_number = 7]. AS MASTER LEFT JOIN [SELECT tbl_server_install_library.server_install_id, tbl_server_install_library.library_type_id, tbl_server_install_library.local_path From tbl_server_install_library WHERE tbl_server_install_library.server_install_id = 1379 AND tbl_server_install_library.library_type_id = 3]. AS SERVER ON MASTER.local_path = SERVER.local_path
WHERE Server.local_path Is Null;
************************END BAD QUERY********************

You see where it replaced the parentheses around the derived tables with square brackets and also added a period preceding the "as" keyword for the derived table name, and it added a semi-colon to the end of the statement.

Try to run that query, and it fails.
Manually modify the query to change it back to have parentheses instead of brackets, and remove the 2 periods and the semi-colon, and it runs fine!

So, why is Access changing this, and how can I prevent it from doing so?

Thanks for your help.

 

[PHV]

Have you tried this ?
MyQDF.Type = dbQSQLPassThrough

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BlakeK]

When I open the query after it is created, it is shown as a Pass-Through.
And according to Access Help:
To create an SQL pass-through query in a Microsoft Jet workspace, you don't need to explicitly set the Type property to dbQSQLPassThrough. The Microsoft Jet database engine automatically sets this when you create a QueryDef object and set the Connect property.

I did however attempt what you suggested, and unfortunately, it results in an error that the setting is read-only.
Access Help states:
For a QueryDef, Recordset, or Workspace object, the property setting is read-only.

Thanks for the suggestion though.

 

[cmmrfrds]

The syntax you indicated of []. runs fine in Jet but I don't know how to keep Access from changing the brackets to []. on a derived query. Since you are building the SQL as a string anyway, just send the string over the connection instead of putting it in a querydef. Also, if the query is initially create through the QBE grid, you should be able to make it pass-through in the grid so that it is not necessary to set in code.