Database And Excel

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

I am currently using excel to manage a small database. The problem is
my database is getting big and I need more then the 65000 lines excel
can have.

I have considered access but it lacks some important built-in features
from excel. For exemple:

- Easily Add multiples similar entry with incremental numbers
- Add colors easily
- Display Conveniency

Maybe I just don't know how to use acces in this case I would
appreciate a help as to where I could find info to do what I want
Else is there a software that could fill my needs for such a database.

In fact what I need is something usefull to enter data and to display
it. The database format itself is not really important.
Thx in advance for your help
 
Access has conditional formatting, which will colour the fields in a
form, and you can use code to create incremental numbers, or an
autonumber field.

I'm not sure what you mean by 'display conveniecy'

If you need just a simple table, and data entry form, you should be able
to set those up fairly easily in Access. You could ask for advice in one
of the Access newsgroups, and someone could probably offer suggestions
for getting started.
 
This sounds more like an Access question than excel.

If the Excel users who dabble(?) in Access don't offer any suggestions, you may
want to head over to an Access newsgroup--where Access users who dabble in Excel
can help.
 
The 65536 row limit is on a single worksheet, but the number of worksheets
in a workbook is limited only the amount of memory you have.

Can you move some of your data onto another sheet in the same workbook?

You'll never be able to view all 65536 rows at once, so I'm assuming you
have some useful filtered views that you've developed. All you need to do it
get the filtered view to select from both (or multiple) sheets.

It will run like a snail, but that'll be what drives you to move it to
Access.

Brian Lowe
---------@
 
It is possible to display multiple worksheets on the same page using
filters? That would be a really good alternative while we eventualy
move to access. Thx for the info. I'll have to look into that
 
(e-mail address removed) wrote ...
I am currently using excel to manage a small database. The problem is
my database is getting big and I need more then the 65000 lines excel
can have.

I have considered access but it lacks some important built-in features
from excel.
Click to expand...

MS Access is unusual in that it is both a database management system
(DBMS) and a rapid application development (RAD) tool. Consider using
it as the former and not the latter. In other words, use the database
to store the raw data, because that's what it does best, and use Excel
to edit, calculate and display the data, because that's what it does
best.

Jamie.

--
 
(e-mail address removed) wrote ...


MS Access is unusual in that it is both a database management system
(DBMS) and a rapid application development (RAD) tool. Consider using
it as the former and not the latter. In other words, use the database
to store the raw data, because that's what it does best, and use Excel
to edit, calculate and display the data, because that's what it does
best.

Jamie.

--
Click to expand...

That seems a good compromise. I was wondering if you could give me
some input where I could look to use Excel As the editor and Acces
(wich I found is infact a Jet Database) to store the data
 
(e-mail address removed) (John Smith) wrote ...
I was wondering if you could give me
some input where I could look to use Excel As the editor and Acces
to store the data
Click to expand...

There's no general answer, it all depends on how you are currently
using you data. That's not offered as a cop out: provide some more
details of your situation and I'll elaborate.

If you are not already doing so, the following would be a useful
exercise. Put your Excel data on its own separate hidden worksheet.
De-couple your data access routines from your other application
routines e.g. create methods such as ReadData and WriteData from the
perspective that the code within them could change if the data source
changes but it wouldn't affect the rest of the application. For
example, your ReadData method could be a function that takes a Range
object or cell address as an argument, writes the data to that range
and reports back how many rows were fetched. Later you could
internally change ReadData to populate the hidden sheet from an
external data source by using some criteria to filter the data to less
than the 64K row limit, or get the data from multiple sheets in the
same workbook, or get the data from another workbook, or write the
filtered data direct from an external source to the target range etc.

The popular tools for the job are MS Query for GUI tools and ADO for
VBA code. MS Query is really only for one way traffic into Excel; if
you need to write to the database, go straight to ADO. There are many
code examples out there which should give you some inspiration.
Acces (wich I found is infact a Jet Database)
Click to expand...

The one time I don't pedantically point that out <g>. I should let it
go more often.

Jamie.

--
 
Back
Top