By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

brackets added to pass through query

P: n/a
Hello All,

Why would brackets be added to the SQL of a pass through query to
Oracle?

If I paste the debug print of the SQL statement into SQLPlus of Oracle's
XE edition it works, and does NOT have brackets around the tablespace
preceding the table name. However when I create the pass through query
in VB brackets are placed around the tablespace, and the pass through
query does NOT work. If I eliminate the brackets around the tablespace
in the pass through query in Access it works.

Any ideals on how to eliminate the brackets around the tablespace when
the SQL of the pass through query is created in VB?

TIA!

You will find below the following:

1 debug print of pass through query in VB,

2 pass through query saved in Access module by VB, &

3 code creating the pass through query.

,----- [ 1 debug print of pass through query in VB ]
| select NVL(sum(ActualRev), 0) as TotActRev, NVL(sum(BudgetRev), 0) as
| TotBdtRev, NVL(sum(PrYrRev), 0) as TotPyRev FROM sc.tblSummary WHERE
| (PdNo = 2) AND (TypeOfMetric = 'Ord');
`-----

,----- [ 2 pass through query saved in Access module by VB ]
| SELECT NVL(sum(ActualRev), 0) AS TotActRev, NVL(sum(BudgetRev), 0) AS
| TotBdtRev, NVL(sum(PrYrRev), 0) AS TotPyRev
| FROM [sc].tblSummary
| WHERE (PdNo = 2) AND (TypeOfMetric = 'Ord');
`-----

=====================>Begin Code>===================================>3a
Private Sub Report_Open(Cancel As Integer)
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim qdfPassThrew As DAO.QueryDef
Dim strSQL As String

Set wsCur = DBEngine.Workspaces(0)
Set dbCur = wsCur.Databases(0)

Call SetConStr

If DoesQryExist("qryMTDOrdRev") Then
dbCur.QueryDefs.Delete ("qryMTDOrdRev")
End If

strSQL = "select NVL(sum(ActualRev), 0) as TotActRev, " & _
"NVL(sum(BudgetRev), 0) as TotBdtRev, " & _
"NVL(sum(PrYrRev), 0) as TotPyRev " & _
"FROM sc.tblSummary " & _
"WHERE (PdNo = 2) AND (TypeOfMetric = 'Ord');"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQueryDef("qryMTDOrdRev", strSQL)
qdfPassThrew.Connect = "ODBC;" & strCnn

Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
RefreshDatabaseWindow

End Sub
=====================<End Code<=====================================<3a

=====================>Begin Code>===================================>3b
Function DoesQryExist(strQryName As String) As Boolean
Dim db As Database
Dim qdef As QueryDef
Set db = CurrentDb
On Error Resume Next
Set qdef = db.QueryDefs(strQryName)
If Err.Number = 3265 Then
DoesQryExist = False
Else
DoesQryExist = True
End If
Set qdef = Nothing
Set db = Nothing
End Function
=====================<End Code<=====================================<3b

Again thanks!

--
Regards,

Greg Strong
Aug 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sat, 19 Aug 2006 15:23:37 -0500, Greg Strong
<ne*********@geedubeeu.com.invalidwrote:
>Why would brackets be added to the SQL of a pass through query to
Oracle?

Apparently the order. I set up PT query with the help of this link at
http://support.microsoft.com/kb/q112108/ and no brackets now. Thanks to
Bob P. in another user group for the link.

--
Regards,

Greg Strong
Aug 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.