ODBC connect with propritary driver/Excel Trust settings

Question

I have a spreadsheet in Excel that connects to an internal DB using the odbc driver for the software (Action Request System). That works fine. Now I'm trying to move the Excel file to a SharePoint site so that our team can review the data and make notes in the same spreadsheet.

So, first I tried just moving the Excel file to the server, but realized that the connection saved in the file was pointing to the connection file on my computer. So I moved that to the SharePoint site, and this seems to be working... Except:

1) On some of the computers, it opens just fine. But I'd prefer it if the dialog that asks for the login and pw for the DB was customized.

2) On other computers, the software and drivers are installed, but not showing up in the user source list, so it throws an error. When the user goes to create a new connection, the driver is there, (so its not in the first list, but it is in the larger list).

3) Macs can open the file, but Excel 2008 can't do the macros and connections.

4) Opening the same file from the same site throws the Trust Center warning each time, even on the same computer/same user.

Is there a way to do any of the following?

• Have a non-macro that keeps the user from saving (and thus overwriting) the sheet until it is confirmed that the user won't make changes to the connections/macros?

• Allow the user to connect from a local config (in case they can set one up that works when the main one fails) so that they can connect using that one but not screw it up for everyone else.

• Set the trust settings (local or on server, I suppose) so that any files from a specific SharePoint are always trusted?

Finally, and this is the big one...

Can you create a connection using a driver not on the local machine? If the driver is installed on a remote server, can Excel use that one? If that were the case, that would solve almost all other problems (Unless you're on a Mac).

I feel like I'm going at this slightly wrong, but since the team all need to see each others notes, our only other solution is to upload the data (from the DB) into a MySQL db so that they can make notes from their browser. This makes it more universally available, but forces us to script a lot of functions that are standard in Excel and create redundant DBs.

Answer 1

Wow! Access to the ARS via ODBC! I suggested that feature to the architect when I worked at Remedy HQ back in the nineties. Great times.

Anyhow, I think your approach of sharing an Excel sheet that connects to an ODBC source is not the easiest path. (As you've been discovering.) ]

ODBC is always a local driver, afaik. A client locally connects to ODBC. ODBC then can use the network to connect to the DB.

Instead, I suggest that you investigate setting up Access with a table that is remotely connected to the ARS system. You could then remotely open the Access table via file sharing as opposed to a dbms-connection. I believe that this would be easier to get going than your current path.

HTH,

Larry

Answer 2

It sounds like you are using a User DSN to make the connection. You can switch to a DSN less connection by using a connection string. If you need some help with that, can you post the code that needs modified?

Answer 3

First, thanks to both users that provided answers. Both of you were really helpful in getting my mind around the problem.

So, it turns out that while you can't easily connect to Action Request DB directly, AR does support Web Services.

Rather than attempt to make Excel, Access, PHP, or any other system deal with creating a Soap Client and handle the work themselves, I came up with an alternative that I think is fairly clever, if I do say so myself:

1) Created a php script that calls the Web Service with the requested data,

2) The script parses the response into a more generic XML form,

3) script echoes out the xml

4) Name the script "AR_Data_Request.xml"

5) Configure the directory, via the .htaccess file, to treat xml files like php

Now, I have a static xml file which always contains the most up-to-date data from Action Request. I can point any other apps (specifically Excel) to that xml file instead of having to write VBA code to query the web service directly (and still have to do it again for php).

The only problem is that I can't get the php to connect to the Web Service (doh!) but that will be my very next question.

Thanks again!