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_tblOrdersCurYr_rev2()
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

strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQueryDef("")
qdfPassThrew.Connect = "ODBC;" & strCnn
qdfPassThrew.SQL = strSQL
qdfPassThrew.ReturnsRecords = False
wsCur.BeginTrans
qdfPassThrew.Execute
wsCur.CommitTrans
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

Greg Strong wrote:

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

,----- [ 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 fSaveGraphDetails()

<snip procedure>

Exit_Proc:
Exit Function
Err_Proc:
Select Case Err.Number
Case Else
fError "frmTmadSave", "fSaveGraphDetails"
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.Description
Else
strMsgErr = Mid(strExplanation, int1, int2 - int1)
End If

Else
strMsgErr = "Oracle Error " & errX.Number & " " &
errX.Description
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

On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:

Quote:

>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

Greg Strong wrote:

Quote:

"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

Greg Strong wrote:

Quote:

"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

On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:

Quote:

>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_tblOrdersCurYr_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.Workspaces(0)
Set dbCur = wsCur.Databases(0)

Call SetConStr

strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr;"

' strSQL = "drop view sc.vw_tblOrdersCurYr_test;"

Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQueryDef("")
qdfPassThrew.Connect = "ODBC;" & strCnn
' qdfPassThrew.Connect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQL = strSQL
qdfPassThrew.ReturnsRecords = False
' wsCur.BeginTrans
qdfPassThrew.Execute
' wsCur.CommitTrans
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=====================<End Code<=====================================<

Thanks!

--
Regards,

Greg Strong

All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using
Transactions are therefore of no use

Pieter

"Greg Strong" <news.REMOVE@geedubeeu.com.invalidwrote in message
news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg@4ax.com...

Quote:

On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:
>

Quote:

>>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_tblOrdersCurYr_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.Workspaces(0)
Set dbCur = wsCur.Databases(0)
>
Call SetConStr
>
strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr;"
>
' strSQL = "drop view sc.vw_tblOrdersCurYr_test;"
>
Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQueryDef("")
qdfPassThrew.Connect = "ODBC;" & strCnn
' qdfPassThrew.Connect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQL = strSQL
qdfPassThrew.ReturnsRecords = False
' wsCur.BeginTrans
qdfPassThrew.Execute
' wsCur.CommitTrans
wsCur.Close
Set qdfPassThrew = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=====================<End Code<=====================================<
>
Thanks!
>
--
Regards,
>
Greg Strong

All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using
Transactions are therefore of no use

Pieter

"Greg Strong" <news.REMOVE@geedubeeu.com.invalidwrote in message
news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg@4ax.com...

Quote:

On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:
>

Quote:

>>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_tblOrdersCurYr_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.Workspaces(0)
Set dbCur = wsCur.Databases(0)
>
Call SetConStr
>
strSQL = "CREATE or REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr;"
>
' strSQL = "drop view sc.vw_tblOrdersCurYr_test;"
>
Debug.Print strSQL
'create query
Set qdfPassThrew = dbCur.CreateQueryDef("")
qdfPassThrew.Connect = "ODBC;" & strCnn
' qdfPassThrew.Connect = "ODBC;" & strMSOCnn
Debug.Print "ODBC;" & strCnn
qdfPassThrew.SQL = strSQL
qdfPassThrew.ReturnsRecords = False
' wsCur.BeginTrans
qdfPassThrew.Execute
' wsCur.CommitTrans
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

On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:

Quote:

>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
<TIMMY!@PurplePandaChasers.Moertheriumwrote:

Quote:

>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.Connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection

Call SetConStr

'works short version / test
' strSQL = "create or replace view sc.vw_tblorderscuryr_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.tblorderscuryr"

strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr"

Debug.Print strSQL

With cnn
.ConnectionString = strOleDBOrcCnn
.Open
End With

Set rst = New ADODB.Recordset

rst.Open CStr(strSQL), cnn, adOpenKeyset, adLockOptimistic

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("frmMain").Controls("txtUserID").Valu e) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("frmMain").Controls("txtPswd").Value) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If

strUID = Forms("frmMain").Controls("txtUserID").Value
strPswd = Forms("frmMain").Controls("txtPswd").Value

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

--
Regards,

Greg Strong

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" <news.REMOVE@geedubeeu.com.invalidwrote in message
news:c71oc25mb7rqmfcv402bulc9n9rbg6jm3f@4ax.com...

Quote:

On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
<TIMMY!@PurplePandaChasers.Moertheriumwrote:
>

Quote:

>>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
<TIMMY!@PurplePandaChasers.Moertheriumwrote:
>

Quote:

>>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.Connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
>
Call SetConStr
>
'works short version / test
' strSQL = "create or replace view sc.vw_tblorderscuryr_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.tblorderscuryr"
>
strSQL = "CREATE OR REPLACE VIEW sc.vw_tblOrdersCurYr_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.tblOrdersCurYr"
>
Debug.Print strSQL
>
With cnn
.ConnectionString = strOleDBOrcCnn
.Open
End With
>
Set rst = New ADODB.Recordset
>
rst.Open CStr(strSQL), cnn, adOpenKeyset, adLockOptimistic
>
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("frmMain").Controls("txtUserID").Valu e) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("frmMain").Controls("txtPswd").Value) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
>
strUID = Forms("frmMain").Controls("txtUserID").Value
strPswd = Forms("frmMain").Controls("txtPswd").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!

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

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

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

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

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about