Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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=SOMETHI NG].THE_TABLE If you have any idea as by what I should replace [odbc;Driver={Oracle in OraHome92};DBQ=THIS.THAT;UId=SOMETHING;PWD=SOMETHI NG] to make it wirk with something else than ODBC, please let me know! Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
Use the Oracle OLEDB provider. See
http://www.carlprothman.net/Default....leFromOr acle for details -- HTH RP (remove nothere from the email address if mailing direct) "DevInstinct" wrote in message oups.com... 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=SOMETHI NG].THE_TABLE If you have any idea as by what I should replace [odbc;Driver={Oracle in OraHome92};DBQ=THIS.THAT;UId=SOMETHING;PWD=SOMETHI NG] to make it wirk with something else than ODBC, please let me know! Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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" wrote in message ps.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel, Microsoft Jet and OLE-DB
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) "DevInstinct" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I covert Microsoft Word file into Microsoft Excel one? | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic errors displaid when opening Microsoft Word 97 & Excel (7 | Setting up and Configuration of Excel | |||
Change individual cell heights/widths in Microsoft Excel 2000 like Microsoft Word | Excel Discussion (Misc queries) | |||
Microsoft Excel Viewer 2003 won't open Microsoft Excel Worksheet | Excel Discussion (Misc queries) | |||
How do I import Microsoft Word files into Microsoft Excel? | New Users to Excel |