Import Access query to Excel, shows less records??

[Asher]

Hello,

I am importing a query from Access into an Excel 2007 spreadsheet. I go to the "Get External Data" tab in the ribbon --> "From Access" --> Selet data source (the Access dBase) --> Select the Table (the Query I want) --> Import Data (select table view and sheet and cell to start in).

When the table shows up it only contains 42 records, but there are 83 records in the query in Access.

Any reason why?
Any way to fix it?

[HansV]

Without knowing the details / seeing the database it's impossible to answer your question.

[Asher]

OK, more details...

I have a database that has 2 linked tables in it. They are linked to a couple excel spreadsheets that are dumps from an online dBase. Those tables are related by a Report field (in Export Table) that matched up with the field Problem Report (in Change Order Export table). This is an outer join. So all the records in Export Table will show up in my query, and only those records from Change Order Export table where Problem Report matches Report (in Export Table) will show up.

I have made many queries based on that relationship with various other filter, sort requirements.

This dBase is similar to the one I was using in the TransferSpreadsheet VBA in Access to Excel 2007 Table. I tried to use the same code and Excel format that finally worked on that dBase for this one, but it doesn't work. Somehow when the TransferSpreadsheet command is used on this dBase, the data goes over to the Excel sheet corrupted. I can't figure out why so I thought I'd go the other way around and do an import from excel, that way the tables are updated using "Refresh".

In attempting to do this, I followed the procedure in my above post and for all my queries that got imported to their respective sheets, the correct number of records came up... except for one.

For some reason, the main query, qryALL, that all the other queries are based off of (the data dump tables have too many columns so the qryALL just pulls in the few that are necessary), that import only pulls 41 records into the excel table instead of the 83 records that are shown in excel when the query runs.

When I look in the Connection Properties, the Connection String looks like this :

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents and Settings\myID\Desktop\Export\Export Status\_Status.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True

Please let me know what other information is necessary if this isn't enough. Or if I'm giving the wrong kind of information, please let me know what you need.

[HansV]

What is the SQL of the query?

[Asher]

SELECT [Export].Report, [Export].[Process Status], [Export].[Date Created], [Export].[Plan Approval Date], [Export].[Required Closure Date], [Export].[Resolution Priority], [Export].[Defect Severity], [Export].Summary, [Export].[Corrective Action Plan], [Export].Product, [Order Export].[Order], [Order Export].Status, [Order Export].[Current Product], [Order Export].[Current Product Title], [Order Export].[Current Product Revision], [Export].Project
FROM ([Export] LEFT JOIN Priority ON [Export].[Resolution Priority] = Priority.[Priority Name]) LEFT JOIN [Order Export] ON [Export].Report = [Order Export].[Problem Report]
WHERE ((([Export].Project) Like "Verification*") AND (([Export].Status)="Open")) OR ((([Export].Project)="Trial") AND (([Export].Status)="Open")) OR ((([Export].Project)="Trial : Sound") AND (([PTS Export].Status)="Open"))
ORDER BY Priority.[Priority Value] DESC;

This si a direct copy paste from Access SQL window except I altered the names a little.

[HansV]

I'm sorry, but I don't see anything that causes alarm bells to ring. I'd really need to see (a stripped down but representative copy of) the database.

(I assume that "PTS Export" should also have been edited to "Export")