Access - Oracle

Jun 27, 2001

Does anyone know if I can have a MS Access project with Oracle backend ?
I know it's a breeze with MS SQL backend !

Thanks in advance !

Ivan

View 1 Replies


ADVERTISEMENT

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.

what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?

View 2 Replies View Related

Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them

Mar 17, 2008

Access Connection

create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!

Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db

Any ideas please help

thanks in advance
Dave

View 2 Replies View Related

Why Can't T-SQL Access Oracle DB When VB6 Can?

Dec 18, 2007

Hi all,

SQL Server 2005 SP2
Windows XP Pro SP2
Oracle 10g Client 10.2
Oracle Data Access Components 10.2 VB6
Oracle Rdb 7.1 (not Oracle RDBMS).

I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...



con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=someusername;

Password=topsecret;Data Source=my_oledb"



I got this example VB appication off the net but have to admit I'm not sure of the difference between

OraOLEDB.Oracle.1 & OraOLEDB.Oracle.



The alias in TNSNAMES looks like this...



my_oledb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (Host = spike) (Port = 1527))
)
(CONNECT_DATA = (SERVICE_NAME = oci_oledb)
)
)



I can also TNSPING this alias without any probems.



My SQL server linked server is setup as follows.



exec sp_addlinkedserver
@server = 'MFP2',
@srvproduct='Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'my_oledb'


exec sp_addlinkedsrvlogin
@rmtsrvname='MFP2',
@useself='false',
@locallogin='sa',
@rmtuser='******',
@rmtpassword='******'



Using both openquery and four part naming (?) I get the following errors


select job_title from mfp2...jobs

OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".



There are no schemas in the Oracle database so I've just used three dots between the linked server and the table name.

Again I know this syntax works becuase I have used it sucessfully with ODBC drivers.



select * from openquery(mfp2,'select * from jobs')

OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".



The passwrods are correct on the Oracle database as these are the ones used in the VB6 application.



Trying to expand the catalogue also causes management studio to hang.



Anyone spot the obvious mistake?



I did read on the net a while ago that registry entries might be rquired on the PC/server, is this still necessary?



Thanks in advance



Dave

View 9 Replies View Related

VB Vs Access As A Front End To SQL, Oracle, Etc

Jan 18, 1999

Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.

Any ideas greatly appreciated.

View 2 Replies View Related

Access Rows In Oracle

Jun 8, 2008

Is there a way to access a particular row of a column in sql

lets say we have a table

A | B| C|
---------
1 | 2 | 3
3 | 5 | 6

And we want to access the 5 in column B. Is there a way to do that?

View 1 Replies View Related

Using DTS To Move Data From Oracle To Access

Apr 7, 2004

I'm attempting to move data from an Oracle table to an Access table using an Oracle stored procedure in DTS. The problem is that you can't pass parameters to an Oracle stored proc when its called in a data pump task. Is there a way to pass global variables into an Oracle stored procedure which retrieves data and moves it to an Access database? Maybe in an Active X task? We are required to use an Oracle stored procedure by our DBA's or else I'd just pass the variables into a SQL string and use it in the data pump task.

View 1 Replies View Related

Linking Form In MS Access To Oracle

May 9, 2008

hello

we suppose to link the form in MS Access with Oracle

can anyone tell me how to write the code for these buttons

i tried to write as I learned in VB.NET,
But it seems doesn't work :(

http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg

View 2 Replies View Related

Access Oracle Stored Procedure

Mar 27, 2007

How can I access Oracle stored procedure from MS SQL Server?

View 1 Replies View Related

Sql Server 2005 Sp2 And Oracle Access

Nov 21, 2006

When I try to create a model in Report Builder based on a connection to Oracle 10g database I get the following error.

ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

Is this a bug in the CTP version or am I missing something?

View 19 Replies View Related

How To Link SQL Server Tables To Access Or Oracle

Mar 7, 2000

Hi All

My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries.
I am thinking of linking server but I am not sure about that.
Is someone can tell me what to do and explain me how.
Thanks in advance
Sincerely.
David

View 2 Replies View Related

Unable To Access Oracle From Legacy DTS Designer.

Apr 11, 2008

I am unable to acces Oracle from legacy DTS designer, here is what I have done,

1. I have kept my old DTS packages in SQL2K5 legacy DTS container
2. Now I am unable to access oracle; it gives provider error
3. I am able to connect oracle from SQLPLUS

How can I troubleshoot this problem?

------------------------
I think, therefore I am - Rene Descartes

View 7 Replies View Related

Convert Ms Access Sql Staement To Oracle Sql Statement

Aug 31, 2005

Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)

View 1 Replies View Related

Recommendations For Oracle Access From SSIS 64-bit Server?

Dec 3, 2006

We're having issues with Microsoft's 64 bit Oracle OLE-DB driver... when we escalated to Microsoft PSS they recommended that we go with Oracle's OLE-DB driver since Microsoft's hasn't been modified in a few years.

I've seen a lot of differing opinions on this topic (almost everyone says do not use Oracle's driver) and am wondering if there's a definitive answer here.

Larry

View 1 Replies View Related

SQL 2012 :: XML / CLOB And BLOB Data Access From Oracle

Jun 10, 2014

How to access the XML, CLOB and BLOB Oracle data through linked server from SQL Server 2012?

I want to retrieve XML, CLOB and BLOB data from SQL Server 2012 through Oracle Linked server .

View 1 Replies View Related

Data Access :: Setting Up Oracle Linked Server

Sep 15, 2011

I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.

View 4 Replies View Related

Oracle Data Access Components (ODAC) For Windows

Mar 19, 2008


Can I create ODP.NET connection in my SSIS connection manager. I had downloaded and installed ODP.NET on my server provided by oracle. The idea is I need to test this provider and see what is the difference connecting oracle database and the data load speed.

thank you

View 4 Replies View Related

Data Access :: Return String From Oracle Anonymous Block

Oct 30, 2015

I am trying to execute oracle procedure (anonymous block) with input and output parameters from ms sql code and return 2 values back:a number and a string.I am using **EXEC() AT** to execute oracle code and return a string back to ms sql. It works fine when returning numbers, but if I add a string output parameter, I am receiving an error:Msg 0, Level 11, State 0, Line 0...A severe error occurred on the current command.  The results, if any, should be discarded.

The server log has the following entry in exception.txt:

Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 00000005031C07E0 at 0x0000000070F9C183
There is also a SQLDump file generated:
* BEGIN STACK DUMP:
*   10/28/15 12:45:38 spid 63
*   Exception Address = 000000005040C183 Module(MSVCR100+000000000003C183)

[code]....

It looks like the problem is in passing varchar2 from oracle into ms sql. I had logging code in oracle block, and it works fine. I have found a few examples of using **exec() at** but there is always only integer output passed back.

View 3 Replies View Related

Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle

Sep 12, 2007

I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.

View 4 Replies View Related

Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)

Apr 2, 2007

Hi!



I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:



TITLE: Microsoft Visual Studio
------------------------------

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:



1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.



2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.



Any help would be greatly appreciated. Thanks!

View 10 Replies View Related

Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2

Oct 26, 2006

Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?

I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.

View 1 Replies View Related

Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1

Feb 22, 2006

Hello,

On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.

I use one or the other according to my needs.

In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.

Almost everything works except Microsoft OLEDB provider for Oracle.

ssis packages on the test machine will return an error

Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.

Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".

I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.

Does anyone could point me to the right direction to solve this issue?

Thanks,

Philippe

View 17 Replies View Related

Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient

Jan 12, 2006

Hi,

I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client  10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was  ok.

 

from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->

the oracle publisher is displayed in the list of publisher but when press ok i got the following error :

TITLE: Distributor Properties
------------------------------

An error occurred applying the changes to the Distributor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Any idea about this error ?

Thanks

Tarek Ghazali
SQL Server MVP.


 

View 2 Replies View Related

Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used

May 11, 2007

Hi Everyone,



I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..



Source: MS SQL Server

Destination: Oracle 10g



The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand



OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.



Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?



If these are simple questions - my appologies, I am new to the product.



Best Regards,



Steve Collins

View 1 Replies View Related

Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle

Aug 23, 2007

Hi everybody,

I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.

Please suggest me wht should i do know?

Thanks in advance

Regards
Arvind L

View 3 Replies View Related

Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider

Jan 11, 2007

Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".

 

This is how I set up my Linked server:

Provider: "Oracle Provider for OLE DB"

Product Name: SomeServer

Data Source: SomeServer

Provider String:  "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"

 

 

The query I run is:

Select * from [Someserver].[schema or database]..[tbl_name]

 

Any help???  What am i missing?

View 3 Replies View Related

Oracle Parameterized Queries To Update Oracle Table Do Not Work

Apr 23, 2007

Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.



Anyone knows how to update an Oracle table through SSIS?



Thanks!

Wenbiao

View 5 Replies View Related

How To Migrate Oracle Applications 11.03/Oracle 8.05 To Navision 4.0/ms Sqlserver

Oct 29, 2005

need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000

tia

View 1 Replies View Related

Oracle Error ORA-12154 From An Application Which Never Uses Oracle

Mar 14, 2008

My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 =  "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified."   By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0 
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata

View 1 Replies View Related

Using SSIS For ETL From Oracle 10g ODS Into Oracle DW And From There Into SSAS Cubes

May 2, 2007

Hi,





This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?



Any caveats houghtscomments on doing this?



Thanks,

JGP

View 1 Replies View Related

Data Access :: Server Rejected The Connection - Access To Selected Database Has Been Denied

Jun 10, 2015

I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.

But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013.  The queries still work for users still using MS 2007. 

I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.

View 6 Replies View Related

Data Access :: Insert Rows To MS Access 2013 Without Listing Column Names

Nov 12, 2015

We need to insert data/rows from a SQL Server 2014 database into MS Access database.  The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.

Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem.  It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?

View 3 Replies View Related

Database Schemas And This Statement Has Attempted To Access Data Whose Access Is Restricted By The Assembly.

Jul 14, 2005

Hello.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved