March 2, 2006 at 11:32 am
Greets.
This may or not be a good forum group to start in, if not, apologies.
Microsoft had no solution for this:
I have an Excel File, a Access Query, and a SQL Based Database. The
relation? The Access Query has both native and polled/linked tables to the
SQL native backend. A Query is designed in Access (for ease) to poll the SQL
side through the linked tables, and a couple of other crosstab queries. This
Access Query needs to be accessed from Excel Pivot Table Manager as a link to
an outside source.
The solution... a user can refresh their pivot table using ONLY Excel
collecting data from both Access AND SQL.
The method... use a Excel Pivot Table Manager.
The problem... "ODBC connection failed" to the SQL backend.
Hypothesis... Excel cannot pull third source data through the Access layer.
ODBC Connections have been verified that they exist and function for both
Access and the SQL side on the PC attempting this feat.
Environment: Dozens of queries have been designed in access as a primary
mode of data extraction from the SQL side. In fact, the primary interface to
the data is actually programmed through VB in Access. (Not my fault). The
problem is that we have layers of experienced users, primarily the ones
responsible for queries are only capable of designing them through Access.
The final End User for the Pivot tables is not allowed access to the queries,
for stability concerns, and lack of End Users experience in anything
complicated.
It is vital that the interaction for the End User is near zero to obtain the
data, and I have to work with the query writing person to pull things into
access.
Microsoft support has not been able to either fully understand or workaround
this issue.
Please e-mail me/ respond if you have additional questions for resolving
this. I am prepared to offer a graphic representation of this if necessary.
March 2, 2006 at 1:45 pm
Hello Tobias,
did you think about instantiating your MS ACCESS object when your Pivot is refreshed?I am not sure what I mean, but if you create an Excel macro for refresh or record an Excel macro when refreshing, you can add code to specify connections etc. For example, I recorded the following macro for refreshing:
Sub TestMacro()
'
' TestMacro Macro
'
Range("C6").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub
PivotTableobject has all ODBC info, connection info etc.
I know that in VBA you can create instances of MS ACCESS and maybe you can get access to MS Access' link tables and ODBC sources through code.Add this code into one macro and let users refresh running this macro.
UPDATED:
found a good article for you:
http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part2/ch05.mspx
The article starts with Access Application Object, read also a couple of paragraphs for the Excel Application Object there,it says about accessing one office application from another one.
Regards,Yelena Varsha
March 2, 2006 at 2:25 pm
This isn't exactly the problem.
You see, the Pivot table is connected to an external connection throught the ODBC driver for Access database objects.
The Access Database object has a crosstab query that I need to have excel pivot.
In the Access Database, the query calls other sub-queries, that are tied with linked tables.
The linked tables are linked to the SQL backend.
I can access the Access queries that are only dealing with non-externally linked table (i.e. Access native tables) -OR- directly to the SQL tables and queries.
I believe what I really need is a ODBC driver that can -chain- multiple connections with thier proper authentications.
For my personal level of programming, I can poll datasources based on programmatical sql statements, but the queries are already made and maintained throught the Access query editor.
Coding a solution would be great if there were only one or two queries I had to replicate in .NET language (specifically VB.NET), but there are many, so I need to work with the applications themselves, and also make it easy for non-programmers to continue to design queries in Access.
March 2, 2006 at 3:55 pm
Use SQL Profiler and capture the Security Audit>>Audit Login Failed event. You should be able to capture the login that is failing. You can then at least see if it is the login that you are expecting to see from your Access DB. I have a feeling that since you are piggy backing 2 different ODBC connections (1 from Excel to Access, 1 from Access to SQL Server if I understand correctly) that the username/password being passed to SQL Server may be getting mixed up. It's just a hunch, but it would be easy to look at in Profiler.
I assume that the credentials are different between the Access and SQL Server ODBC connections. Have you thought of making them the same?
March 3, 2006 at 9:34 am
Hi,
did you try a FILE DSN for SQL Server forODBC source? Trying it now.
Regards,Yelena Varsha
March 3, 2006 at 10:26 am
Hi,
I tested it and it worked!
I created a File Data Source to SQL Server database on my workstation connecting to other server. I used a wizard in ODBC Data Sources Manager. It created a file under C:\Program Files\Common Files\ODBC\Data Sources with the extention .dsn. I edited this file to add a password:
[ODBC]
DRIVER=SQL Server
UID=mylogin
PASSWORD=mypassword
DATABASE=pubs
WSID=MYWORKSATIONNAME
APP=Microsoft Open Database Connectivity
SERVER=MyServerName
Description=Test File DSN
Then I linked Sales table of Pubs to the ACCESS database. Created a query to select stor_id, qty and ord_date from Sales through the linked table. Closed Access database. Opened excel. Created 2 pivots: one for the table and one for the query. Both worked. Changed data in Sales table on SQL Server. refreshed. It refreshed data reflecting the new numbers.
I also was able to copy this DSN file to another workstaion, it worked.
Regards,Yelena Varsha
March 4, 2006 at 3:09 am
Further thread here..
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=262845
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply