Access 2000 front, Oracle tables ado connection

I need to run a series of queries to get my final result. these queries are quite large and either make table or update queries. There are many fields involved too. When I write the code in to VBA to run from ADO, Ihave too many characters and the code errors out. Any suggestions?

someone has to know

Need more info to help - what are your queries and what errors are you getting?

Yup. A simple one. Create a view or views on the Oracle box. You can make views that use user-written functions to return 'fields'.

In fact, having the activity server-side improves throughput.

Then all you have to do is: SELECT * from myView

For example - here is something we commonly do with Oracle views to be abvle to retreive the rows we want efficiently:

Code:

---

CREATE OR REPLACE VIEW datamart.uzvtrev AS
  SELECT
                    uabopen_cust_code                                      uzvtrev_cust_code,
                  uabopen_prem_code                                    uzvtrev_prem_code,
                  uabopen_serv_num                                      uzvtrev_serv_num,
                  uabopen_charge_date                                  uzvtrev_charge_date,
                  uabopen_srat_code                                    uzvtrev_srat_code,
                    uabopen_scat_code                                    uzvtrev_scat_code,
                    uabopen_ar_trans                                      uzvtrev_ar_trans,
                    uabopen_chrg_calc_num                                uzvtrev_chrg_calc_num,
                    ucbprem_rev_rep_loc_id                                uzvtrev_rev_rep_loc_id,
                    ucrserv_scls_code                                    uzvtrev_scls_code,
                    uabopen_printed_date                                  uzvtrev_printed_date,
                    SYSDATE                                              uzvtrev_gl_end_date,
                    b.utvsrat_asvc_code                                  uzvtrev_srv_asvc_code,
                    a.utvsrat_asvc_code                                  uzvtrev_opn_asvc_code,
                    utrsrat_tax_ind                                      uzvtrev_tax_ind,
                  uabopen_billed_chg                                    uzvtrev_billed_chg,
                  uabopen_balance                                      uzvtrev_balance,
                  uabopen_bad_debt_status_code                          uzvtrev_bd_stat_code,
                  uabopen_bd_amount                                    uzvtrev_bd_amount,
                  uabopen_bd_balance                                    uzvtrev_bd_balance,
                  uabopen_recoverable_amount                            uzvtrev_bd_recov_amt,
                  SYSDATE                                              uzvtrev_activity_date,
                    USER                                                  uzvtrev_user_id,
                  NULL                                                uzvtrev_business_post_date,
                  'O'                                                  uzvtrev_record_type,
                  NULL                                                uzvtrev_adj_date,
                  NULL                                                uzvtrev_origin_ar_trans,
                  NULL                                                uzvtrev_adjm_code,
                  NULL   
                    SELECT  DISTINCT utrsrat_glcl_code
            INTO tmp
            FROM uimsmgr.utrsrat 
            WHERE 
                utrsrat_srat_code = srat
            AND utrsrat_scat_code = scat
            AND utrsrat_effect_date
                  IN
              (SELECT  MAX(utrsrat_effect_date)
                FROM
                      uimsmgr.utrsrat
                WHERE
                      utrsrat_srat_code = srat
                  AND utrsrat_scat_code = scat
                  AND utrsrat_effect_date <= indate);                                                                                      uzvtrev_gl_class_ind,
                  uzrtrev_get_gl_a(uabopen_scat_code,uabopen_srat_code,uabopen_charge_date)                    uzvtrev_ban_gl_acct_a,
                  uzrtrev_get_gl_b(uabopen_scat_code,uabopen_srat_code,uabopen_charge_date)                    uzvtrev_ban_gl_acct_b,
                  uzrtrev_get_rev_category(uabopen_scat_code,uabopen_srat_code,uabopen_charge_date)            uzvtrev_rev_category,
                  uzrtrev_get_glcl(uabopen_scat_code,uabopen_srat_code,uabopen_charge_date)                    uzvtrev_glcl_code
 FROM
        utvsrat a,
        utvsrat b,
        utrsrat,
        ucbprem,
        ucrserv,
        uabopen,
        uzttrow
 WHERE
        uzttrow_table_code = 'O'                    AND
        uabopen.rowid = chartorowid(uzttrow_row_id)  AND
        ucrserv_prem_code(+) =uabopen_prem_code      AND
        ucrserv_num(+) =uabopen_serv_num            AND
        b.utvsrat_code(+) =ucrserv_srat_code        AND
        ucbprem_code(+) =uabopen_prem_code          AND
        a.utvsrat_code(+) =uabopen_srat_code        AND
        utrsrat_srat_code(+) =uabopen_srat_code      AND
        utrsrat_scat_code(+) =uabopen_scat_code      AND
        utrsrat_temp_rate_ind(+) = 'P'              AND
        utrsrat_effect_date(+) <= to_date(uabopen_charge_date) AND
        utrsrat_nchg_date(+) >= to_date(uabopen_charge_date);


COMMIT;

-- @(#)PNM $Header:  $
--
--  uzvtrev_load.sql - This SQL script loads the uzvtrev table with revenue data
--              from GL.
--
--  Date    Name      Description
-- --------  ---------  -------------------------------------------------
--
-- 05/24/02  jmc        Primary datamart table load
--
--
--
--

---

Select * from uzvtrev; does it all

Thanks, but let me get more specific. I am using

Access 2000 front end

Linking via ADO to oracle tables housed somewhere else

I do not have access to an Oracle Box

With out connecting I have several queries that make a table, and update the table. These queries do not run with ado (I knew that already) I wrote an sql statement to pass to oracle and that didnt work either


mysql = "INSERT INTO LD_ClosedData (15 fields) 15 fields
"FROM table1 LEFT JOIN tabel 2 ON table1.TKTNUM = table2.TKTNUM " & _
"WHERE several criteria"


I need to append data into a local access table to run queries from

Well, the thing is, you DO have access to the Oracle box, using ADO. As Jim suggests, try and create a View in Oracle using ADO (which is a similar concept to creating a Query in Access).
You can write Sotrd Procedures or Views from ADO using the ADODB.Command method (search this forum for more details) - in reality, you should try and get access to SQL*Plus (oracle interface) or TOAD (3rd party) if you are going to do a serious job on this...

Thanks for the info. Learn something new every day especially when one know nothing about the topic.