External data from Access missing queries/views when linking to excel file.

Question

For one of my Access database files when I try to pull data into Excel by creating a external data from Access link I am not getting the option to pick from the queries/Views. The only options that shows up are standard Tables. I have not been having this problem with other access databases, so I'm not sure what the problem could be. Does anyone have a solution or know what might cause the queries to be filtered out?

Answer 1

Hello,

If you are using Data > Get External Data > Microsoft Access, try this longer approach.

1. Data > Get External Data > From other sources > From data connection wizard > Other/Advanced, click Next
2. Choose the Microsoft Office 12.0 Access Databae Engine OLE DB Provider, click Next
3. Provide the path to your database file in the 'Data Source' field
4. Click Ok

Do you see the query listed within the Data Connection Wizard dialog now? If so, it appears the issue lies somewhere within the default driver being used for Access. You could try a repair of Access 2010.

If the queries still do not show, then there are other reasons preventing them from being displayed. Perhaps this database has user-level security or it's corrupted. You could create a new blank database, import all of the objects from the old database into the new database container and then test using the Get External data in Excel to the new database file.

Regards,

Dennis

Answer 2

I have something similar going on.  It doesn't show all the queries.  Tried using the from Access and the more advanced method outlined above, and in both cases the list of tables/queries is the same.  As far as I know the queries are fairly standard SQL.  They do date manipulation to group by Month and year for counts and sums of the collected data.

Answer 3

Hello,

 

If you are using Data > Get External Data > Microsoft Access, try this longer approach.

 

1. Data > Get External Data > From other sources > From data connection wizard > Other/Advanced, click Next
2. Choose the Microsoft Office 12.0 Access Databae Engine OLE DB Provider, click Next
3. Provide the path to your database file in the 'Data Source' field
4. Click Ok

 

Do you see the query listed within the Data Connection Wizard dialog now? If so, it appears the issue lies somewhere within the default driver being used for Access. You could try a repair of Access 2010.

 

If the queries still do not show, then there are other reasons preventing them from being displayed. Perhaps this database has user-level security or it's corrupted. You could create a new blank database, import all of the objects from the old database into the new database container and then test using the Get External data in Excel to the new database file.

Regards,

Dennis

　

　

I was also having a very similar type of problem, but not exactly the same.  In my case, I was trying to crate an Excel (Excel 2010 & Access 2010) PivotTable which would be linked to a query in an Access DB.  When I get to the step where I need to select the table or query that I want ot link to, I see ALL tables in the DB but only SOME of the queries.  The query I wanted to link wasn't one of the ones displayed. 

I was able to dig a little further.  On one of the queries that doesn't show up, there is a field which takes a date from the source table & converts it to the week ending date (Saturday).  (See Below for the code that does this).  Anyway - If I remove the week end date from the query and re-save the query under a new name, the new query will show up in the XL pivottable source selector dialog box.  When I put the week end date field back in & resave under yet another new query, this new query does not show up in the Pivottable Source selector.  Obviously, there is something fishy with that week end date field.   

All the queries (with & without the Week End Date) field work in Access.  It's just that not all of them are showing up in the XL pivottable source selection dialig box.

The database was created back in the mid '90's in Access 2.0 but has been subsequently converted to Access 2010.  My suspiciion is that somewhere along the line, Microsoft has done something to make queries which contain fields based on functions not be visible to Excel.  Probably a bug in the new 2010 security features which make Excel leery about running "code"  in external data.  (Just my hunch) 

Query Field:  Week_End_Date: WkEndDate([Date])

("Date" is the field name from the source table for this query)

Code that computes the Week end date:

Function WkEndDate(XDate As Variant) As Date    Dim DayofWeek As Variant

DayofWeek = WeekDay(XDate)    WkEndDate = XDate + 7 - DayofWeek

End Function

Any more hunches out there?  My plan at this point is to remove the WkEndDate function & replace it with a table that takes each & every date (From 1969 on) and links that to it's appropriate week end date.  Not very elegant, but at this point, I'll try anything.

Answer 4

Exporting the data from Access is not an option. Too many files will rely on the data (different subsets) and the data can and does change overtime. It would be impractical and error prone to try to remember what changes occurred and what files needed to be updated. Constantly exporting to an Excel file and then having to make sure it gets in the right place. That just defeats the whole point of having a database.

What I'm trying to do and have done with another one of my databases is to create tables and pivots that are linked to the Access database which can simply be refreshed and any changes are automatically propagated. It works well for my needs. My issue is that for this particular database the list of available tables and more importantly queries does not show the queries (referred to as views).

Answer 5

Hi,

I don't know how did you export data from Access to Excel? I feel strange why did you use "Access" option under "External Data" menu? It is import data from or link to data in another Access database. If you want to export data from Access 2010 to Excel, you can click "External Data" tab and from "Export" group, click "Excel", in the pop-up "Export- Excel Spreadsheet" wizard, from file name, select the location where you need to export and specify Excel file name, under file format, choose an appropriate format. Please check the following article.

http://www.addictivetips.com/microsoft-office/access-2010-export-database-table-to-excel-2010/

Best regards