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
