Should I create the database directly in Excel or use Access and then create a connection?

I have an Excel (Excel 2007) with several tabs which data I need to store for later retrieval. This database will only be used in Excel (retrieving data from the database to different cells in the different tabs). So instead of going via Access and create a link to Excel, couldn't I just create the database directly in Excel with VBA, like he does here:

https://www.youtube.com/watch?v=AZINnj5DPWY 

What advantages and disadvantages would each method have (database directly in Excel or database via Access)?

If I would need to create the database in Access, I would never use it for lookup or anything, only retrieve to Excel to fill different cells in different tabs.

The data is indexed according to horse name, and then links from data about the horse should be made for race date and track etc.
LVL 1
hermesalphaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

benhansonCommented:
Why not just do the whole thing in Access if you are going this route?  What is excel giving you, interface wise, that you can't do, or is more difficult to do in Access?
0
Saqib Husain, SyedEngineerCommented:
If your data size is small/moderate then probably every thing done in excel would be ideal.

However if your data size is large/huge then excel gets slowed down and you would need the help of access.
0
byundtMechanical EngineerCommented:
I've been teaching my daughter how to use tools. She feels comfortable with wrenches and screwdrivers. And after I showed her how a drill works, she's OK with that, too. But she draws the line with the Skil saw. It just looks and sounds far too dangerous.

I feel the same way with Access. I'm sure it's a useful tool, but my mind can't wrap around the notion that any change you make will be applied to the only copy of the file--you can't recover from an oops by closing the database and just not saving it.

If I needed more than one person to be making changes to a file at the same time, then Access is a much better choice than Excel. Or if I were dealing with relational databases or had over a million rows of data, then once again, Access is the way to go. But for garden variety database problems, I will always reach for Excel--and I have never felt there was any performance hit with my doing so.

If you feel proficient in both Access and Excel, then by all means consider both for your task. But if you feel stronger in one than the other--then let that be your guide.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

benhansonCommented:
So I've had plenty of circumstances where users queried SQL using Excel, so thinking about it in that respect, I can understand your question better.

So to answer the specific question, unless you are building, populating, then destroying the access database in VBA, it seems like an over-complication to create it using VBA.  Just build the access database and query from excel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hermesalphaAuthor Commented:
I haven't used Access before and have a lot of formulas, layout details, VBA etc. in Excel that I don't know how to recreate in Access. And the layout and interface in Excel is perfect for my purposes (to get overview and also be able to quickly drill into details with the collapse/expand of different sections.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Personally I think Access is always the best choice for storing data, regardless of how it's displayed. Access has builtin mechanisms that manage data storage and relationships, and it is designed for exactly that purpose. Excel is not (although I've seen plenty of folks who do this).

However, if you're not comfortable with Access you'll spend quite a bit of time learning how to use it. It's much more complex than Excel, and often those who are accustomed to flat-file, non-relational data storage like you find in a spreadsheet have difficulty wrapping their heads around the relation model.
0
IrogSintaCommented:
I haven't used Access before... a lot of formulas, layout details, VBA etc. in Excel that I don't know how to recreate in Access... layout and interface in Excel is perfect for my purposes...
Sounds like you already know your own answer.  

I use Access for database purposes where you present only a subset of data to the user using custom forms so you can have control over what they can change in order to keep the data "clean".  I use it for efficiency - using related tables help minimize duplication (e.g. entering the same department, building number, etc. multiple times);  I also use it for better reporting - I can easily show the same data in many ways (e.g. sales by store, by salesperson, by day) without having to copy the data over again to multiple sheets.

I use Excel for number crunching where I need to do calculations in rows and columns (making use of it's extensive math functions), and to come up with What-If scenarios and projections (because you can quickly see what happens down the line when you make a change), and presenting the data with some really cool looking charts (Access still has the boring charts that Excel used to have).  

I think it's good to know how to use both, that way you can use the right tool for the right job.  If you want to use this as an opportunity to learn Access and see what it can do, then why not take the step?  If you are pressed for time however, then just stick with what you know.

Ron
0
IrogSintaCommented:
any change you make will be applied to the only copy of the file--you can't recover from an oops by closing the database and just not saving it.
@byundt, can you explain what you mean?  If you are talking about the data you enter in Access, that is saved as soon as you move to the next record.  If you are talking about changes you make in designing a form or report, Access will always ask if you want to save your changes just as Excel does. So I don't follow.

Ron
0
PatHartmanCommented:
My husband once drilled a hole through a kitchen cabinet using a screwdriver so he could connect the ice maker in our new refrigerator to the water source at the sink.  It took quite a while and there were some bad words spoken but we were new homeowners and the screwdriver was the only tool in our toolbox.

So, yes, you can have some success with using Excel as a database but that is not its function.  If you are even considering Access, perhaps you innately recognize that the job will be more suitable for a relational database.

Based on your description, I see no need to not go all the way with Access.  Excel isn't bringing anything to the table.  You can use Access to do the data entry and also the reporting.  There are lots of samples out there.  Pretty much anything with a many-to-many relationship will help you understand how to set up your tables.  An Order Entry application contains the type of relationship you need to emulate.  Orders - order details - products.  In your case, one relationship will be Horses -race details - Races.  In both cases the "middle" table is called a junction table and it connects the other two tables.

I can already see the flat Excel file where you add a new column for race every time a new race is run.  That involves changing any formulas that operate horizontally across races.  When you add horses, you have to modify any formula that operates vertically against horses.  Once you move to the relational model, you will no longer be adding "columns" you will only be adding rows (to the three tables) and that means that NO calculations need to be changes.    The problem with the flat model is you only get one attribute per dimension so if you wanted to also log track condition, you don't have any way of doing it but with a relational database you do.  Just add it as an attribute to the race table and then your queries can filter or group by track condition.  You can add any thing else you wanted such as weather and temperature.  Post position would go in the race details table because anything related to both the horse and the race would go into the junction table.

Have fun.
0
[ fanpages ]IT Services ConsultantCommented:
(Disclaimer: I use both products professionally on a daily basis; in some cases MS-Excel is the lead development platform &, in others, MS-Access).

Re: Excel vs Access

Both have their own pros & cons, but ultimately the decision to develop in either comes down to:

1) Security Model required (if any)
2) Quantity of data to be stored (rows x columns) rather than actual byte capacities
3) Speed of development (i.e. costs in short term versus long term)
4) Ease of deployment (into user environment)
5) Stability of product
6) Multi-user usage/capabilities
7) Impact (costs) of Downtime due to failure
8) Familiarity with product/development environment
9) Speed of execution
10) User interface

Given the choice of both products mentioned, personally I find MS-Excel a better product for quick development and more pleasing user interface is MS-Excel (given that users are probably more familiar with a spreadsheet tabular layout than a database with many "tables").

However, if the speed of execution, stability, storage capabilities and security of your data are all primary concerns, then MS-Access is the route to take.

When both solutions have been tested thoroughly, issues with an Access-based variant are ultimately down to data (that can ordinarily be fixed with SQL statement updates), whereas Excel presents a steeper curve of investigation as formulae and lookup-based approaches take time to debug and correct.

The multi-user facilities that MS-Access provides far outweigh the "shared" workbook option in MS-Excel (and Access ultimately proves the more stable platform in this respect).


That said, I prefer MS-Excel as a development tool, but in terms of speed of execution MS-Access wins every time.


In summary, if your users prefer a solution they are at ease with (read: can relate to), that is quicker to develop, but is not as secure/stable then go down the MS-Excel route.  If data integrity and a robust application are paramount at the sacrifice of a lengthier development process, then MS-Access is your tool of choice.
0
byundtMechanical EngineerCommented:
I can already see the flat Excel file where you add a new column for race every time a new race is run.

PatHartman makes a good point. If you decide to use Excel, I suggest that you normalize your data. Normalizing forces you to minimize the number of columns in your data at the expense of repeating certain values on each row.

For example, you might have columns for the name of the race, date, distance, track condition, horse's name, jockey's name, race time and place. The values in the first four columns will be repeated for each horse that competed in a given race, while the last four will be different.

Normalized data can be analyzed using PivotTables or PowerPivot. These tools are fast, and let you slice though large amounts of data to find patterns you can take advantage of when planning bets.
0
hermesalphaAuthor Commented:
I received a reply now from a provider of a service for handicapping harness races, and he tells me that his service downloads data about horses, drivers, tracks and past races from an online repository into his software using MySQL. He furthermore says that this database is completely open and that I can export any data I choose into my Excel. This sounds exactly like the setup I would prefer: a database for extensive information but ability to present selected data in Excel format.

However, now it's about MySQL and not Access, although I took a course several years ago in not MySQL but the other main database software (forgot what it's called).

So I would then have a ready database to work with, wouldn't I? The main issue then would be to learn how to select the data I need to import into my Excel (his database is enormous and comprehend much more information than I am interested in, only need a small amount of those data to fill cells in my Excel). And setup a procedure for this so I just need to click a button when I want to import data (primarily start lists for a current race).

I forgot, I still need to create my own database for storing my Excel data as those are completely different from his data. And for that I could use Access. So the route would be his MySQL database into my Excel and export to Access.

Considering above and considering my limited knowledge of database softwares, would it be better to use Excel + Access than Excel + the other database software (not Oracle, not mySQL but a third)?
0
PatHartmanCommented:
In my opinion, this is a job for a relational database.  However, many database projects can be coerced into Excel so start with Excel and see if you run into any insurmountable problems.

You didn't say how you would be receiving the data.  Will you be downloading a MySQL backup and installing it on your computer, in which case, it should stay in MySQL.  There is no reason to involve Access.  Or will you be requesting extracts and downloading flat files?  If you are downloading flat files, you may as well leave them that way and download a fresh file whenever you need more current data.  If you are only downloading new records, then you should be importing them into Access.  Make sure you specify appropriate primary keys/unique indexes so you don't accidentally import the same download multiple times.
0
tomfarrarCommented:
Having read most of the comments, I didn't see anyone suggest the Power Pivot addin to Excel 2010.  The tool enables one to process more than a million rows of data, and also links different tables similar to using a VLookUp, but without using the VLookUp.  The size of the files in Power Pivot are significantly smaller in Megs as a normal pivot table.  

Here is a you tube on the subject:

https://www.youtube.com/watch?v=ms149Qi2PVA
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.