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

[Duke Carey]

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
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Susan]

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

[Susan]

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

[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

"Susan" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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

"Susan" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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

"skuzapo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Susan]

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