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

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

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

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




"Susan" wrote:

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

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

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" wrote in message
oups.com...
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

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

NickHK wrote:
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

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

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

"Susan" wrote in message
oups.com...

NickHK wrote:
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

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

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

"Susan" wrote in message
oups.com...

NickHK wrote:
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

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

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


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=519893

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

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

"skuzapo" wrote in
message ...

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


--
skuzapo
------------------------------------------------------------------------
skuzapo's Profile:
http://www.excelforum.com/member.php...o&userid=27430
View this thread: http://www.excelforum.com/showthread...hreadid=519893

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

NickHK wrote:
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