ODBC in a Shared Excel Workbook

[mattjp]

Hello, I am trying to setup an ODBC connection to MS SQL Server from an Excel 2003 workbook. Whenever the workbook is in non-shared, single-user mode, the connection works fine and the linked data can be refreshed, etc.; but when shared mode is activated the data becomes static and the connection no longer seems to work.

Is this a limitation of shared Excel workbooks? I have searched the MS knowledge base and can find no mention of this situation. Links to other workbooks still work fine, I don't know why the ODBC connection would die.

Thanks for any help in advance,
Matt

 

[SkipVought]

Hi,

It seems that Query is disables in shared workbooks. It is not documented.

However, it does seem logical, perhaps. User A has the workbook open with unrefreshed querytable. User B opens and refreshes the QT. Could become a major mess.

In a table scenario, rows become keys. User A deletes row 5 or modifies row 5, and row 5 is deleted or changed in all open workbooks if accepted. Has nothing to do with the data VALUE in row 5. In a query, row 5 (and all other rows) may be returned with entirely different data. How would that work?

Can you explain how your workbook is designed to be used in this application?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattjp]

The connection to the SQL server data source is one-way only. I am only trying to read data from the server, put it in a named range (both of which are nicely automatic, it seems), and then other code on the worksheet references this range in a VLOOKUP and displayes derived information based on the values retrieved.

This workbook performs the base statistical calculations that determine whether our people need to run further quality tests on a type of product we produce. In the past, it referenced data in another excel workbook that contained logistics information. However, we just changed ourlogistics system to a SQL Server database with an access frontend.

We did not forsee any problems linking to the SQL Server instead of an external workbook, and unfortunately the use of shared workbooks is unavoidable. I can't think of any workarounds that aren't extremely messy. I guess I'll have to look harder.

 

[PHV]

SQL Server database with an access frontend
So, why using excel instead of an Access.Form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

ditto!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

The connection to the SQL server data source is one-way only."

I in no way assumed that you were doing anything to the data source. I was ONLY referring to what can happen when several shared workbooks are open and NEW QUERY RESULTS are returned to one of the workbooks.

Your multi-user application seems like it would fit much better in Access than Excel.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattjp]

This particular application is much better suited to excel than access; namely, it does large amounts of calculations on large data sets, generates graphs and statistics. The only hiccup is that while in the past, it referenced cells in another workbook file, it now must reference a table on an SQL server. I am simply trying to find a way to work around the limitation of breaking ODBC connections in shared mode.

Skip, I now understand what you are saying--I can see how the discrepancy might foul things up. Unfortunately, this isn't something we'd have to worry about in our situation, but I guess that's little consolation.

 

[SkipVought]

Instead of an embedded query, make it a ADO query, run entirely with VBA code and placed on the sheet using the CopyFromRecordset method.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattjp]

Thanks, I'll try that. I guess it's too bad that I can't use the easy built in functions =)