Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vs. access
odd question, i know, but one that's been bugging me.
i create fairly involved programs. the only process i know is a simple one: inputs are put into an excel spreadsheet. code is written in vb modules (vb editor?) as subroutines (and sometimes functions). this code will crunch the data and output it on another spreadsheet (or wherever it is needed). most programs establish a "database" that keeps up w/ the output. i keep this "database" on another excel spreadsheet. i can refer to it in code and draw anything from it and organize it any way i want (in code). this leads to my question: what can access (or any other database program) do that excel cannot? the only thing that i can think of is my "databases" in excel spreadsheets may run out of room (only 256 columns by 65,536 rows). also, the size of the file may be larger in excel vs. a (compressed?) access database. can i enlarge a spreadsheet's dimensions? i know i can use multiple spreadsheets as my "database", but how hard is it to reference data when it is split up? when data is larger than 256x65,536, is there any other way i can use excel as a database? or do i have to go to access (which i know nothing about) ? thanks, mike allen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vs. access
Access offers a lot of features which you probably don't need if you are
happy using excel. Access is limited to 256 columns as well as I understand it. If your rows are going to get bigger than 65536 rows, then I wouldn't go to multiple pages. I would go to Access. Even if you data was to go above 16K rows, I would consider access. Excel gets pretty slow when dealing with a lot of rows. Access does offer you an SQL interface and this can be much more efficient in retrieving subsets of your data. -- Regards, Tom Ogilvy "mike allen" wrote in message ... odd question, i know, but one that's been bugging me. i create fairly involved programs. the only process i know is a simple one: inputs are put into an excel spreadsheet. code is written in vb modules (vb editor?) as subroutines (and sometimes functions). this code will crunch the data and output it on another spreadsheet (or wherever it is needed). most programs establish a "database" that keeps up w/ the output. i keep this "database" on another excel spreadsheet. i can refer to it in code and draw anything from it and organize it any way i want (in code). this leads to my question: what can access (or any other database program) do that excel cannot? the only thing that i can think of is my "databases" in excel spreadsheets may run out of room (only 256 columns by 65,536 rows). also, the size of the file may be larger in excel vs. a (compressed?) access database. can i enlarge a spreadsheet's dimensions? i know i can use multiple spreadsheets as my "database", but how hard is it to reference data when it is split up? when data is larger than 256x65,536, is there any other way i can use excel as a database? or do i have to go to access (which i know nothing about) ? thanks, mike allen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vs. access
Hi
if you really need more than 65K records of data, you really should consider using a database (Access won't increase the number of columns though). Some benefits of a real relational database - can store large volumnes of data - data integrety can be ensured - you can 'link' tables (depend tables with foreign keys) - You can easily use SQL to query your data (in Excel you may use SQL.REQUEST or formula constructs) So if you have really a large amount of data with dependencies you may consider using a database. Excel is NOT a database though it has some features which seems to indicate this :-) -- Regards Frank Kabel Frankfurt, Germany mike allen wrote: odd question, i know, but one that's been bugging me. i create fairly involved programs. the only process i know is a simple one: inputs are put into an excel spreadsheet. code is written in vb modules (vb editor?) as subroutines (and sometimes functions). this code will crunch the data and output it on another spreadsheet (or wherever it is needed). most programs establish a "database" that keeps up w/ the output. i keep this "database" on another excel spreadsheet. i can refer to it in code and draw anything from it and organize it any way i want (in code). this leads to my question: what can access (or any other database program) do that excel cannot? the only thing that i can think of is my "databases" in excel spreadsheets may run out of room (only 256 columns by 65,536 rows). also, the size of the file may be larger in excel vs. a (compressed?) access database. can i enlarge a spreadsheet's dimensions? i know i can use multiple spreadsheets as my "database", but how hard is it to reference data when it is split up? when data is larger than 256x65,536, is there any other way i can use excel as a database? or do i have to go to access (which i know nothing about) ? thanks, mike allen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel vs. access
In practical terms:
1. Unless you have only modest amounts, storing data in a 'proper' database would allow faster access. 2. Although a single Jet (MS Access) table is limited to 255 columns, you may use more than one table and join them very efficiently in queries. 3. You would probably end up with a better structure to both your data and your application. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
access--excel--access | Excel Programming | |||
How to access ACCESS from Excel! | Excel Programming |