Large Access Query - How to Reference?? Help Needed!

[mgrogan06]

Hi all,
I have a large Access database that will max out the number of rows Excel can handle. I am referencing part numbers that contain over 2 million items. My users only have Excel experience so I have been tasked with attempting to maintain that standard.

Is there a way in Excel to pull Access data via a connection? I know I can import the data (too large) or try a Pivot (still too much data). Is there a magic "=getaccess" formula out there?

In my mind I see Excel serving as a dashboard. I will pull the data from Access and display nice and clean in Excel.

Thanks for the help!!!

 

[ranman256]

instead of Excel , use access to pull the data,
then use this code to export the data into 1 million record blocks. 1 per sheet.

in access write a make table of your data. including a 'marked' field (empty)

start
create an 'export' select query, qsExportTopUnmarked: where it selects the top 1 million records where 'marked' is null
export this list via : DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, "batch" & i (i = 1)

then run an update query that updates qsExportTopUnmarked and updates [marked] = "X" or true.
run Start again, but export with i = 2 (the second worksheet)
repeat until all records in qsExportTopUnmarked, are marked.

 

[Smitty]

Welcome to the Board!

Is Power Pivot an option (Excel 2010+)? It can handle millions of rows and Pivot them with no problem.