ODBC Max Length of Pass Through Query with Access 2k2? Greg Strong <news.REMOVE[ at ]geedubeeu.com.invalid> 13.07.2006 05:13:09 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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> 13.07.2006 13:03:28 Greg Strong wrote: [Quoted Text] > 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 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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Greg Strong <news.REMOVE[ at ]geedubeeu.com.invalid> 17.07.2006 03:51:47 On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: [Quoted Text] >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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> 17.07.2006 03:54:43 Greg Strong wrote: [Quoted Text] > "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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> 17.07.2006 03:58:11 Greg Strong wrote: [Quoted Text] > "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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Greg Strong <news.REMOVE[ at ]geedubeeu.com.invalid> 19.07.2006 03:14:20 On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: [Quoted Text] >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

Re: ODBC Max Length of Pass Through Query with Access 2k2? "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please[ at ]online.replace.with.norway> 19.07.2006 09:18:06 All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using Transactions are therefore of no use Pieter "Greg Strong" <news.REMOVE[ at ]geedubeeu.com.invalid> wrote in message news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg[ at ]4ax.com... [Quoted Text] > On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall > <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: > >>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

Re: ODBC Max Length of Pass Through Query with Access 2k2? "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please[ at ]online.replace.with.norway> 19.07.2006 09:18:06 All DDL (CREATE/DROP/ALTER) commands will automatically commit. Using Transactions are therefore of no use Pieter "Greg Strong" <news.REMOVE[ at ]geedubeeu.com.invalid> wrote in message news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg[ at ]4ax.com... [Quoted Text] > On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall > <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: > >>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

Re: ODBC Max Length of Pass Through Query with Access 2k2? Greg Strong <news.REMOVE[ at ]geedubeeu.com.invalid> 30.07.2006 02:06:04 On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: [Quoted Text] >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![ at ]PurplePandaChasers.Moertherium> wrote: >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/thread.jspa?messageID=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").Value) 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

Re: ODBC Max Length of Pass Through Query with Access 2k2? "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please[ at ]online.replace.with.norway> 30.07.2006 10:51:00 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[ at ]geedubeeu.com.invalid> wrote in message news:c71oc25mb7rqmfcv402bulc9n9rbg6jm3f[ at ]4ax.com... [Quoted Text] > On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall > <TIMMY![ at ]PurplePandaChasers.Moertherium> wrote: > >>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![ at ]PurplePandaChasers.Moertherium> wrote: > >>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/thread.jspa?messageID=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").Value) 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!