Excel, Microsoft Jet and OLE-DB

[DevInstinct]

I use the Microsoft Jet engine to get data from Oracle directly into
Excel. It works fine with an ODBC connection but with very poor
performances (compared to incredible perfs with SQL Server, but I've
got to make this work with Oracle). I tried to specify the oledb
provider instead of ODBC, but without success. It looks like it only
works with odbc.

The connection is:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended
Properties="Excel 8.0;HDR=Yes;"

And the statement is:
INSERT INTO [Sheet99$] SELECT * FROM [odbc;Driver={Oracle in
OraHome92};DBQ=THIS.THAT;UId=SOMETHING;PWD=SOMETHING].THE_TABLE

If you have any idea as by what I should replace
[odbc;Driver={Oracle in
OraHome92};DBQ=THIS.THAT;UId=SOMETHING;PWD=SOMETHING]

to make it wirk with something else than ODBC, please let me know!

Thanks

 

[Bob Phillips]

Use the Oracle OLEDB provider. See
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForOracleFromOracle
for details

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DevInstinct]

Unfortunately, I tried it before and it's not working.

I tried:
INSERT INTO [Sheet99$] SELECT * FROM [Provider=OraOLEDB.Oracle;Data
Source=THIS.THAT;User Id=SOMETHING;Password=SOMETHING].THE_TABLE

and

INSERT INTO [Sheet99$] SELECT * FROM
[odbc;Provider=OraOLEDB.Oracle;Data Source=THIS.THAT;User
Id=SOMETHING;Password=SOMETHING].THE_TABLE

There either something in the FROM clause that I miss or it's just not
supported by the Microsoft Jet engine.

Any further help is welcome.

Thanks,
Martin

 

[Bob Phillips]

Why not just read it from the Oracle database and use VBA to dump the data
into Excel. Far simpler.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DevInstinct]

It's Server-side work, shipping documents over the Internet in Excel.
Can't use OWC either :-(

Thanks if you have anything else to suggest...

Martin

 

[Bob Phillips]

But you still have Excel on the server, otherwise how can you create Excel
docs over there?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DevInstinct]

I could use Excel on the server, but Microsoft doesn't recommend it.
Using OLE-DB allows me not to make any COM calls. It doesn't requires
Excel to be on the server either. That's _really_ cool! I append data
to an already existing document, so I don't need to create the file,
just make a copy of the original file, modify it, ship it.

But then, the perfs with the Oracle ODBC are my problem in the topic
above.
I did tried ADODB and CopyFromRecordset from the COM component. Perfs
are bad too (compared to same request using SQL Server).

Martin

 

[Bob Phillips]

Martin,

I contacted a colleague, Dennis Wallentin, who is a bit more familiar with
Oracle than I and posed your problem to him. This was his response

========================================
The approach to directly include the source(s) in SQL statements is good
for textfiles and other sources simple datasources but not with RDBMS. At
least, not in my opinion.

One major explanation to the poor performance is that the call is done via
the "bridge" ODBC to the OCI (Oracle Call Interface). Of course, the use of
the Oracle DB Provider will improve the performance considerable (at least
it should in theory).

I've notice that the poster only mention poor performance but not anything
about errors.

The following syntax is the standard for Oracle DB Provider and he may try
to add brackets around the table name:

INSERT INTO [Sheet99$] SELECT * FROM
[Provider=OraOLEDB.Oracle;Data Source=Database;User
Id=Username;Password=secret;].[The_Table]

Next solution may appear as a "wild thing" but...

If the person experience good performance with SQL Server then it may be
possible for him to link the Oracle database's table like the following:

"Select * FROM linkedserver.oracledatabase.dbowner.tablename"

to the SQL Server database and then create the INSERT INTO SQL statement
with reference to the SQL Server. He can use a temporary table for this
task.

==========================================

I know not if that helps you or not, but if you want to follow it up, I
suggest that I step out of the loop, and you post your query to VBAExpress,
www.VBAExpress.com, or OzGrid, www.OzGrid.com, a couple of forums that
Dennis frequents, and he may pick it up and run with you on it.

Best of luck.


--

HTH

RP
(remove nothere from the email address if mailing direct)