April 10, 2012 at 7:06 am
Hi,
Its been many years since I have used Access in anger. I have been working as a MS SQL database developer for the past seven years so the Access way of doing things is a bit alien.I have recently taken on some charity work that i am doing in my free time. It is a order tracking database that has a SQL 2005 express backend with an Access 2010 front end.
One of the forms in the Access front end is a Pivot table that is based on a database view. In the PivotTable Tools menu there is the option to "Export to Excel". This should export the whole pivot table and data connections into an excel worksheet.
Current whent he button is press, Excel opens but produces an error ssaying "Problems came up in the following areas during load: PivotTable." It also say that an error log has been generated and give a location but that location does not exist and neither does the log.
I have mangaed to trace this problem down to the just of INNER JOINS in the view. When i alter the database view to select directlly from a single table i can export the Pivot table without issue but when i put the joins back in i get the same error. I have even tried create a view that selects its results set from the origional view (This means that the view that access refs does not contain any joins) but i still get the same error.
I have had a look around the site and the internet but i cannot seem to find anything that would indicate that this was a bug or a known limitation. Has anyone else encountered this and is there a way to get it working?
Thanks
Steve
April 10, 2012 at 7:46 am
There were some issues with exports to Excel in the initial release of Access 2010. Most of those were resolved with SP1 when it was released in mid-2011, so you might want to check and make sure you have the service pack installed. Also, it's not clear where the PIVOT is being done. Is it in the SQL Server view, or is Access performing the pivot? Finally, how is the connection to SQL Server being made - using ODBC data sources or with direct connection strings? Or is this the ADP format?
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 10, 2012 at 8:02 am
Hi Wendell
Thanks for your reply. I am using version 14.0.6112.5000 which i believe is the latest version.
The PIVOT is being done in access, the database view is static.
As for the connection, i am not sure how i can find this out. I have had a look at the connection and server properties and they do not give to connection info. If i had to guess i would say its ODBC.
April 10, 2012 at 8:58 am
You do have SP1 based on that version number, and I am not aware of any hotfixes published subsequently that deal with Excel export issues. Access pivots have never been as useful as what you can do in Excel, but in this case, does it appear the pivot is behaving as it should? You can determine if the tables are linked by holding down the SHIFT key as you open the database and then look at the tables and see if they show a globe symbol indicating they are ODBC linked tables. Have you tried copying and pasting the pivot result into Excel directly rather than trying to export it?
It seems unlikely that joins in the SQL Server view would cause issues with the pivot unless there are a great many records. How many records does the view return?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 10, 2012 at 9:47 am
The Pivot seems to work fine in Access, its a bit slow but that may be down to the rubbish machine that i am using!
I held down SHIFT as the database opened and got the "All Access Objects" box which listed all the database objects as well as the various forms and chunks of VB code. There were no globe symbols next to any of the objects.
Copying it from access to excel does not work.
There are only 4630 rows returned by the view.
The reason i think it might be down to the joins is that when i removed them and hard coded the values into the select it exported fine. When i created a new pivot table in access that was based on a view selecting from a single table, that exported fine too. In both cases, as soon as i added a join into the view, the export ceased to work.
Thanks so much for your help so far.
April 10, 2012 at 8:05 pm
Sorry - the day got more than a little crazy. It sounds as if you may have an ADP format database, which connects directly to SQL Server. But in that case, I don't think the Pivot feature is supported. Try opening it so you see the Nav Bar, and then see if the Linked Table Manager under External Data gives you some clues as to how the connection is being made.
Wendell
GLMMS, LLC
Evergreen, CO
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 11, 2012 at 2:21 am
No worries, i am just happy for the help.
The Linked Table Manager is greyed out. I have been have trouble finding where the access/database interaction takes place. When an action is preformed in access i can see that the database is being queried but i cannot find those queries in access. In some places the properties of an object in access show the query or view that is used but in other places that source seems to point to an existing object on a parent form.
Sorry for the vague response, I am the latest in a number of people to have worked on this project.
April 11, 2012 at 4:52 am
You can tell what type of database the Access front-end is from it's file extension. Prior to 2007, there were four common ones, .mdb and and .adp, and .mde and .ade which are compiled versions of the other two respectively. Access 2007 introduced a new format called .accdb, and it's .accde compiled version. The fact that the Linked Table Manager is greyed out is odd unless it is the .adp/.ade format. In a compiled version, you cannot view the design of forms and reports, so it appears that you are probably not dealing with a compiled version. If it is the .adp format, you should see stored procedures rather than queries when you expand the Navigation Pane.
There is another possibility however, that being that all of the SQL Server tables are accessed using ADO in the VBA modules. That makes it considerably more difficult to sort out how things are being done if you haven't had much experience with Visual Basic for Applications. If that is the case, you should see a connection string at the beginning of the module behind forms and reports that establishes the data source.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 11, 2012 at 8:32 am
The extension of the Access file is .adp
There is no connection string in the code behind the forms, it is using Data Link which i think is part of OLE DB.
The database connection is defined in the properties on the access file.
April 11, 2012 at 9:24 am
Since it is an ADP, all forms and reports will be using either SQL Server views or stored procedures as their data source. Note however that some of those may be SQL T-SQL strings embedded in Visual Basic for Applications that reside behind forms. In your case, I suspect the pivot is actually being done in code behind the form using embedded VBA statements. I do not normally work with .ADP databases, preferring to use the linked table approach used with the .MDB version, so I'm not sure I can be much more help in resolving your issue. I would suggest you post your issue in one of the forums more focused on Access. Those include UtterAccess, Eileen's Lounge in the Access forum, Windows Secrets Access Forum, or in the MSDN Access Developer's Forum.
One of the challenges with .ADP format databases is that you can't easily upload the application in a scaled down version so that experts can tinker with it, since they would also need the SQL Server database. The Microsoft recommendation on what format to use has gone back and forth, but the present recommendation is to use the .MDB format with linked ODBC tables for new development. However with an existing application, I would not suggest redesigning things - I fully expect the .ADP format to be supported in at least the next version of Access.
Wendell
GLMMS, LLC
Evergreen, CO
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 16, 2012 at 8:24 am
I should have added this on my original reply. If you do get a resolution to your problem from another source, it would be helpful to others to post a summary in this thread with any appropriate links to detailed information. Thanks.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply