Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20

    Unhappy Unanswered: VBA - SQL query returning only 1 row from Oracle

    Am trying to retrieve data from an Oracle 9 db instead of Oracle 7. Using Microsoft Driver for Oracle. (Have not tried using Oracle 9 driver)
    Using DAO 3.6 Library
    Access 2000 db

    The SQL when run through VBA only returns one row; however when it is run as a Pass Through Query on query tab it returns multiple rows. The correct result is multiple rows.

    Any suggestions on how to return more than 1 row with the VBA?
    I inherited this

    Code is as follows:

    'Import Oracle Data for SUInForce, SUPremium and TrtyPlanProc tables.
    Set qdfOracleData = dbAccess.CreateQueryDef("")
    qdfOracleData.Connect = strConnect
    qdfOracleData.SQL = "select si.* from summary_inforce si, trty_plan_proc tpp, (select control_date from CONTROL_DATE where control_date_type='SU') x " _
    & "where tpp.activity_date = x.control_date and si.summ_control_id = tpp.summ_control_id " _
    & "and billing_method in (3, 4) and region <> 40 and activity_type in ('A', 'C', 'E', 'S') and due_date between '" _
    & strStartDate & "' and '" & strEndDate & "'"
    qdfOracleData.ReturnsRecords = True
    Set rstOracleData = qdfOracleData.OpenRecordset(dbOpenSnapshot)

    Set rstAccessData = dbAccess.OpenRecordset("SUInForce", dbOpenDynaset)
    Do Until rstOracleData.EOF
    With rstAccessData
    .AddNew
    ' place data in several columns.....

    .Update
    End With
    rst.MoveNext
    Loop

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: VBA - SQL query returning only 1 row from Oracle

    Originally posted by agduga
    Am trying to retrieve data from an Oracle 9 db instead of Oracle 7. Using Microsoft Driver for Oracle. (Have not tried using Oracle 9 driver)
    Using DAO 3.6 Library
    Access 2000 db

    The SQL when run through VBA only returns one row; however when it is run as a Pass Through Query on query tab it returns multiple rows. The correct result is multiple rows.

    Any suggestions on how to return more than 1 row with the VBA?
    I inherited this

    Code is as follows:

    'Import Oracle Data for SUInForce, SUPremium and TrtyPlanProc tables.
    Set qdfOracleData = dbAccess.CreateQueryDef("")
    qdfOracleData.Connect = strConnect
    qdfOracleData.SQL = "select si.* from summary_inforce si, trty_plan_proc tpp, (select control_date from CONTROL_DATE where control_date_type='SU') x " _
    & "where tpp.activity_date = x.control_date and si.summ_control_id = tpp.summ_control_id " _
    & "and billing_method in (3, 4) and region <> 40 and activity_type in ('A', 'C', 'E', 'S') and due_date between '" _
    & strStartDate & "' and '" & strEndDate & "'"
    qdfOracleData.ReturnsRecords = True
    Set rstOracleData = qdfOracleData.OpenRecordset(dbOpenSnapshot)

    Set rstAccessData = dbAccess.OpenRecordset("SUInForce", dbOpenDynaset)
    Do Until rstOracleData.EOF
    With rstAccessData
    .AddNew
    ' place data in several columns.....

    .Update
    End With
    rst.MoveNext
    Loop
    Ok. First thing that confuses me is the reference to the rst.MoveNext when rst is not a recordset in your code. I'm assuming that you just typed it incorrectly for posting here...

    Are you determining that you are returning only one row because of the resulting data in the Access database, or have you used the Immediate window or a message box to get the recordcount at some point?


    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20

    Re: VBA - SQL query returning only 1 row from Oracle

    Originally posted by basicmek
    Ok. First thing that confuses me is the reference to the rst.MoveNext when rst is not a recordset in your code. I'm assuming that you just typed it incorrectly for posting here...

    Are you determining that you are returning only one row because of the resulting data in the Access database, or have you used the Immediate window or a message box to get the recordcount at some point?


    Gregg

    sorry about the typo

    I am using a messagebox to get the recordcount. I stripped that piece out
    MsgBox ("Oracle Record Count = " + CStr(rstOracleData.RecordCount) + ".")

    Here is the code again - been looking at this for too long today:

    Set qdfOracleData = dbAccess.CreateQueryDef("")
    qdfOracleData.Connect = strConnect
    qdfOracleData.SQL = "select si.* from summary_inforce si, trty_plan_proc tpp, (select control_date from CONTROL_DATE where control_date_type='SU') x " _
    & "where tpp.activity_date = x.control_date and si.summ_control_id = tpp.summ_control_id " _
    & "and billing_method in (3, 4) and region <> 40 and activity_type in ('A', 'C', 'E', 'S') and due_date between '" _
    & strStartDate & "' and '" & strEndDate & "'"
    qdfOracleData.ReturnsRecords = True
    Set rstOracleData = qdfOracleData.OpenRecordset(dbOpenSnapshot)

    'Stop
    Set rstAccessData = dbAccess.OpenRecordset("SUInForce", dbOpenDynaset)
    'Stop
    MsgBox ("Oracle Record Count = " + CStr(rstOracleData.RecordCount) + ".")
    Do Until rstOracleData.EOF
    With rstAccessData
    .AddNew
    !SUMM_CONTROL_ID = rstOracleData!SUMM_CONTROL_ID
    !ACTIVITY_DATE = rstOracleData!ACTIVITY_DATE
    !DUE_DATE = rstOracleData!DUE_DATE
    !activity_type = rstOracleData!activity_type
    !sequence_num = rstOracleData!sequence_num
    !new_business_ctr = rstOracleData!new_business_ctr
    !new_business_amt = rstOracleData!new_business_amt
    !NOT_TAKEN_OUT_CTR = rstOracleData!NOT_TAKEN_OUT_CTR
    !not_taken_out_amt = rstOracleData!not_taken_out_amt
    !reinstatements_ctr = rstOracleData!reinstatements_ctr
    !reinstatements_amt = rstOracleData!reinstatements_amt
    !admin_new_bus_ctr = rstOracleData!admin_new_bus_ctr
    !admin_new_bus_amt = rstOracleData!admin_new_bus_amt
    !lapses_ctr = rstOracleData!lapses_ctr
    !lapses_amt = rstOracleData!lapses_amt
    !recaptures_ctr = rstOracleData!recaptures_ctr
    !recaptures_amt = rstOracleData!recaptures_amt
    !coi_surrenders_ctr = rstOracleData!coi_surrenders_ctr
    !coi_surrenders_amt = rstOracleData!coi_surrenders_amt
    !deaths_ctr = rstOracleData!deaths_ctr
    !deaths_amt = rstOracleData!deaths_amt
    !expiries_ctr = rstOracleData!expiries_ctr
    !expiries_amt = rstOracleData!expiries_amt
    !admin_lapses_ctr = rstOracleData!admin_lapses_ctr
    !admin_lapses_amt = rstOracleData!admin_lapses_amt
    !increase_dec_ctr = rstOracleData!increase_dec_ctr
    !INCREASE_DEC_AMT = rstOracleData!INCREASE_DEC_AMT
    !inforce_ctr = rstOracleData!inforce_ctr
    !inforce_amt = rstOracleData!inforce_amt
    .Update
    End With
    rstOracleData.MoveNext
    Loop

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: VBA - SQL query returning only 1 row from Oracle

    Originally posted by agduga
    sorry about the typo

    I am using a messagebox to get the recordcount. I stripped that piece out
    MsgBox ("Oracle Record Count = " + CStr(rstOracleData.RecordCount) + ".")

    Here is the code again - been looking at this for too long today:

    Set qdfOracleData = dbAccess.CreateQueryDef("")
    qdfOracleData.Connect = strConnect
    qdfOracleData.SQL = "select si.* from summary_inforce si, trty_plan_proc tpp, (select control_date from CONTROL_DATE where control_date_type='SU') x " _
    & "where tpp.activity_date = x.control_date and si.summ_control_id = tpp.summ_control_id " _
    & "and billing_method in (3, 4) and region <> 40 and activity_type in ('A', 'C', 'E', 'S') and due_date between '" _
    & strStartDate & "' and '" & strEndDate & "'"
    qdfOracleData.ReturnsRecords = True
    Set rstOracleData = qdfOracleData.OpenRecordset(dbOpenSnapshot)

    'Stop
    Set rstAccessData = dbAccess.OpenRecordset("SUInForce", dbOpenDynaset)
    'Stop
    MsgBox ("Oracle Record Count = " + CStr(rstOracleData.RecordCount) + ".")
    Do Until rstOracleData.EOF
    With rstAccessData
    .AddNew
    !SUMM_CONTROL_ID = rstOracleData!SUMM_CONTROL_ID
    !ACTIVITY_DATE = rstOracleData!ACTIVITY_DATE
    !DUE_DATE = rstOracleData!DUE_DATE
    !activity_type = rstOracleData!activity_type
    !sequence_num = rstOracleData!sequence_num
    !new_business_ctr = rstOracleData!new_business_ctr
    !new_business_amt = rstOracleData!new_business_amt
    !NOT_TAKEN_OUT_CTR = rstOracleData!NOT_TAKEN_OUT_CTR
    !not_taken_out_amt = rstOracleData!not_taken_out_amt
    !reinstatements_ctr = rstOracleData!reinstatements_ctr
    !reinstatements_amt = rstOracleData!reinstatements_amt
    !admin_new_bus_ctr = rstOracleData!admin_new_bus_ctr
    !admin_new_bus_amt = rstOracleData!admin_new_bus_amt
    !lapses_ctr = rstOracleData!lapses_ctr
    !lapses_amt = rstOracleData!lapses_amt
    !recaptures_ctr = rstOracleData!recaptures_ctr
    !recaptures_amt = rstOracleData!recaptures_amt
    !coi_surrenders_ctr = rstOracleData!coi_surrenders_ctr
    !coi_surrenders_amt = rstOracleData!coi_surrenders_amt
    !deaths_ctr = rstOracleData!deaths_ctr
    !deaths_amt = rstOracleData!deaths_amt
    !expiries_ctr = rstOracleData!expiries_ctr
    !expiries_amt = rstOracleData!expiries_amt
    !admin_lapses_ctr = rstOracleData!admin_lapses_ctr
    !admin_lapses_amt = rstOracleData!admin_lapses_amt
    !increase_dec_ctr = rstOracleData!increase_dec_ctr
    !INCREASE_DEC_AMT = rstOracleData!INCREASE_DEC_AMT
    !inforce_ctr = rstOracleData!inforce_ctr
    !inforce_amt = rstOracleData!inforce_amt
    .Update
    End With
    rstOracleData.MoveNext
    Loop
    Let me continue with the questions. Are you using the MsgBox as the only indicator for the recordcount? A snapshot will return a recordcount of 1. I just tested this to be sure. But, it will let you loop using the method that you are using and increment through the recordset.

    Have you tried a less restrictive SQL statement to see if for some reason it is in the SQL statement? You know like "Select * from table"?

    I saw that you checked via a pass through but that's what I would do.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •