Re: Getting data via ODBC

Access 2000 (and allegedly it hasn't changed much since 95):

Assuming you have data in Access that you want to get into an Oracle backend, highlight a table, click File - Export, and in the ensuing dialogue box, select for files of type 'ODBC databases'.

When you click OK, it will offer to save your table with the same name in the ODBC database. Click OK to that, and you then get the Data Source selection window. Select your backend database name from the list (mine is under 'Machine Data Source'), and click OK. You should then be prompted to log in to the Oracle database. After that, it should all be plain sailing -just repeat the process for all of your tables.

Just make sure that all your source tables and column names are stored in Access in UPPERCASE, otherwise you will have a devil of a job querying the damn things when they are safely inside Oracle (you'll have to do select * from 'Table_Name' instead of just select * from table_name -Oracle respects the case of the stuff coming in via ODBC, and 'table_name' is considered different to 'Table_Name', which wouldn't ordinarily be the case (bad pun, I agree).

As to how to set up your ODBC connection in the first place? Well, I use the W2K ODBC applet in the Control Panel (via Administrative Tools). Add a new source, and select for 'Oracle ODBC Driver" (don't mess with the Microsoft version -I've never got it to work yet!).

In the ensuing dialogue, I am sure there must be more scientific approaches, but trial and error have taught me to set the Data Source Name window to the Instance name, and the Service Name to exactly the same. Anything else, and I never seem to connect properly.

You are indeed a brave soul if you are going to try this with Lotus Notes. I guess the whole concept of ODBC suggests that it should all work much the same, but I don't rate your chances for total sanity at the end of a week of trying!

Hope it goes well, anyway
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------

"Mark" <cdo_at_lambic.co.uk> wrote in message news:39fa9e3a.1348715352_at_news...


> I'm working on an Oracle 8.0.5 database running in an AIX environment.

>

> Can anyone tell me how I would go about connecting to an ODBC data

> source (e.g. access or lotus notes) to pull data down into the oracle

> database?

>

> Thanks.

>

> --

> Mark Styles - Oracle/Unix developer and DBA

> http://www.lambic.co.uk