Can Excel use itself as a Database?

Question

I found an interesting article at MSDN, which says:

ADO makes it possible to treat an Excel workbook as if it were a database.

So is it possible for an Excel workbook to connect to itself, and treat one of its worksheets as a database table and execute queries on it? -- and is this possible via VBA programming?

Answer 1

Can Excel use itself as an RDBMS?

No, but you could use it as a "DBMS" with no relational features.

So is it possible for an Excel workbook to use itself as a database?

Without going into design, DRI, BCP, HA etc... 2 major blocks:

• Row limits
• File is exclusively locked, no sharing so single user

After comments:

Whether it supports the relational model is irrelevant, it does not have features one would expect in an RDBMS: PKs, FKs, triggers, contraints, defaults, etc

On that basis, how would I uniquely indentify a row?

The row number does not: surrogate keys still need a unique constraint (not supported in Excel) to ensure the natural key is unique.

Answer 2

yes you can connect to an excel file using DAO or ADO, from within VBA. Is sometimes a useful trick. For example I distibute a summary report to several users, and using this trick I can provide transactional data in a separate sheet and with VBA provide drill down from summary to detail.

There are Microsoft support pages that show you how to do it.

your link shows the basics with ADO. It was the one I used!

this is code from my example using DAO. You can see how it connects and an example of assembling a query.

Dim mPath As String
Dim mName As String
Dim ColcnT As Long
Dim C As Long
Dim RecCnt As Long
Dim mPeriod As String

mPath = ActiveWorkbook.Path
mName = ActiveWorkbook.Name
mPeriod = Range("mperiod")

Dim dbtmp As dao.Database
Dim tblobj As dao.TableDef
Dim rs As Recordset
Dim qd As dao.QueryDef



Set dbtmp = OpenDatabase(mPath & "\" & mName, False, True, "Excel 8.0;")

DoEvents
If PeriodType = 1 Then Set qd = dbtmp.CreateQueryDef("", "SELECT * FROM mDrillDATA WHERE (((SubOwner1)=[msubowner]) AND ((ACC)=[mACC]) AND ((period)=[mperiod]))")
If PeriodType = 2 Then Set qd = dbtmp.CreateQueryDef("", "SELECT * FROM mDrillDATA WHERE (((SubOwner1)=[msubowner]) AND ((ACC)=[mACC]))")
    qd.Parameters("msubowner") = mOwner
    qd.Parameters("mACC") = mACC
    If PeriodType = 1 Then qd.Parameters("mperiod") = mPeriod
Set rs = qd.OpenRecordset(dbOpenDynaset)

Answer 3

It's possible but it would be a really really bad idea! You should use a real database if you need a database. You want things like datatypes and indexes and PK/FK constraints and the ability to easily query and limit the kind of information placed in the datbase. I have never yet seen an Excel spreadsheet that had the kinds of restrictions on data you need for real database information to have data integrity.