MS Query: joining table value to value in spreadsheet

[Stefan Keydel]

Hello,

Please forgive me if this has been addressed many times already!

I'm using Excel 2004 on the Mac. I'm using MS Query to query an Oracle
database and I want to join a field in the Oracle database to a field in
my Excel spreadsheet.

For example, suppose I have a column in my Excel spreadsheet with the
names of cities and suppose the Oracle database has a table with cities
and some of their key metrics (population, square acreage, etc.). What I
want to do is return a row of data for each city by joining the city
field in my spreadsheet with the city in the Oracle table.

Is this possible? I have no trouble running queries against the Oracle
database and returning data to the spreadsheet, but I can't seem to
figure out how to specify this join in the query.

Thanks,

Stefan

 

[Jim Gordon MVP]

Hi Stefan,

A query can only work with one database at a time.

From the sounds of things you do not have the ability to add your excel
spreadsheet as a table in the Oracle database.

You would need to have a different database in which to add the two tables
that you want to query. That would entail having a local copy of FileMaker
Pro or MySQL or something that you can control yourself.

Then you could use MS Query to join the tables from that data source.

-Jim Gordon
Mac MVP

Hello,

Please forgive me if this has been addressed many times already!

I'm using Excel 2004 on the Mac. I'm using MS Query to query an Oracle
database and I want to join a field in the Oracle database to a field in
my Excel spreadsheet.

For example, suppose I have a column in my Excel spreadsheet with the
names of cities and suppose the Oracle database has a table with cities
and some of their key metrics (population, square acreage, etc.). What I
want to do is return a row of data for each city by joining the city
field in my spreadsheet with the city in the Oracle table.

Is this possible? I have no trouble running queries against the Oracle
database and returning data to the spreadsheet, but I can't seem to
figure out how to specify this join in the query.

Thanks,

Stefan

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Stefan Keydel]

Hi Jim,

Actually, I do have the ability to upload the data to a table in my
Oracle schema; I guess I was hoping that I could eliminate this step.

Interestingly, when reading the PC-centric programming board, I found a
message pointing to an article that describes how to do this using the
parameter setting in the "Return data" dialogue:

"Using Parameters In External ODBC Data Queries"
<http://nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm>

The first sentence reads:
"Here we can demonstrate the linking of data in an external data source
with Excel, using the values in cells to determine the data presented,
using parameterised data queries."

Is this just one of those things that's possible with the Windows
version but not with the Mac?

Thanks for your response,

Stefan

 

[Jonathan Monroe]

Interestingly, when reading the PC-centric programming board, I found a
message pointing to an article that describes how to do this using the
parameter setting in the "Return data" dialogue:

"Using Parameters In External ODBC Data Queries"
<http://nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm>

The first sentence reads:
"Here we can demonstrate the linking of data in an external data source
with Excel, using the values in cells to determine the data presented,
using parameterised data queries."

The "Parameters..." button in Excel mentioned in the article you cited
appears to be permanently disabled in the Mac version of Excel. I
could not get it to be enabled when I following the instructions in the
article, or when I tried anything else.

According to the article, the "Parameters" feature is for inserting a
small set of values from a spreadsheet into a SQL query. I don't think
this would let you perform a JOIN with your spreadsheet data, like you
were inquiring about earlier.

If you are comfortable with VBA, you might be able to write a function
that iterates over each cell in your spreadsheet, and builds a SQL
string that can be executed using the QueryTable VBA class. You would
need to build the query so it only returns a single result given the
single cell value. You would execute a SQL query once for each row in
your spreadsheet, giving you the same end result as a JOIN, but much
less efficiently (you would be executing a query once for each row in
your range, as opposed to only executing a single query once overall).

I'm not a VBA expert, so I can't give you the exact code to do this,
but I can give you an example function that uses the QueryTable
function:

Sub ExecuteQuery ()
Dim sql
sql = "SELECT my_column FROM my_table WHERE id = the_id"

Dim connString
connString = "ODBC;DSN=myOracleDSN;UID=myUserID;PWD=myPassword"

Dim thisQT As QueryTable
Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connString,
Destination:=Range("a1"))

thisQT.BackgroundQuery = False
thisQT.sql = sql

On Error GoTo XERR
thisQT.Refresh

Exit Sub
XERR:
Dim errErrs As ODBCErrors
Dim errErr As ODBCError
Set errErrs = Application.ODBCErrors
If errErrs.Count > 0 Then
For Each errErr In errErrs
strMsg = strMsg & "#" & errErr.SqlState & " = " & errErr.ErrorString
Next errErr
MsgBox strMsg, vbCritical, "ODBC ERROR"
End If
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr & Err.Source & vbCr & vbCr &
Err.Description, vbCritical
Err.Clear
End If
End Sub

Your function would iterate over each cell in your range and modify the
sql string to use the ID that corresponds to the current cell. You
would also modify the Destination:= to be the cell where you want your
returned field to go (probably adjacent to the source cell).

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
http://www.actualtechnologies.com

 

[sfkeydel]

Thanks, Jonathan, for the detailed reply! I don't know if I have the
stomach to try to wrangle this, but it's certainly something to aspire
to

Stefan