CODEWITHSUNDEEP
excelvbadaoadodb
Rick Henderson
Rick Henderson

Reputation: 326

Though deprecated, is DAO still used for automation of Access databases from Excel?

I'm trying to wrap my head around it. I've checked other questions and nothing seems to be too similar.

The Office 2013 development centre contains extensive DAO examples and states it is one of the easiest ways to work with an Access file (does not require an Access window) but DAO is a deprecated technology. (https://msdn.microsoft.com/en-us/library/office/ff834801.aspx)

I'm trying to write an Excel Add-in (eventual end point) that will grade Access assignments in .accdb format.

I'm can't just use ADODB to perform SQL queries to extract data, unless SQL can also do the following:

I also need to check that certain values exist in a table, but those I can solve with SQL.

So should I be using DAO or stick with ADODB? Remember, I'm using Excel, not programming in Access VBA.

Upvotes: 0

Views: 1177

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15357

Microsoft originally deprecated DAO in favor of ADO, but recently renamed DAO to Microsoft Access Engine (ACE) and is now pushing it as the preferred data access technology for Automation-supported environments (VBA, WSH etc.)

In general, in your scenario I don't think it makes much of a difference which one you use. I suggest you read through this.

I don't think it's possible to read table/query/field information via SQL. However, this can be done either with the DAO.TableDefs and DAO.QueryDefs collection, or the ADO.OpenSchema method.

RE: forms/reports - I don't know if it's possible to read form/report information via DAO or ADO even without SQL, as it is actually part of the UI objects, and not the data; unless we're talking about reading/parsing the system tables. You may have to open the database in a hidden Access instance and read the forms/reports that way.

Upvotes: 0

rgo
rgo

Reputation: 491

The simplest way to work with excel tables is to link them in Access either manually or via the TransferSpreadsheet function in VBA. If you use a generic naming standard for the file names and tab names in Excel, you will not have to relink, rather you can replace the Excel file and the Access link will read the new file unless the layout has changed.

Once linked you can use the query-by-example tool to write your queries which also can be written into code (i.e. either embedded in the VBA [old school and not a best practice] or saved in an Access table then looked up and assigned to a variable for use with the CurrentDb.Execute strSQL, dbFailOnError command.

I suggest manually linking the first go round so you can manually define the individual column types vs. letting Access mess it up.

If you cannot use generic names and have to pick the file via a browse dialog, you can get a file browse function via Google to allow you to programatically link the file. (or you can rename it to a generic name which works even better)

So DAO vs. ADODB is moot from an Access perspective. I supported 50 databases with 70,000+ lines of code and dozens of Excel source files the past 4 years and never had to ponder the question.

Upvotes: 1

Related Questions