MS Query Mac vs PC and exchanging spreadsheets

[Maurice Jeter]

Hello,

We have a couple of Macs in our organization which have Office for the Mac
installed, and we were looking for ways to use MS Query for the mac, to run
reports against an Oracle database. We found we needed to use a 3rd party
ODBC driver (from Actual Technologies) to accomplish this, and were able to
get it to work.

What we've found, however, is a query in a spreadsheet that was generated in
Excel for Mac, cannot be edited nor refreshed when sent to a Windows Excel
user - even if both machines have the same DSN names setup for ODBC.

When attempting to edit/refresh a query we get:

[Microsoft][ODBC Driver Manager]Data source name not found...

At this point, we can select a datasource (that matches the same DSN name
that was used to create it, on the Mac) but then we receive:

[Microsoft][ODBC Driver Manager] Drier's SQLSetConnectAttr failed
[Microsoft][ODBC Driver for Oracle] Invalid connection string attribute

This this mean that the connection string formats used on the Mac, and
Windows, are different (and thus incompatible)?

Thank you for any insight.. or if a different Mac ODBC driver is recommended!

Maurice

[Shane Devenshire]

Hi,

I really don't know what the difference is, however, you might find out by
recording the process on the Mac and PC (assuming you are not using Mac
2008). I would then compare the SQL statements generated by the two
recordings.

You might see an obvious difference which you could address. For example,
you can trap the version of Excel you are using (mac vs pc) and then handle
the differences in code with IF's.

For example, here is the code recorded for a 2003 query against another
Excel spreadsheet.

Sub ExternalData()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=F:\Classes\Pivot Tables - Class
Files\Lesson11.xls;DefaultDir=F:\Classes\Pivot Tables - Class Files;DriverI" _
), Array("d=790;MaxBufferSize=2048;PageTimeout=5;")),
Destination:=Range("D1") _
)
.CommandText = Array( _
"SELECT Data.Store, Data.Fruit, Data.`Number Sold`" & Chr(13) & "" &
Chr(10) & "FROM `F:\Classes\Pivot Tables - Class Files\Lesson11`.Data Data" _
)
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire