Breckets Added on CreateQueryDef

G

Guest

I have a somewhat unsual problem. I am creating a passthrough query to hit
DB2 table with Access. The problem is DB2 is a little sensitive to
punctuation. I was able to create the paathrough query just fine with the
following code. The problem is the query fails because Access addes Brackets
around the first node in the tables in the from clause, "[TBDW1].

Any ideas of how to turn this off.

SQL = "SELECT " & _
"CONF_TEST_ORD_D.TEST_ORD_NUM, " & _
"LBTR_LAB_SPEC_D.SPECIMEN_NUMBER, " & _
"LBTR_TEST_RSLTS_F.ORDER_DT_SRGT " & _
"FROM TBDW1.LBTR_TEST_RSLTS_F LBTR_TEST_RSLTS_F, " & _
"TBDW1.LBTR_LAB_SPEC_D LBTR_LAB_SPEC_D, " & _
"TBDW1.CONF_TEST_ORD_D CONF_TEST_ORD_D " & _
"Where LBTR_TEST_RSLTS_F.SPECIMEN_SRGT =
LBTR_LAB_SPEC_D.SPECIMEN_SRGT And " & _
"LBTR_TEST_RSLTS_F.TEST_ORD_SRGT =
CONF_TEST_ORD_D.TEST_ORD_SRGT And " & _
" (CONF_TEST_ORD_D.TEST_ORD_NUM Between '" & O1 & "' And '" & Or2 & "')
And" & _
" (LBTR_TEST_RSLTS_F.ORDER_DT_SRGT Between " & D1 & " And " & D2 & ");"

QN = "TmpTestQry"
Set QDF = db.CreateQueryDef(QN, SQL)
QDF.Connect = "ODBC;DSN=@;UID=@;PWD=@;MODE=SHARE;DBALIAS=@;"

@ - Names were changed to protect the innocent.
 
R

Roger Carlson

That IS very odd. Access should not be adding anything to your string. One
suggestion is that "SQL" is a reserved word in Access and this might be
complicating things. Try strSQL instead and see if it changes anything.

Also, add the line
Debug.Print strSQL

just after the code creating the string and put a code break on the line
immediately following. You will see exactly what the string looks like in
the Immediate Window.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
D

Douglas J. Steele

The problem may be that the SQL is being added to the query before Access
knows it's a pass-through query.

See whether resetting the SQL once the query's created works:

QN = "TmpTestQry"
Set QDF = db.CreateQueryDef(QN, SQL)
QDF.Connect = "ODBC;DSN=@;UID=@;PWD=@;MODE=SHARE;DBALIAS=@;"
QDF.SQL = SQL


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roger Carlson said:
That IS very odd. Access should not be adding anything to your string.
One suggestion is that "SQL" is a reserved word in Access and this might
be complicating things. Try strSQL instead and see if it changes
anything.

Also, add the line
Debug.Print strSQL

just after the code creating the string and put a code break on the line
immediately following. You will see exactly what the string looks like in
the Immediate Window.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

BCP said:
I have a somewhat unsual problem. I am creating a passthrough query to
hit
DB2 table with Access. The problem is DB2 is a little sensitive to
punctuation. I was able to create the paathrough query just fine with
the
following code. The problem is the query fails because Access addes
Brackets
around the first node in the tables in the from clause, "[TBDW1].

Any ideas of how to turn this off.

SQL = "SELECT " & _
"CONF_TEST_ORD_D.TEST_ORD_NUM, " & _
"LBTR_LAB_SPEC_D.SPECIMEN_NUMBER, " & _
"LBTR_TEST_RSLTS_F.ORDER_DT_SRGT " & _
"FROM TBDW1.LBTR_TEST_RSLTS_F LBTR_TEST_RSLTS_F, " & _
"TBDW1.LBTR_LAB_SPEC_D LBTR_LAB_SPEC_D, " & _
"TBDW1.CONF_TEST_ORD_D CONF_TEST_ORD_D " & _
"Where LBTR_TEST_RSLTS_F.SPECIMEN_SRGT =
LBTR_LAB_SPEC_D.SPECIMEN_SRGT And " & _
"LBTR_TEST_RSLTS_F.TEST_ORD_SRGT =
CONF_TEST_ORD_D.TEST_ORD_SRGT And " & _
" (CONF_TEST_ORD_D.TEST_ORD_NUM Between '" & O1 & "' And '" & Or2 & "')
And" & _
" (LBTR_TEST_RSLTS_F.ORDER_DT_SRGT Between " & D1 & " And " & D2 &
");"

QN = "TmpTestQry"
Set QDF = db.CreateQueryDef(QN, SQL)
QDF.Connect = "ODBC;DSN=@;UID=@;PWD=@;MODE=SHARE;DBALIAS=@;"

@ - Names were changed to protect the innocent.
Click to expand...
Click to expand...
 
Top