Data too large for Excel, need to query Access data for results

[Susan]

I've got a large database within Excel, and will soon exceed 70,000
rows. Excel's limit, as we know is 65,536
Its used to support VLOOKUP and IF functions, and return prices and
purchasing data for items on another worksheet, which usually contain
1000 items or less.

Short of paring down my database, it there a way to keep the data in MS
Access, and query the lookup answers from within Excel, and have Excel
just display the correct prices and other data pertinant to that line?


Sue

 

[Guest]

Pulling data from Access into Excel isn't difficult. Here's a starting point

http://support.microsoft.com/kb/295646

It explains how to pull Access data into Excel using ADO. You don't need to
do lots of coding if your queries are fairly standardized, and you may find
it is much faster than having 60,000+ rows in Excel

 

[NickHK]

Sue,
To add to Duke's info: You can get the data into Access quite easily with
its Import feature.

Or get Office 12, which apparently extends the rows.count to something like
1 million.

NickHK

 

[Susan]

Sue,
To add to Duke's info: You can get the data into Access quite easily with
its Import feature.

Or get Office 12, which apparently extends the rows.count to something like
1 million.

NickHK

Nick, I didn't see a reply from Duke, but I do thank you for the heads
up on Office 12.

Susan

 

[NickHK]

Susan,
<Duke's comment>
Pulling data from Access into Excel isn't difficult. Here's a starting point
http://support.microsoft.com/kb/295646
It explains how to pull Access data into Excel using ADO. You don't need to
do lots of coding if your queries are fairly standardized, and you may find
it is much faster than having 60,000+ rows in Excel
</Duke's comment>

NickHK

 

[NickHK]

Susan,
But bear in mind that your code would then not be compatible with previous
versions of Excel, ones with rows.count<1 million.

Nick

 

[skuzapo]

Hi Nick - I read the article in the microsoft knowledge base you
submitted and I was wondering if you could help me with a similar
question.

I'm trying to do the same thing as Sue but the otherway round. I have a
data list in Excel which is added to every now and then. I'd like to
automate the transfer of this list into an access table from where I
run some reports.

I don't mind if this is an append everytime a few lines is added or a
complete transfer of all the lines once every now and then.

Appreciate any advice in this regard.
Cheers

 

[NickHK]

skuzapo,
Why not just link your Excel file to Access; File > Get External Data > Link
Tables.
Then you can base your report on a query that includes only the new records,
however you decide that.

NickHK

 

[Susan]

Susan,
<Duke's comment>
Pulling data from Access into Excel isn't difficult. Here's a starting point
http://support.microsoft.com/kb/295646
It explains how to pull Access data into Excel using ADO. You don't need to
do lots of coding if your queries are fairly standardized, and you may find
it is much faster than having 60,000+ rows in Excel
</Duke's comment>

NickHK

Nick, seeing that I'm using Excel 2003, I went here
http://support.microsoft.com/kb/246335/EN-US/

Followed it to the letter, clicked on the button, and got no responsen
and Excel was non-responsive..
Question: I paste the code on the userform view code, after any
existing code, which was 2 lines identifying the button?

TIA for any leads.


Susan