how do I link to Oracle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have Access2003. I'm creating queries in Excel that run against an ODBC
connection to Oracle. This works fine, but the error messages are so
primative that I thought I'd try to use Access to run them instead. When I
try to do this I get a message stating that I have to be connected to a SQL
Server. Is it true that in order to connect to Oracle from Access I have to
be connected to SQL Server?

thanks
 
Hi

No, you don't need to be connected to a SQL server.

If the ODBC to Oracle is set up correctly and working you shouldn't have any
problems.

I connect to Oracle via ODBC from Access - by linking the tables needed for
my queries.

What is the exact error message?

Regards

Andy Hull
 
Thanks for replying Andy. I can't run it from home. I'll get you the exact
message after I run it again at work on Tue.

thx
 
After I select File/Get External Data/Import/ODBC DBs, I select my ODBC
connection and enter the userid/pw. At this point it brings back a list of
the tables in that DB. If I select a table a Dialog pops up and reads:

"Access was not able to perform this operation because the project is not
connected to a SQL Server database."

thanks
 
Eric,

So when connecting to Oracle via ODBC I need another product? I thought the
whole point of ODBC was to abstract the DB. Can you describe the set up
further or point me to where I can get more details?

Thanks,

Russ
 
Hi

Now I understand!

Your connection to Oracle is fine else you wouldn't get to see the list of
tables - they are the right tables aren't they?

The problem is that you are working with or have created a new Access Data
Project (ADP) which is a type of Access database that stores its tables in a
SQL Server database. So at the point that you're telling Access to go get the
Oracle data and store it in Access it wants to connect to its SQL database to
do that.

Either, you're meaning to work with an ADP and therefore need to tell it the
SQL server...

Or, you need to create an Access database (.MDB file) and import your data
into that. When you launch Access and click File and New - choose Database
(not Project).

Hope this helps

Regards

Andy Hull
 
One last thing...

Consider Linnking your Oracle tables as opposed to importing them.

File - Get External Data - Link Tables...

Importing them will mean hauling a load of data across your network and
storing in your Access database - and then it won't be live and will have to
be re-imported sometime in the future.

Linking your tables is like a shortcut telling Access where the data is when
it needs it. Obviously, the data required by any queries has to be brought
across the network at runtime so there are pros and cons.

Regards

Andy Hull
 
You were right on the money. I picked the MDB, did the LINK, and it works
like a charm. Thanks for your help!

Russ
 
Back
Top