Access 2000 front, Oracle tables ado connection

[swoozie]

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?

[swoozie]

someone has to know

[cim3]

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

[jim mcnamara]

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:

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

[swoozie]

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

[Gaffer]

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

[swoozie]

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