Is it unprofessional to use a Microsoft.ACE.OLEDB data connection to perform an SQL query on an open Excel Workbook?

Hi,

This is more something that I'd like some advice on than a problem. I'd like to get an expert's opinion on the use of a Microsoft.ACE.OLEDB data connection within Excel to perform an SQL query on a table in an open Excel workbook to produce output in the same workbook. I believe it is deemed unprofessional to do this, but it seems to be a really useful thing to be able to do. It seems to have served me well, so I'm interested to hear an explanation as to why it's an inadvisable approach and what the best alternative is. One alternative I've experimented with is using a temporary closed workbook to read the data from before deleting it, but it seems excessive incorporating this procedure into a macro. Here is an example of code I might use to perform an SQL query on an open Excel workbook:

Sub TestTable()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim ConnStr As String
Dim StrQuery As String
Dim Output As Worksheet

Set Output = ThisWorkbook.Worksheets("Output")
Output.UsedRange.ClearContents

ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & ThisWorkbook.FullName & "; Extended Properties='Excel 12.0 Xml; HDR=YES';"

StrQuery = "SELECT * FROM [Table1$]"

cnn.Open ConnStr
rst.Open StrQuery, cnn, adOpenStatic

Output.Cells(2,1).CopyFromRecordset rst

End Sub

Open in new window

Rowan BaruaPricing AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
As far as I am aware that is an acceptable approach to access embedded tables, and leveraging the power of SQL.  You can even JOIN multiple tables which is pretty cool.

The alternative (that I actually more often use for the small amount of tables stuff I have done) is to use the ListObject, which can be a bit faster and lighter weight.



»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowan BaruaPricing AnalystAuthor Commented:
Thanks Bill. Glad someone endorses this approach. I use the ListObject for single tables, but I find it really useful to use an ADODB connection for performing joins of multiple tables as you say.
Rowan BaruaPricing AnalystAuthor Commented:
Bill's comment reassures me that this approach is professionally acceptable.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rowan BaruaPricing AnalystAuthor Commented:
Sorry to bring this back to top, but I've read another post by someone claiming that ADO queries on open workbooks cause memory leakage. I've found it really useful to be able to query open workbooks and found that queries have been less efficient when I've adapted macros to transfer data to a temporary closed workbook before performing the query. However, I do have the hang-up that I'm being unprofessional. Are there any circumstances under which querying an open workbook is acceptable? Is it sometimes the case that a bit of memory leakage isn't the end of the world if it means a job gets done quicker? What is the best alternative? Interested to get opinions on this.
Bill PrewIT / Software Engineering ConsultantCommented:
Can you share the link you mention?  I can't find any recent references to active memory leak problems in ACE/OLEDB related to Excel ADO usage.  I found a JET related one, but that should be different than what you are using.


»bp
Rowan BaruaPricing AnalystAuthor Commented:
Sure. Hopefully it's a problem that has disappeared. I'll be delighted if it is. Thanks for the response.

Here's the link: https://www.excelforum.com/excel-programming-vba-macros/981510-are-multiple-4-left-joins-supported-by-adodb.html

It is from 2014.
Bill PrewIT / Software Engineering ConsultantCommented:
Yes, that's four year old link, I wouldn't worry too much about that, a lot has changed since then.  If there were a lot of current reports of leakage I'd be concerned (or if your real world usage exhibited it), but I didn't find those.


»bp
Rowan BaruaPricing AnalystAuthor Commented:
That’s made my day! Thank you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.