0

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:

  • check a specified report has a specified title
  • check that specified tables, queries, forms, and reports exist
  • check specific fields in a table exist
  • check that specific fields have been set as the Primary Key

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.

Rick Henderson
  • 326
  • 3
  • 14

2 Answers2

1

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.

rgo
  • 481
  • 4
  • 11
  • Great information Rgo. I'll wait to see if I get more answers before selecting yours if you don't mind. I realize that my coding is probably "old school". Will work on that. – Rick Henderson Apr 06 '15 at 20:28
  • I will however mention that I'm not trying to pull Excel files into Access, I'm trying to go the other way. So your answer doesn't really help. – Rick Henderson Apr 17 '15 at 01:50
0

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.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136