[AccessD] How to Pull Data from an Access Query into Excel with VBA Code in Excel (ADO)

Brad Marks BradM at blackforestltd.com
Sun Nov 25 21:37:39 CST 2012


Bill,

Here are more details...

Provider=Microsoft.ACE.OLEDB.12.0

Dim ADO_Connection As New ADODB.Connection
Dim ADO_RecordSet As New ADODB.Recordset

I have looked at several examples in two books and none of them have a semicolon at the end of the SQL string.  Therefore, my best guess is that my use of the semicolon is what caused the strange results that I saw earlier.

Thanks for your assistance.

Brad 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of William Benson
Sent: Sun 11/25/2012 8:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to Pull Data from an Access Query into Excel with VBA Code in Excel (ADO)
 
To my mind there is no good reason for it not to work however I have found
through ADO the semicolon is not an acceptable character to have at the end
of a statement when I queried an Oracle table. I don't know why I was
getting an "Invalid Character"  message but I was. You didnt say if you
were also trapping an error Mark, you only said that you were not having
records returned. So tjst is why I did not suggest removing the semicolon.
I honestly dont know what the correct use of semicolon is but glad now I
didnt go after the red herring.

You have not specified but I would like to know what is the code you use to
connect to the database.... ie, driver, datasource, provider. Are you using
ADO?

In future I think the additional information could help lead to better
responses, FWIW.

Best regards,

Bill

...grabled by smrat phonn as ususl
On Nov 25, 2012 7:19 PM, "Brad Marks" <BradM at blackforestltd.com> wrote:

> Jim,
>
> I followed your suggestion and placed Query1's SQL on the Excel side of
> the fence and it worked nicely.
>
> I then built a new query in Access (Query2) which worked as I was
> originally trying to do with Query1.
>
> strSQL = "SELECT Field1 From Query2;"
>
>
> I then re-tested query1 on Access and made some minor changes.
>
> To my surprise, Query1 is now working in Excel via
>
> strSQL = "SELECT Field1 From Query1;"
>
>
> I am still not sure why I ran into the original problem or why it is now
> working.
>
> I plan to do some more testing.
>
> Thanks for you help.
>
> Brad
>
> ~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman
> Sent: Sun 11/25/2012 5:31 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] How to Pull Data from an Access Query into Excel
> with VBA Code in Excel (ADO)
>
> Take the SQL from query1 in access and use it on the excel side in the ado
> call
>
> Jim
>
> Sent from my iPhone
>
> On Nov 25, 2012, at 3:39 PM, "Brad Marks" <BradM at blackforestltd.com>
> wrote:
>
> > All,
> >
> > I have just started to experiment with pulling Access 2007 data into
> Excel.
> >
> > I have an test Excel file that uses VBA code to pull the data from the
> Access table.
> > This is working nicely.
> >
> > I would now like to pull data into Excel via the Access query that I
> have set up.
> >
> > I simply changed the SQL Statement in the Excel VBA code like this.
> >
> > strSQL = "SELECT Field1 From Table1;"
> >
> > strSQL = "SELECT Field1 From Query1;"
> >
> > When I now run the Excel VBA code no data is returned.
> >
> > I must be missing something.
> >
> > Does anyone have an example of how to pull data from an Access query
> into Excel via VBA code in Excel?
> >
> > Thanks,
> > Brad
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.




More information about the AccessD mailing list