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.