Re: Newbie Question...HELP!!!

From: Mario Zgela <mario_at_hnb.hr>
Date: 7 Jan 1999 15:06:53 GMT
Message-ID: <01be3a50$32e7cf80$236faaaa_at_mzgela>


Jeremy Dorin <jeremy_dorin_at_dpc.senate.gov> wrote in article <369370F5.D3AF5218_at_dpc.senate.gov>...
> I have a Oracle Database in which one table consists of an index term
> and a variable which stores many paragraphs of text. I am trying to
> export this into Microsoft Access.
>
> I have a little bit of a problem as the machine with Access cannot
> connect to the machine with Oracle. Instead, I have a third machine
> which connects to both and has established a ODBC connection to both the
> Oracle and my Access databases.

You claim you have third machine with Access and ODBC connection to Oracle. Why don't you
simply connect on Oracle using that third machine and attach Oracle table to Access database.
After that you can create Access table (using make-table query) from this attached Oracle table.
The final step is to export that Access table to separate Access database and copy that database
(which consists of just one table) it to second machine. Maybe this is not possible because I didn't understood your problem. So, if there is no ODBC connection to
Oracle database from Access on third machine, you can do table export to ASCII file (fixed width). You can do it from within Oracle SQL*Plus on Oracle machine by using spooling technique:

  1. Execute following commands:
SQL>	SET ECHO OFF;
SQL>	SET NEWPAGE 0;
SQL>	SET SPACE 0;
SQL>	SET PAGESIZE 0;
SQL>   	SET FEEDBACK OFF;
SQL> 	SET HEADING OFF;

2. assign file name which will be filled with table data:

SQL> spool table_content.txt;

3. issue the SQL command that will execute query and fill the table_content.txt file:

SQL> SELECT * FROM your_table;

After this you will get ASCII file named table_content.txt which is stored in SQL*Plus start in directory (in windows95: \ORAWIN95\BIN)

4. stop the spooling:

SQL> spool off;

Now, you can copy file table_content.txt to machine with Access, and do the import into Access.
In Access97 it's done with File->Get external data->Import. In drop down list "files of type"
you choose "Text files" and select table_content.txt file. The import wizard will lead you
thru the rest of process.

>
> My strategy is to try to export the Oracle table into Access format and
> then simply ftp it over to the Access machine and open it. However, I
> do not know how to export into Access without having Access.
>
> My Questions:
> 1) Is there a way to export tables from Oracle 7.1 NT Server to Access
> Files without Access?
>
> 2) Is there a better way to do this? (I'm sure there is, but it
> continues to allude me).
>
> Thank you very much,
> Jeremy Dorin
> jeremy_dorin_at_dpc.senate.gov
>
>
Received on Thu Jan 07 1999 - 16:06:53 CET

Original text of this message