| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Importing data from Oracle server via ODBCto Excel on a mac?
Hi Mark,
If you are using Excel 98 you can turn on the visual basic recorder (Tools menu). Then use Get External Data to access the database and return the data to Excel.
The Excel 98 visual basic editor will record the code that was used to make the connection and the SQL queries that you made along the way. You can use the recorded code to initiate your connection and then use any SQL code you like. The Mac connection syntax is slightly different from the Windows version of Excel, so their code samples won't help you with the connection syntax.
There are lots of code samples in the visual basic help files. The Query help files are not installed automatically with Excel. Install the extra help files from the Value Pack.
For those using Excel 2001 you will need the ODBC drivers from the Office 98
install CD. Details are here:
http://www.agentjim.com/MVP/Excel/ExcelHome.htm
For those using Excel X: send a note to Microsoft detailing the product(s) you need to connect with (eg. Orcale 8i, SQL Server 2000, Access XP) being sure to include the version number. Also mention the number of Macs that would be accessing the databases and the importance of this connectivity to your organization. Microsoft needs this information to assess demand for building new drivers for OSX (UNIX). Send your information in an email to mswish_at_microsoft.com <mailto:mswish_at_microsoft.com>. The subject should be Macintosh Data Access Drivers.
-Jim Gordon
Mac MVP
All responses should be made to this newsgroup within the same thread. Thanks.
> I am trying to download data to an excel spread sheet on a mac from an Oracle
> server accessed via ODBC. I want to code this into Visual Basic code and pass
> the SQL command to the server. I am using the Merant ODBC version 3.51
> software, which I've installed a demo version on my mac. I can access the
> server using Microsoft Query, so I know at least that works, but I can't find
> any commands in Visual Basic that properly works. Has anyone actually done
> this, and what method did you use?
>
> I first tried to use SQLOpen and SQLExecQuery commands. The SQLOpen prompts
> me for username and password, but the SQLExecQuery command returns an error,
> and when I use SQLError, it gives me an error IMO001, with a message
> indicating that the SQL driver manager does not support this function. (FWIW,
> the same commands do work with local non-Oracle ODBC accessed database.)
>
> Since Microsoft Query works, I decided to try and use DDE to access the data
> via Microsoft Query, i.e.:
>
> Chan = DDEInitiate("MSQuery", "System")
> DDEExecute Chan, "[ODBCLogon('xxxxxx',1)]"
>
> Chan is successfully filled with a connection number, but the next command
> doesn't work at all, as it returns an error 2023. Note that I can't even get
> this to work with local non-Oracle databases either. I tried both ODBCLogon
> and the simple Logon command.
>
> Thanks very much.
>
> Mark London
> MRL_at_PSFC.MIT.EDU
Received on Sat Dec 29 2001 - 11:22:20 CST
 |
 |