Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Greg Strong, Jul 28, 2006.

  1. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    General Max Arthur Macauliffe History of Sikhism Sep 4, 2013
    SciTech Mammals pick offspring’s sex to maximize number of grandchildren, study shows Breaking News Jul 11, 2013
    Events Year 2013 is centennial to honour max arthur macauliffe History of Sikhism May 8, 2013
    Sikhi To The Max developers Information Technology Mar 26, 2013
    NRI brides from region face max desertions Hard Talk Jan 22, 2010

  3. Tim Marshall

    Tim Marshall
    Expand Collapse
    Guest

    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 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
     
  4. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    On Thu, 13 Jul 2006 10:33:28 -0230, Tim Marshall
    <TIMMY!@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.


    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
     
  5. Tim Marshall

    Tim Marshall
    Expand Collapse
    Guest

    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
     
  6. Tim Marshall

    Tim Marshall
    Expand Collapse
    Guest

    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
     
  7. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
    <TIMMY!@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
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg@4ax.com...
    > On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
    > <TIMMY!@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
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    news:mc7rb29v625ejkk9ss04ls7n7q8vue5nvg@4ax.com...
    > On Mon, 17 Jul 2006 01:28:11 -0230, Tim Marshall
    > <TIMMY!@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
     

Share This Page