 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / May 2007how do I link to Oracle
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 > Hello, > [quoted text clipped - 6 lines] > > thanks 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 > Hi > [quoted text clipped - 22 lines] > > > > thanks 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 > Hi > [quoted text clipped - 22 lines] > > > > thanks You have to install the ORACLE connection software > Hello, > [quoted text clipped - 6 lines] > > 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 > You have to install the ORACLE connection software > [quoted text clipped - 8 lines] > > > > thanks When I did it years ago the software was part of the oracle installation disks. > Eric, > [quoted text clipped - 18 lines] > > > > > > thanks 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 > Eric, > [quoted text clipped - 18 lines] > > > > > > thanks 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 > Eric, > [quoted text clipped - 18 lines] > > > > > > thanks You were right on the money. I picked the MDB, did the LINK, and it works like a charm. Thanks for your help! Russ > One last thing... > [quoted text clipped - 36 lines] > > > > > > > > thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.