473,708 Members | 2,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ODBC Max Length of Pass Through Query with Access 2k2?

Hello All,

What is the maximum length of an ODBC pass through query?

Things work fine with the code except when I try to create a view which
is pretty complex in Oracle. I'm using a DSN provided with the Oracle
Express Edition and using Access 2k2 as the front-end. When I debug
print the SQL and paste it into SQLplus it works fine. The length of
the debug print that works is 1988 characters. Since the exact same
code works with shorter SQL I'm guessing there is a max length to the
string that can be passed which is why I asked the question. Error
reads:

,----- [ Error on PTQ ]
| Run-time error '3146'
| ODBC-call failed.
`-----

The code is included below. Any ideals. Thanks!

=============== ======>Begin Code>========== =============== ==========>
Sub Create_vw_tblOr dersCurYr_rev2( )
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim qdfPassThrew As DAO.QueryDef
Dim strSQL As String

Set wsCur = DBEngine.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr

strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrders CurYr_rev2 AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _
"case when Length(MktCode) = 4 then case when
(substr(MktCode ,-2) between 10 and 19) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "1" & Chr(39) & " when (substr(MktCode ,-2) between 20
and 29) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "2" &
Chr(39) & " when (substr(MktCode ,-2) between 30 and 39) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "3" & Chr(39) & " when
(substr(MktCode ,-2) between 40 and 49) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "4" & Chr(39) & " when (substr(MktCode ,-2) between 50
and 59) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "5" &
Chr(39) & " when (substr(MktCode ,-2) between 60 and 69) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when
(substr(MktCode ,-2) between 70 and 79) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "7" & Chr(39) & " when (substr(MktCode ,-2) between 80
and 89) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "8" &
Chr(39) & " when (substr(MktCode ,-2) between 90 and 99) then " _
& Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "9" & Chr(39) & "
when (substr(MktCode ,-2) between 100 and 109) then " & Chr(39) & "VM" &
Chr(39) & "||" & Chr(39) & "10" & Chr(39) & " end when Length(MktCode) =
5 then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "10" & Chr(39) &
" end as VertMkt, " & _
"case when (substr(BrchNo,-4) between 1500 and 1550) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BrchNo,-4) between 2500 and 2550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BrchNo,-4)
between 3500 and 3550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "3" & Chr(39) & " when (substr(BrchNo,-4) between 4500 and
4550) then " & Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "4" &
Chr(39) & " when (substr(BrchNo,-4) between 5500 and 5500) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when
(substr(BrchNo,-4) between 6500 and 6550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when (substr(BrchNo,-4)
between 7500 and 7550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "7" & Chr(39) & " end as Rgn, " & _
"case when (substr(ProdNo,-3) between 100 and 199) then " &
Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(ProdNo,-3) between 200 and 299) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(ProdNo,-3)
between 300 and 399) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " when (substr(ProdNo,-3) between 400 and 499) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "4" & Chr(39) & " when
(substr(ProdNo,-3) between 500 and 599) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when (substr(ProdNo,-3)
between 600 and 699) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "6" & Chr(39) & " when (substr(ProdNo,-3) between 700 and 799) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "7" & Chr(39) & " when
(substr(ProdNo,-3) between 800 and 899) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "8" & Chr(39) & " end as LineOfBus " & _
"from sc.tblOrdersCur Yr;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQue ryDef("")
qdfPassThrew.Co nnect = "ODBC;" & strCnn
qdfPassThrew.SQ L = strSQL
qdfPassThrew.Re turnsRecords = False
wsCur.BeginTran s
qdfPassThrew.Ex ecute
wsCur.CommitTra ns
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=============== ======<End Code<========== =============== ============<

What can I say? I'm just experimenting a little with Access 2k2 and
Oracle 10g Express Edition. Thanks!

--
Regards,

Greg Strong
Jul 13 '06 #1
9 2576
Greg Strong wrote:
What is the maximum length of an ODBC pass through query?
64K characters. Beyond that you get a message that says something to
the effect that the statement can't be edited or some such.
Things work fine with the code except when I try to create a view which
is pretty complex in Oracle.
The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.
I'm using a DSN provided with the Oracle
Express Edition and using Access 2k2 as the front-end.
I have heard of some issues with Oracle 10g and the Oracle ODBC driver.
Sorry, I can't remember exactly what they were. Some of the people in
my development/user community (for a CMMS) claim they found that using
the MS ODBC driver alleviated whatever problems they were having.

I've developed some standard procs that create Oracle views, as well as
procs that combine those views into a select statement (I generate SQL
that, without the use of views, can exceed 100K characters) and into a
PTQ querydef. I didn't bother to post them as there are numerous
conventions I use which may be difficult to see in context. I *don't*
use work space objects like you have nor have I come up with the need to
run commit statements. When you run an insert/update/delete statement
via a PTQ, the ODBC connection does the commit; statement for you.
,----- [ Error on PTQ ]
| Run-time error '3146'
| ODBC-call failed.
`-----
It's better to trap the Oracle error. There's a KB article somewhere on
bringing back errors from Oracle which in my usually helpful manner, I
can't remember, but I'll show my code for this following. This is old
code and I've done some silly things in it (such as the strExplanation
string), but it's tried and true for the most part (watch the wrap).
fError is used in the on error proc of a sub/function as follows in
which this is a function of the form frmTmadSave:

^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^
Function fSaveGraphDetai ls()

<snip procedure>

Exit_Proc:
Exit Function
Err_Proc:
Select Case Err.Number
Case Else
fError "frmTmadSav e", "fSaveGraphDeta ils"
Resume Exit_Proc
End Select
End Function
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^
Function fError(strForm As String, strProc As String, Optional
strExplanation As String)

'Generic Oracle error trapping method
'strexplanation should be prefeixed and suffixed and separated by
semicolons, with no semicolons used in error messages. For example:
';1;"Already entered as a name";

Dim errX As Error
Dim strMsg As String
Dim strMsgErr As String
Dim strTitle As String
Dim int1 As Integer
Dim int2 As Integer

strTitle = "Form/Module: " & strForm & ", Procedure: " & strProc
strMsg = "Please contact Tim immediately with the full text of this
message, including " & _
"the form and procedure name in the title of this message box."

'sometimes an error can be called and errors.count will be 0
If Errors.Count = 0 Then
MsgBox "Error " & Err.Number & " " & Err.Description ,
vbCritical, strTitle
Else
For Each errX In Errors
'if error count >1, Oracle error will be called first
If Errors.Count 1 Then
int1 = InStr(1, strExplanation, ";" & errX.Number & ";")
If int1 0 Then 'there's a custom explanation provided
for this error
'find next semi-colon

int1 = InStr(int1 + 1, strExplanation, ";") + 1
int2 = InStr(int1, strExplanation, ";") - 1

If int2 - int1 < 1 Then
strMsg = "Oracle Error " & errX.Number & " " &
errX.Descriptio n
Else
strMsgErr = Mid(strExplanat ion, int1, int2 - int1)
End If

Else
strMsgErr = "Oracle Error " & errX.Number & " " &
errX.Descriptio n
End If

MsgBox strMsgErr, vbCritical, strTitle

Exit For

Else

MsgBox "Access Error " & Err.Number & " " &
Err.Description & vbCrLf & vbCrLf & _
strMsg, vbCritical, strTitle, Err.HelpFile,
Err.HelpContext

End If

Next errX

End If

End Function
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jul 13 '06 #2
On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.
FWIW the problem is with the case statements. Althought they work in
SQLPlus, when I simplify and remove case statement the create or replace
views work.

"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _

I've eliminated the "|" and still an error. I would think ODBC doesn't
like something in there.

--
Regards,

Greg Strong
Jul 17 '06 #3
Greg Strong wrote:
"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _

I've eliminated the "|" and still an error. I would think ODBC doesn't
like something in there.
I'll look at my development stuff at work when I get back on Tuesday.
However, I'm pretty sure some of the create or replace view statements
I'm firing off via PTQs from Access VBA work just fine and the
(ridiculously) large views I create certainly have some fairly in-depth
case statements and certainly lots of pipes ||

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jul 17 '06 #4
Greg Strong wrote:
"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _
Again, I'll look at my code on Tuesday, but something I just noticed was
your column identifier. I use double quotes, ie (from the end of your
case statement):

between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end ""BusUnit"" , " & _

Instead of as BusUnit.

I can't imagine that causing anyy difference in performance, but just a
difference I spotted...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jul 17 '06 #5
On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>Again, I'll look at my code on Tuesday, but something I just noticed was
your column identifier. I use double quotes, ie (from the end of your
case statement):

between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end ""BusUnit"" , " & _

Instead of as BusUnit.
FWIW I've tried your syntax. I don't know why you left out the 'as', but
anyhow I thought possible easy oversight given the focus on the double
quotes. So I tried some other possibilities. I eliminated the pipes,
'|', and was left with what is below. The just of it I still get the
same error, and when I copy/paste the debug print into SQLPlus it works.
When I replace the SQL string with the drop view command the PTQ works.
So apparently something in the case statement being handled by ODBC is a
little fickle. If you also notice I took out the 'BeginTrans' and
'CommitTrans'

,----- [ Error on PTQ ]
| Run-time error '3146'
| ODBC-call failed.
`-----

=============== ======>Begin Code>========== =============== ==========>
Sub Create_vw_tblOr dersCurYr_test_ r2()
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim cnn As DAO.Connection
Dim qdfPassThrew As DAO.QueryDef
Dim strSQL As String

Set wsCur = DBEngine.Worksp aces(0)
Set dbCur = wsCur.Databases (0)

Call SetConStr

strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrders CurYr_test AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(buscode ,-3) between 100 and 199) then " &
Chr(39) & "BU1" & Chr(39) & " when (substr(buscode ,-3) between 200 and
299) then " & Chr(39) & "BU2" & Chr(39) & " when (substr(buscode ,-3)
between 300 and 399) then " & Chr(39) & "BU3" & Chr(39) & " end as
""BusUnit"" " & _
"from sc.tblOrdersCur Yr;"

' strSQL = "drop view sc.vw_tblOrders CurYr_test;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQue ryDef("")
qdfPassThrew.Co nnect = "ODBC;" & strCnn
' qdfPassThrew.Co nnect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQ L = strSQL
qdfPassThrew.Re turnsRecords = False
' wsCur.BeginTran s
qdfPassThrew.Ex ecute
' wsCur.CommitTra ns
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=============== ======<End Code<========== =============== ============<

Thanks!

--
Regards,

Greg Strong
Jul 19 '06 #6
All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using
Transactions are therefore of no use

Pieter

"Greg Strong" <ne*********@ge edubeeu.com.inv alidwrote in message
news:mc******** *************** *********@4ax.c om...
On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>>Again, I'll look at my code on Tuesday, but something I just noticed was
your column identifier. I use double quotes, ie (from the end of your
case statement):

between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end ""BusUnit"" , " & _

Instead of as BusUnit.

FWIW I've tried your syntax. I don't know why you left out the 'as', but
anyhow I thought possible easy oversight given the focus on the double
quotes. So I tried some other possibilities. I eliminated the pipes,
'|', and was left with what is below. The just of it I still get the
same error, and when I copy/paste the debug print into SQLPlus it works.
When I replace the SQL string with the drop view command the PTQ works.
So apparently something in the case statement being handled by ODBC is a
little fickle. If you also notice I took out the 'BeginTrans' and
'CommitTrans'

,----- [ Error on PTQ ]
| Run-time error '3146'
| ODBC-call failed.
`-----

=============== ======>Begin Code>========== =============== ==========>
Sub Create_vw_tblOr dersCurYr_test_ r2()
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim cnn As DAO.Connection
Dim qdfPassThrew As DAO.QueryDef
Dim strSQL As String

Set wsCur = DBEngine.Worksp aces(0)
Set dbCur = wsCur.Databases (0)

Call SetConStr

strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrders CurYr_test AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(buscode ,-3) between 100 and 199) then " &
Chr(39) & "BU1" & Chr(39) & " when (substr(buscode ,-3) between 200 and
299) then " & Chr(39) & "BU2" & Chr(39) & " when (substr(buscode ,-3)
between 300 and 399) then " & Chr(39) & "BU3" & Chr(39) & " end as
""BusUnit"" " & _
"from sc.tblOrdersCur Yr;"

' strSQL = "drop view sc.vw_tblOrders CurYr_test;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQue ryDef("")
qdfPassThrew.Co nnect = "ODBC;" & strCnn
' qdfPassThrew.Co nnect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQ L = strSQL
qdfPassThrew.Re turnsRecords = False
' wsCur.BeginTran s
qdfPassThrew.Ex ecute
' wsCur.CommitTra ns
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=============== ======<End Code<========== =============== ============<

Thanks!

--
Regards,

Greg Strong

Jul 19 '06 #7
All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using
Transactions are therefore of no use

Pieter

"Greg Strong" <ne*********@ge edubeeu.com.inv alidwrote in message
news:mc******** *************** *********@4ax.c om...
On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>>Again, I'll look at my code on Tuesday, but something I just noticed was
your column identifier. I use double quotes, ie (from the end of your
case statement):

between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end ""BusUnit"" , " & _

Instead of as BusUnit.

FWIW I've tried your syntax. I don't know why you left out the 'as', but
anyhow I thought possible easy oversight given the focus on the double
quotes. So I tried some other possibilities. I eliminated the pipes,
'|', and was left with what is below. The just of it I still get the
same error, and when I copy/paste the debug print into SQLPlus it works.
When I replace the SQL string with the drop view command the PTQ works.
So apparently something in the case statement being handled by ODBC is a
little fickle. If you also notice I took out the 'BeginTrans' and
'CommitTrans'

,----- [ Error on PTQ ]
| Run-time error '3146'
| ODBC-call failed.
`-----

=============== ======>Begin Code>========== =============== ==========>
Sub Create_vw_tblOr dersCurYr_test_ r2()
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim cnn As DAO.Connection
Dim qdfPassThrew As DAO.QueryDef
Dim strSQL As String

Set wsCur = DBEngine.Worksp aces(0)
Set dbCur = wsCur.Databases (0)

Call SetConStr

strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrders CurYr_test AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(buscode ,-3) between 100 and 199) then " &
Chr(39) & "BU1" & Chr(39) & " when (substr(buscode ,-3) between 200 and
299) then " & Chr(39) & "BU2" & Chr(39) & " when (substr(buscode ,-3)
between 300 and 399) then " & Chr(39) & "BU3" & Chr(39) & " end as
""BusUnit"" " & _
"from sc.tblOrdersCur Yr;"

' strSQL = "drop view sc.vw_tblOrders CurYr_test;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQue ryDef("")
qdfPassThrew.Co nnect = "ODBC;" & strCnn
' qdfPassThrew.Co nnect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQ L = strSQL
qdfPassThrew.Re turnsRecords = False
' wsCur.BeginTran s
qdfPassThrew.Ex ecute
' wsCur.CommitTra ns
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=============== ======<End Code<========== =============== ============<

Thanks!

--
Regards,

Greg Strong


--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4285 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Jul 19 '06 #8
On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.
On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.
Well I banged my head long enough on DAO. After reading some threads on
DAO and its relationship to Jet, I thought ADO probably was a better way
to go. I've got it to work with ADO with the OLEDB provided with Oracle
Express. Couple thanks here to several sources. First an excellent post
by an Oracle Express user on Oracle Technology Network per
http://forums.oracle.com/forums/thre...79316&#1279316.
Also many thanks to Access 2k2 Enterprise Developer's Handbook which
provided some great information. I've also learned that in ADO you don't
need the semi-colon at the end. So here is the test that worked:

=============== ======>Begin Code>========== =============== ==========>
Sub TestADO_DLL_SQL ()
Dim cnn As ADODB.Connectio n
Dim strSQL As String
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connectio n

Call SetConStr

'works short version / test
' strSQL = "create or replace view sc.vw_tblorders curyr_test as " & _
' "select orderno, pdneworder, estrev, estcgs, " & _
' "case when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 100 and 199" & Chr(41) & " then " & Chr(39) & "bu1"
& Chr(39) & " when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 200 and 299" & Chr(41) & " then " & Chr(39) & "bu2"
& Chr(39) & " when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 300 and 399" & Chr(41) & " then " & Chr(39) & "bu3"
& Chr(39) & " end as busunit " & _
' "from sc.tblorderscur yr"

strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrders CurYr_rev2 AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _
"case when Length(MktCode) = 4 then case when
(substr(MktCode ,-2) between 10 and 19) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "1" & Chr(39) & " when (substr(MktCode ,-2) between 20
and 29) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "2" &
Chr(39) & " when (substr(MktCode ,-2) between 30 and 39) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "3" & Chr(39) & " when
(substr(MktCode ,-2) between 40 and 49) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "4" & Chr(39) & " when (substr(MktCode ,-2) between 50
and 59) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "5" &
Chr(39) & " when (substr(MktCode ,-2) between 60 and 69) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when
(substr(MktCode ,-2) between 70 and 79) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "7" & Chr(39) & " when (substr(MktCode ,-2) between 80
and 89) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "8" &
Chr(39) & " when (substr(MktCode ,-2) between 90 and 99) then " _
& Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "9" & Chr(39) & "
when (substr(MktCode ,-2) between 100 and 109) then " & Chr(39) & "VM" &
Chr(39) & "||" & Chr(39) & "10" & Chr(39) & " end when Length(MktCode) =
5 then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "10" & Chr(39) &
" end as VertMkt, " & _
"case when (substr(BrchNo,-4) between 1500 and 1550) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BrchNo,-4) between 2500 and 2550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BrchNo,-4)
between 3500 and 3550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "3" & Chr(39) & " when (substr(BrchNo,-4) between 4500 and
4550) then " & Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "4" &
Chr(39) & " when (substr(BrchNo,-4) between 5500 and 5500) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when
(substr(BrchNo,-4) between 6500 and 6550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when (substr(BrchNo,-4)
between 7500 and 7550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "7" & Chr(39) & " end as Rgn, " & _
"case when (substr(ProdNo,-3) between 100 and 199) then " &
Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(ProdNo,-3) between 200 and 299) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(ProdNo,-3)
between 300 and 399) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " when (substr(ProdNo,-3) between 400 and 499) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "4" & Chr(39) & " when
(substr(ProdNo,-3) between 500 and 599) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when (substr(ProdNo,-3)
between 600 and 699) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "6" & Chr(39) & " when (substr(ProdNo,-3) between 700 and 799) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "7" & Chr(39) & " when
(substr(ProdNo,-3) between 800 and 899) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "8" & Chr(39) & " end as LineOfBus " & _
"from sc.tblOrdersCur Yr"

Debug.Print strSQL

With cnn
.ConnectionStri ng = strOleDBOrcCnn
.Open
End With

Set rst = New ADODB.Recordset

rst.Open CStr(strSQL), cnn, adOpenKeyset, adLockOptimisti c

Set rst = Nothing
Set cnn = Nothing
End Sub
=============== ======<End Code<========== =============== ============<
Connection string (w/code):
=============== ======>Begin Code>========== =============== ==========>
Public strOleDBOrcCnn As String

Sub SetConStr()
Dim strUID As String, strPswd As String

Forms("frmMain" ).Refresh

strOleDBOrcCnn = ""

If IsNull(Forms("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If

strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value

strOleDBOrcCnn = "Provider = OraOLEDB.Oracle ;" & _
"Data Source = XE;" & _
"User Id = " & strUID & ";" & _
"Password = " & strPswd
End Sub
=============== ======<End Code<========== =============== ============<
--
Regards,

Greg Strong
Jul 30 '06 #9
It is even wrong to use the ; as it's a SQLPlus "only" character used to
mark the end of a statement & not a part of SQL
Note however that you must (offcourse) use it in (anonymous) PL/SQL Blocks
ie

BEGIN
INSERT INTO ATABLE(AFIELD) VALUES(AVALUE);
COMMIT;
END

HTH

Pieter

"Greg Strong" <ne*********@ge edubeeu.com.inv alidwrote in message
news:c7******** *************** *********@4ax.c om...
On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>>The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.

On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TI****@PurpleP andaChasers.Moe rtheriumwrote:
>>The SQL looks all right to me, though that's just a quuick glance
through it. However, if you're getting results in SQL Plus, then you
shuld be fine.

Well I banged my head long enough on DAO. After reading some threads on
DAO and its relationship to Jet, I thought ADO probably was a better way
to go. I've got it to work with ADO with the OLEDB provided with Oracle
Express. Couple thanks here to several sources. First an excellent post
by an Oracle Express user on Oracle Technology Network per
http://forums.oracle.com/forums/thre...79316&#1279316.
Also many thanks to Access 2k2 Enterprise Developer's Handbook which
provided some great information. I've also learned that in ADO you don't
need the semi-colon at the end. So here is the test that worked:

=============== ======>Begin Code>========== =============== ==========>
Sub TestADO_DLL_SQL ()
Dim cnn As ADODB.Connectio n
Dim strSQL As String
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connectio n

Call SetConStr

'works short version / test
' strSQL = "create or replace view sc.vw_tblorders curyr_test as " & _
' "select orderno, pdneworder, estrev, estcgs, " & _
' "case when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 100 and 199" & Chr(41) & " then " & Chr(39) & "bu1"
& Chr(39) & " when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 200 and 299" & Chr(41) & " then " & Chr(39) & "bu2"
& Chr(39) & " when " & Chr(40) & "substr" & Chr(40) & "buscode,-3" &
Chr(41) & " between 300 and 399" & Chr(41) & " then " & Chr(39) & "bu3"
& Chr(39) & " end as busunit " & _
' "from sc.tblorderscur yr"

strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrders CurYr_rev2 AS " & _
"SELECT OrderNo, PdNewOrder, EstRev, EstCGS, " & _
"case when (substr(BusCode ,-3) between 100 and 199) then " &
Chr(39) & "BU" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BusCode ,-3) between 200 and 299) then " & Chr(39) & "BU" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BusCode ,-3)
between 300 and 399) then " & Chr(39) & "BU" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " end as BusUnit, " & _
"case when Length(MktCode) = 4 then case when
(substr(MktCode ,-2) between 10 and 19) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "1" & Chr(39) & " when (substr(MktCode ,-2) between 20
and 29) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "2" &
Chr(39) & " when (substr(MktCode ,-2) between 30 and 39) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "3" & Chr(39) & " when
(substr(MktCode ,-2) between 40 and 49) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "4" & Chr(39) & " when (substr(MktCode ,-2) between 50
and 59) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "5" &
Chr(39) & " when (substr(MktCode ,-2) between 60 and 69) then " & Chr(39)
& "VM" & Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when
(substr(MktCode ,-2) between 70 and 79) then " & Chr(39) & "VM" & Chr(39)
& "||" & Chr(39) & "7" & Chr(39) & " when (substr(MktCode ,-2) between 80
and 89) then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "8" &
Chr(39) & " when (substr(MktCode ,-2) between 90 and 99) then " _
& Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "9" & Chr(39) & "
when (substr(MktCode ,-2) between 100 and 109) then " & Chr(39) & "VM" &
Chr(39) & "||" & Chr(39) & "10" & Chr(39) & " end when Length(MktCode) =
5 then " & Chr(39) & "VM" & Chr(39) & "||" & Chr(39) & "10" & Chr(39) &
" end as VertMkt, " & _
"case when (substr(BrchNo,-4) between 1500 and 1550) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(BrchNo,-4) between 2500 and 2550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(BrchNo,-4)
between 3500 and 3550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "3" & Chr(39) & " when (substr(BrchNo,-4) between 4500 and
4550) then " & Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "4" &
Chr(39) & " when (substr(BrchNo,-4) between 5500 and 5500) then " &
Chr(39) & "Rgn" & Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when
(substr(BrchNo,-4) between 6500 and 6550) then " & Chr(39) & "Rgn" &
Chr(39) & "||" & Chr(39) & "6" & Chr(39) & " when (substr(BrchNo,-4)
between 7500 and 7550) then " & Chr(39) & "Rgn" & Chr(39) & "||" &
Chr(39) & "7" & Chr(39) & " end as Rgn, " & _
"case when (substr(ProdNo,-3) between 100 and 199) then " &
Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "1" & Chr(39) & " when
(substr(ProdNo,-3) between 200 and 299) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "2" & Chr(39) & " when (substr(ProdNo,-3)
between 300 and 399) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "3" & Chr(39) & " when (substr(ProdNo,-3) between 400 and 499) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "4" & Chr(39) & " when
(substr(ProdNo,-3) between 500 and 599) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "5" & Chr(39) & " when (substr(ProdNo,-3)
between 600 and 699) then " & Chr(39) & "LOB" & Chr(39) & "||" & Chr(39)
& "6" & Chr(39) & " when (substr(ProdNo,-3) between 700 and 799) then "
& Chr(39) & "LOB" & Chr(39) & "||" & Chr(39) & "7" & Chr(39) & " when
(substr(ProdNo,-3) between 800 and 899) then " & Chr(39) & "LOB" &
Chr(39) & "||" & Chr(39) & "8" & Chr(39) & " end as LineOfBus " & _
"from sc.tblOrdersCur Yr"

Debug.Print strSQL

With cnn
.ConnectionStri ng = strOleDBOrcCnn
.Open
End With

Set rst = New ADODB.Recordset

rst.Open CStr(strSQL), cnn, adOpenKeyset, adLockOptimisti c

Set rst = Nothing
Set cnn = Nothing
End Sub
=============== ======<End Code<========== =============== ============<
Connection string (w/code):
=============== ======>Begin Code>========== =============== ==========>
Public strOleDBOrcCnn As String

Sub SetConStr()
Dim strUID As String, strPswd As String

Forms("frmMain" ).Refresh

strOleDBOrcCnn = ""

If IsNull(Forms("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If

strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value

strOleDBOrcCnn = "Provider = OraOLEDB.Oracle ;" & _
"Data Source = XE;" & _
"User Id = " & strUID & ";" & _
"Password = " & strPswd
End Sub
=============== ======<End Code<========== =============== ============<
--
Regards,

Greg Strong
--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!
Jul 30 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
6773
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
11
17566
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless. I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The network connection is very...
14
9050
by: jj | last post by:
Is it possible to call a remote php script from within Access? I'm thinking something like: DoCMD... http://www.domain.com/scripts/dataquery.php DoCmd.OpenQuery "update_data", acNormal, acEdit ..... So the PHP script does something on the server database, then when a linked table is viewed within access, the data changes have been made?
0
1823
by: LesM | last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002 SP3. I have Progress table that is linked via ODBC into Access using OpenLink Lite for Progress 9.0b. For over a year, using Access 2000 under Win98, I have been running a Make Table or Append query against this ODBC table and producing an extracted Access table. Any fields that are NULL on the source ODBC table show as NULL on the output table. The query also has an...
14
10138
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought I should give back to the community by posting our findings. Thanks you all for all your help till now by posting problems and their solutions. ~Abhijit
8
4040
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked table in access, the total number of records is OK but some records appear several times and some records do not appear at all. It seems as if access or the ODBC drivers returns several times the same record and skips some of the records, curiosly...
7
11831
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For tables that involve a number field as the primary key, the data is returned successfully. For tables that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary key, I have the correct number of rows returned, but the data displayed...
8
9633
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 work from
11
16328
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction. We have been using oracle client 10.1.0.2 with it's odbc for a while without problem. The problem arose when we decided to reconnect all the tables and save password. Some query became suddenly very slow. Then I've discovered that the tables...
0
8699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9064
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7932
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5941
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4456
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4713
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3151
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2508
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2097
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.