Exporting from Access to text

[HeatherR]

I have over 400,000 rows of data that I want to export from Access 2003. I
have Excel 2007 on another computer that I'm going to analyze the data with.
When I try to export to a text file, it still cuts off at 65,000 rows. Is
there any other way around this?

 

[Pete D.]

You are hitting the excel row limit and even as text you will hit this
limit. Excel will import from access so use a copy of the mdb file or
export to Dbase which excel also can import. You will still have to work
around this import limit in excel as far as row you can work on at a time.

 

[tcarnahan via AccessMonster.com]

Cant you just use the Excel "External Data" feature from the "Data" menu and
link into your Access file? You can also use the Pivot Table wizard to link
to Access.

 

[gllincoln]

Hi Heather,

Access 2003 is not going to export more then 65k records in Excel format - hard coded limitation, Access 2003 doesn't know about the .xlsx file format or ability of Excel 2007 to handle 1M rows.

The function below can run from a macro or trim off the Function declaration and put the code in a command button click event. Insert your table name and path\folder\filename info.

Hope this helps,
Gordon


Public Function myExport() as Boolean

Const myTable as String = "InsertTableNameHere"
Const myFolderFileName as String = "C:\InsertSomeFolder\SomeFileNameHere.csv"

docmd.TransferText acExportDelim,,myTable, myFolderFileName,True
myExport = True

End Function