(e-mail address removed) (Rachel) wrote ...
I really don't understand...
If I use VBA or an exteranl program that sends queries to the Excel
database through ODBC, won't it be like any other database?
Can you detail more the missing features of Excel as a database?
It's probably easier to detail the database functionality that Excel
*does* support, using the OLE DB provider for Jet 4.0, being the most
capable Excel provider or driver.
1) CREATE TABLE DDL: limited choice of data types (e.g. only one
numeric type), columns always nullable, no constraints. Creates
workbook level defined Name (and Worksheet as needed). Example:
CREATE TABLE MyExcelTable (
MyCurrencyCol CURRENCY NULL,
MyDateCol DATETIME NULL,
MyBooleanCol LOGICAL NULL,
MyDoubleCol DOUBLE NULL,
MyTextCol VARCHAR(255) NULL,
MyMemoCol MEMO NULL
);
2) INSERT INTO DML: appends one new row of data to the 'bottom' of the
Range/Worksheet subject to space. Expands the defined Name to
accommodate the new row. Example:
INSERT INTO MyExcelTable (
MyCurrencyCol, MyDateCol, MyBooleanCol,
MyDoubleCol, MyTextCol, MyMemoCol
) VALUES (
380, NOW(), NULL,
55.5, 'Test text', 'Test text'
);
3) UPDATE DML: amend values in an existing row. Not able to change a
cell containing a formula. Changing a cell formula's precedent will
not cause recalculation of the formula. Example:
UPDATE MyExcelTable
SET MyBooleanCol = TRUE
WHERE MyTextCol = 'Test text';
4) SELECT..INTO Jet syntax: equivalent to a CREATE TABLE using the
data type of the columns in the SELECT clause and populates using the
data in the result set. Exmaple:
SELECT
MyCurrencyCol AS NewCurrencyCol,
MyDateCol AS NewDateCol,
MyDoubleCol AS NewDoubleCol
INTO MyNewTable
FROM MyExceltable
WHERE MyTextCol = 'Some text';
5) INSERT INTO...SELECT Jet syntax: equivalent to a multi-line INSERT
INTO using the result set. Example:
INSERT INTO MyNewTable (
NewCurrencyCol, NewDoubleCol
)
SELECT
MyCurrencyCol AS NewCurrencyCol,
MyDoubleCol AS NewDoubleCol
FROM MyExceltable
WHERE MyTextCol = 'Test text';
6) DROP TABLE DDL: deletes data and column headers. The worksheet and
the Name definition (if applicable) remain. Example:
DROP TABLE MyExcelTable;
7) SELECT queries: AFAIK Excel supports the same functionality and
limitations as for native Jet tables.
Database functionality that is notably absent from Excel:
1) The Excel 'database' can be opened and manipulated in using the
Excel native app. For example, while a CREATE TABLE enforces data
typing, adding data to the table in the Excel app may cause the data
to become 'mixed' and hence uncertain for future use via SQL DML.
Counter measures may be undertaken in the workbook to help protect its
contents while open in Excel native mode e.g. Data Validation to
restrict a column to one data type.
2) DELETE DML: cannot remove an Excel row. Workarounds include:
setting all columns for the row to null; using SELECT..INTO to create
a new table and DROP TABLE on the original etc.
3) Constraints: required ensure data integrity and entirely absent for
Excel. Examples:
PRIMARY KEY: requires each row in a table to be uniquely identified.
NOT NULL: requires a value to be supplied for a column in every row in
the table.
DEFAULT: applied if no value is supplied when using INSERT INTO.
FOREIGN KEY: enforces declarative referential integrity (DRI).
CHECK: prevents a row being inserted or updated if CHECK condition is
not met.
UNIQUE INDEX: requires row values to be unique for specified
column(s).
4) Permissions: while worksheets may be set to read only, there is
only on 'public' user type i.e. not able to grant specific permissions
to specific users or roles.
5) Locking: workbook (database) level only. Mulitple connections may
be set to read only. Only one connection may be read/write.
6) Controlling data input and output: no support for VIEW or PROCEDURE
functionality.
This is just off the top off my head. Corrections and additions
welcome, of course.
Jamie.
--
