Linking Access Table with Excel Worksheet

jeanvliet2012

New member
Local time
Today, 03:25
Joined
Aug 15, 2012
Messages
8
I have a big Access table with 900,000 rows, this table may grow beyond 1,000,000 records within 2-3 months. I would like to use Excel to read the table but I don't want to download the data to Excel. I know when you link Access and Excel, you will download the data to Excel. Is there a way to use Excel to read the Access table and do some calculations in Excel.

I am not sure if my request can be done. If you know how to do it, please let me know. Thank you very much.
Jean:confused:
 
I am assuming that you don't want to link (download) the data because of the number of records. Instead of linking on the table, can you build a query in Access that summarizes or otherwise groups your data into a manageable dataset? Then link into the query?

Depending on your desired calculations, you may be able to write the query to produce your desired results.
 
BigHappyDaddy is right on that one, i.e. creating a query to perform your calculations and linking to that.

There are a couple of ways Excel offers for linking to databases. I would advise you to explore the External Data tab or menu in Excel. If however you want the summarised data to be exported permanently to an Excel file then we can offer links to useful code.
 
Thank you for your reply.
Unfortunately, I have to use all the data in the Access table to do the analysis. I searched internet for Access and Excel related threads and could not find any resolutions. I assume that you can't just build a shell in Excl and leave the data in Access. Please let me know if I am incorrect.

Thanks.
Jean
 
I don't think you're following what we're saying. Perform your calculations or analysis in a query in Access and export the result of that to Excel. It will be much quicker that way.
 
Hi VbaInet,
I understood your saying. I did several tests between Access and Excel before I asked help here. Access database has 2 GB limitation. I was not able to put all the calcuation in Access. The performance was not good either.
Thanks
Jean
 
Storage limitation but not memory for performing calculations.

What's the source of the data?
 

Users who are viewing this thread

Back
Top Bottom