VBA - query an Access table -> results in Excel

Using VBA, I want to query an Access database table which has > 240,000
records. The data I am quering for is from a worksheet (sheet1) and I would
like the results stored in an Excel worksheet (sheet2). The results from the
query will be only a few thousand records, never greater than 10,000. The
Access db and Excel sheet are on the same PC. Eventually I will need to query
the Access DB from a different PC across a network.
What is the best way to do this?

Thanks,

rirus

I'd use ADO This article may help.

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

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"rirus" wrote:
| Using VBA, I want to query an Access database table which has > 240,000
| records. The data I am quering for is from a worksheet (sheet1) and I
would
| like the results stored in an Excel worksheet (sheet2). The results from
the
| query will be only a few thousand records, never greater than 10,000. The
| Access db and Excel sheet are on the same PC. Eventually I will need to
query
| the Access DB from a different PC across a network.
| What is the best way to do this?
|
| Thanks,
|
| rirus

Maybe you can use this
http://www.rondebruin.nl/accessexcel.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"rirus" <[email protected]> wrote in message news:[email protected]...
> Using VBA, I want to query an Access database table which has > 240,000
> records. The data I am quering for is from a worksheet (sheet1) and I would
> like the results stored in an Excel worksheet (sheet2). The results from the
> query will be only a few thousand records, never greater than 10,000. The
> Access db and Excel sheet are on the same PC. Eventually I will need to query
> the Access DB from a different PC across a network.
> What is the best way to do this?
>
> Thanks,
>
> rirus

I set up an ODBC for the Access database and then added a tablequery in the
Excel worksheet. So far that is working great and is getting what I need.
Surprisingly, the query takes seconds to run which is faster than what I
expected. I am not sure though how it will work across a network. Hopefully
the retrieval rate will not change too much.

Thanks for your inputs. They have been helpful.

rirus


"Ron de Bruin" wrote:

> Maybe you can use this
> http://www.rondebruin.nl/accessexcel.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "rirus" <[email protected]> wrote in message news:[email protected]...
> > Using VBA, I want to query an Access database table which has > 240,000
> > records. The data I am quering for is from a worksheet (sheet1) and I would
> > like the results stored in an Excel worksheet (sheet2). The results from the
> > query will be only a few thousand records, never greater than 10,000. The
> > Access db and Excel sheet are on the same PC. Eventually I will need to query
> > the Access DB from a different PC across a network.
> > What is the best way to do this?
> >
> > Thanks,
> >
> > rirus
>
>
>