Using Excel as a Database

  • Thread starter Thread starter UBER_GEEK
  • Start date Start date
U

UBER_GEEK

Dont have access to "Access", but could use Excel as a Database. Does
anyone have any sample code they could share, of quering a database
for a entry, or updating a excel database with a record.

Trying to upgrade from shared spreadsheets.
 
UBER_GEEK said:
Dont have access to "Access", but could use Excel as a Database. Does
anyone have any sample code they could share, of quering a database
for a entry, or updating a excel database with a record.

Trying to upgrade from shared spreadsheets.
Click to expand...

HI Uber

I don't want to pre-empt anyone else's reply to your inquiry but I think
help in the form of code is going to be thin on the ground and
possibly of little or no use to you.

Why ?

What your trying to achieve is a application level solution from ground
zero as it were. So the odd snippet of code is unlikely to
be very helpful in the context of the job and conditions you have locally.
Pardon me if I'm wrong but that's my assessment of your post.

It is totally possible to run a VBA based database within the Excel
environment and for that product to be updated from shared spreadsheets.

The key factor here I think is to get down and do some work learning more
about VBA. I reckon a couple of months focused study
and application for maybe an hour or more a day would equip you with the
knowledge to achieve your project goal.

It is at the point where you are trying and failing you might get maximum
might get benefit from this group.

During that learning process you will encounter difficulties and
imponderables which I'm certain many in this group will be willing and very
able to assist you positively.



Good Luck

N10 :)
 
Whilst I think Excel is a wonderful product, it is not a database
application.
If you feel you need a database application, use one; MySQL is free.
You can then query it from Excel etc as required.

NickHK
 
Hi

It really depends of what "database" you want with your Excel. if you want
somethere to input data in a grid format, you got it already.

However, if you want to limit data type in the columns, then just use
validation. but how about delete some records in table then some data in
other table will also be deleted automatically? i am afraid it's not easy to
achieve in Excel and simply doesn't worth to code it.

Just like you want to use WORD to be an Excel and you create your own field
and use program code to do formula.

Anyway, data validation, list, some worksheets protection feature can help
you to achieve some features of Database.

However, if you want a serious databse, choose get Access then. Excel is
just TOO flexible and powerful.





Generally, I agree with the comment from N10.
 
FWIW, I use a text file as a db. Saving is pretty much instantaneous. For
data retrieval, it can populate 150 rows or so before the user form that
calls it has time to close. This is roughly an order of magnitude faster than
Excel. I wrote this a while back for a project used at work. Don't have the
code and don't remember it well. It was quite specialized for my situation
though.

In general (from memory) it involved the following.

Saving:
1. Define a unique identifier (UI) for each record (date in my case).
2. Define the source cell range (noncontiguous in my case).
3. First equate a text var to the UI.
4. Loop through the text file looking for the UI to see if it already exists.
5. If found, record the file location and querry if should overwrite. Abort
if overwrite refused.
6. Now loop through the cell range concatenating the cell values to the
same text var using a semi-colon delimiter.
7. If UI was not found (step 5) then append the concatenated record to the
file else overwrite the location where found. An entire record goes into one
line in the text file.

Retrieving:
1. Loop through the records in the text file and append the UI's to an
array. Use the InStr function to discriminate the UI from the concatenated
string during the search. Then populate a combo box in a user form with the
array. Example (where arr is an array of UI's from the text file):
Load UserForm1
UserForm1.ListBox1.List = arr
UserForm1.Show

2. User selects a record using the combo box.
3. Use the combo box listindex as an offset or loop through the file again
looing for the record.
4. Retrieve the concatenated record.
5. Use the Split function to quickly create an array of the values.
6. Loop through the cell range and populate the values.
7. I also designed a record deletion option.

To research working with text files, see VBA's Open statement.

Regards,
Greg
 
First question to ask I think is if you want to use SQL in your database. If
you do (and I think that is usually better) than I suggest to go with the
freeware SQLite.
I have an example workbook that shows how to use this
with Excel. Very simple, very fast and very powerful.

If you don't want to go with SQL then maybe text files
could be a good solutions as suggested by Greg Wilson.

RBS
 
Why not download SQL Server 2005 Express?

It's free, it is a full relational database that supports very large
datasets, indexes (clustered and non-clustered), views, triggers, stored
procedures, Transact SQL...

MH
 
Why not download SQL Server 2005 Express?

Have to install the .Net framework.
4 Gb maximum database size.
Not as simple as SQLite.
Not as fast as SQLite.
Probably overkill for the OP's requirements.

RBS
 
Installing the .Net framework is trivial.
Yes, there is a 4Gb maximum database size but who is going to fill that if
they are considering using Excel as a db?
I cannot comment on wether SQL Server Express 2005 is as simple as SQLite to
be honest but I use SQL Server at work so I doubt it would be simpler for me
personally to use.
Again, it may not as fast as SQLite but it does have many features and
reading the list of SQL92 features it does not support, I think I'll stick
with SSE.
SS Express is overkill for the OPs requirements but SQLite is not?

It's up to the OP of course, but SQL Server is a more sought-after skill to
have on your CV.

:o)

MH
 
Of course it all depends on the exact requirements etc., but you asked why
not, so I mentioned some possible reasons.

RBS
 
Back
Top