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

William Benson vbacreations at gmail.com
Sun Nov 25 23:35:22 CST 2012


Ok that seems like it might be the issue. especially if the query1 ended
with its own semicolon.

Darryl is my go to guy on this issue and if he doesnt use one, I wouldn't
either. It is puzzling that it worked when used with Query2 however. One
thing I have learned is to try not to "assume" based on insufficient
testing or too convenient reasons. So I keep an open mind and l hope in the
future to confirm findings... before reaching full conclusions... but I
never find time to fully research.

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

> 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.
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>


More information about the AccessD mailing list