ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Using Excel as my database (https://www.excelbanter.com/excel-programming/316708-using-excel-my-database.html)

Rachel[_6_]
Using Excel as my database
 
Hello all,

I need to use Excel as my database because my company needs to save
the data in flat files.

Can I define tables in Excel?
Can I retrieve/update records matching set criteria from several
tables?
Can I use SQL?


Thanks a lot,
Rachel

Line Riendeau
Using Excel as my database
 
Hi,

it's possible. But don't insert ou delete line or row, you will have to
redefine the table. I already try with a access programm.

"Rachel" a écrit dans le message de news:
...
Hello all,

I need to use Excel as my database because my company needs to save
the data in flat files.

Can I define tables in Excel?
Can I retrieve/update records matching set criteria from several
tables?
Can I use SQL?


Thanks a lot,
Rachel



Frank Kabel
Using Excel as my database
 
Hi
if you have this requirements you really should use a database. e.g. MS
Access.
Also what do you mean with 'has to save as flat file'?

--
Regards
Frank Kabel
Frankfurt, Germany


Rachel wrote:
Hello all,

I need to use Excel as my database because my company needs to save
the data in flat files.

Can I define tables in Excel?
Can I retrieve/update records matching set criteria from several
tables?
Can I use SQL?


Thanks a lot,
Rachel

Joe90[_2_]
Using Excel as my database
 
You can certainly use excel as a database, and read/write/update etc using
ADO, but this requires a fair bit of automation. I think you can access SQL
databases, but I haven't got that far yet. Go to the VBE Editor, then the
VBA Help and search on ADO. Office 200 onwards I believe

Joe

"Rachel" wrote in message
om...
Hello all,

I need to use Excel as my database because my company needs to save
the data in flat files.

Can I define tables in Excel?
Can I retrieve/update records matching set criteria from several
tables?
Can I use SQL?


Thanks a lot,
Rachel



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Jamie Collins
Using Excel as my database
 
"Joe90" wrote ...

You can certainly use excel as a database, and read/write/update etc using
ADO

You can't DELETE using SQL (you can SELECT..INTO a new table and DROP
TABLE on the original but this has limits <g). There are other
missing features e.g. constraints, indexes, reliable data typing,
declarative referential integrity, user accounts for security, etc.

Jamie.

--

Jamie Collins
Using Excel as my database
 
"Joe90" wrote ...

You can certainly use excel as a database, and read/write/update etc using
ADO

You can't DELETE using SQL (you can SELECT..INTO a new table and DROP
TABLE on the original but this has limits <g). There are other
missing features e.g. constraints, indexes, reliable data typing,
declarative referential integrity, user accounts for security, etc.

Jamie.

--

Jamie Collins
Using Excel as my database
 
"Joe90" wrote ...

You can certainly use excel as a database, and read/write/update etc using
ADO

You can't DELETE using SQL (you can SELECT..INTO a new table and DROP
TABLE on the original but this has limits <g). There are other
missing features e.g. constraints, indexes, reliable data typing,
declarative referential integrity, user accounts for security, etc.

Jamie.

--

Jamie Collins
Using Excel as my database
 
"Frank Kabel" wrote ...

if you have this requirements you really should use a database. e.g. MS
Access.

MS Access/Jet would seem to be the way to go. A .mdb file would meet
the requirements for a flat file (it only becomes a relational
database when coupled with the Jet SQL engine).

Jamie.

--

Frank Kabel
Using Excel as my database
 
Hi Jamie
I also would assume the company probably is not that clear what they
mean with 'flat file'. I don't think they want a kind of VSAM data
structure (at least I hope they don't want it...)

--
Regards
Frank Kabel
Frankfurt, Germany


Jamie Collins wrote:
"Frank Kabel" wrote ...

if you have this requirements you really should use a database. e.g.
MS Access.

MS Access/Jet would seem to be the way to go. A .mdb file would meet
the requirements for a flat file (it only becomes a relational
database when coupled with the Jet SQL engine).

Jamie.

Rachel[_6_]
Using Excel as my database
 
Jamie,
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?

Thanks a lot

Jamie Collins
Using Excel as my database
 
(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.

--

Rachel[_6_]
Using Excel as my database
 
Thanks a lot :-)

Jamie Collins
Using Excel as my database
 
(Jamie Collins) wrote in ...

This is just off the top off my head. Corrections and additions
welcome, of course.

Here's another notable omission from Excel database functionality:

ALTER TABLE DDL: example:

ALTER TABLE ADD MyNewCol CURRENCY NULL;

Jamie.

--


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com