Access exports only 1st column of query

All - I'm embarrassed to ask something that appears to be so rudimentary, but I'm stuck.

Using Access 2007, I ran a query against a single 84K row table to produce a result set of ~80K row. I can't copy/paste the result set into Excel (Access fails copy/pasting > 64K rows). When I right-click on the query and export, no matter what format I try, it only exports the firs开发者_如何学Got row (ID).

How can I get Access to export the entire result set? (I've tried highlighting everything, etc. I also tried using the 'External Data' ribbon, but that just exports the original table, not the result set from the query I ran.)

Thanks!

I ran a query, highlighted everything by clicking on the little arrow in the upper left, CTRL-C, opened Excel, CTRL-V. Exported the whole thing. (Granted I didn't have ~100k rows like you, but I don't understand why it wouldn't handle that too.)

Or is that not what you want?

What if you copy 40,000 rows at a time to different tabs in your Excel file?

I have had a similar problem with Access 2013, so decided to share how to resolve it. The only way I could solve this issue was by using VBA.

Only update testSQL (easy to see when you go to SQL view of your query) and CSV_file_path (the file path of your CSV export)

Sub Export_ToCSV()

Dim testSQL As String
Dim UserInput As String
Dim db As Database, qd As DAO.QueryDef

Set db = CurrentDb

testSQL = "SELECT Table1.Column1, Table1.Column2, Table1.Column3 FROM Table1;"
CSV_file_path = "C:\temp\filename.csv"

Set qd = db.CreateQueryDef("tmpExport", testSQL)
DoCmd.TransferText acExportDelim, , "tmpExport", CSV_file_path, True
db.QueryDefs.Delete "tmpExport"
MsgBox ("Finished")

End Sub