Export large query from Access

[AK177]

Hi, I have a large query (40K records and two dozen or so fields) that I want
to export from Access and then load into the program R as a csv file. It
seems to be too big to export as an Excel file or dbf - when I try to export
as an Excel file not all of the records are exported and when I try to export
as a dbf I get the error "cannot define the field more than once". What's the
best solution here?

Thanks.

 

[Gina Whipp]

AK177,

Did you try just sending them to .txt file?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jerry Whittle]

How are you trying to export it to Excel? If you are using the Menu toolbar
to export the query or using the DoCmd.OutputTo in a macro or code, it has
trouble with that many records.

The TransferSpreadsheet in a macro or code can handle more records - up to
64K.

DoCmd.TransferSpreadsheet acExport, 8, "Employees","C:\Employees.xls"

Here's a way to do it directly from a query. However you need to delete the
spreadsheet if you do it more than once. Otherwise you will get a "Table A
already exists" error and it will NOT give you an option to overwrite it.

SELECT * INTO [Excel 8.0;database=C:\Temp\External.xls].[A] FROM TableA;

 

[John W. Vinson]

Hi, I have a large query (40K records and two dozen or so fields) that I want
to export from Access and then load into the program R as a csv file. It
seems to be too big to export as an Excel file or dbf - when I try to export
as an Excel file not all of the records are exported and when I try to export
as a dbf I get the error "cannot define the field more than once". What's the
best solution here?

Thanks.

File... Export... and select .txt or .csv as Files Of Type. Excel doesn't need
to get involved at all.

If you're doing it with VBA look at the Help for the TransferText method.