views:

8228

answers:

13

I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they would like to be able to query but I do not want to use excel as a database as I don't think it is fit for that purpose and am considering using Access. [BTW, I know Access has its shortcomings but there is zero budget available and Access already on friend's PC]

To summarise, I am considering dumping a bunch of data into Access and then using Excel as a front end to query the database and display results in a userform style environment.

Questions:

  1. How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?
  2. Do I pay a performance penalty (vs.using forms in Access as the UI)?
  3. Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?
  4. Any other things I should be aware of?

I have strong Excel VBA skills and think I can overcome Access VBA quite quickly but never really done Excel / Access link before. I could shoehorn the data into Excel and use as a quasi-database but that just seems more pain than it is worth (and not a robust long term solution)

Any advice appreciated.

Alex

+5  A: 

If the end user has Access, it might be easier to develop the whole thing in Access. Access has some WYSIWYG form design tools built-in.

Scott
+1; if you use the datasheet views in Access you can get the look and feel of a spreadsheet. I'm sure you could do this with Excel, but it feels like it would be a lot of trouble for little benefit.
Patrick Cuff
A: 

If you're looking for a "robust long term solution", how about you use a real database with a real user interface? It's shockingly easy to develop database driven ASP.NET applications, and this would allow more flexibility in the future.

Alex Fort
Ok. I did consider this and this would be ideal. But if user does not have .NET framework installed I cannot delpoy the application to thier PC as far as I know.
-1 because a "real" database like SQL Server would cost money. And a "real" interface is more difficult to build. You can alway migrate from Access later. Access is designed for small projects like this.
epochwolf
SQL Express is free.
TheTXI
MySQL is also free. SQL Express has a limit to how many connections can exist, so that may not be an ideal solution.
Alex Fort
-1 Access bashing isn't helping this guy with his problem. Plus I'd put the Access UI up against an ASP.NET UI for functionality any day.
JohnFx
This isn't as "bashing" as it looks (not the typical - you are doing it wrong answer). The OP did asks for general advice, not just how to do it in MS Access.
CodeSlave
It also doesn't solve the problem, because the issue here is not where the data is stored, but what you use for building the UI, Excel or Access.
David-W-Fenton
+6  A: 

Just skip the excel part - the excel user forms are just a poor man's version of the way more robust Access forms. Also Access VBA is identical to Excel VBA - you just have to learn Access' object model. With a simple application you won't need to write much VBA anyways because in Access you can wire things together quite easily.

DJ
+14  A: 

I'm sure you'll get a ton of "don't do this" answers, and I must say, there is good reason. This isn't an ideal solution....

That being said, I've gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn't talk around it.

Here are a few things to consider with this:

How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative?

It's fairly straitforward. You're more limited than you would be doing things using other tools, since VBA and Excel forms is a bit more limiting than most full programming languages, but there isn't anything that will be a show stopper. It works - sometimes its a bit ugly, but it does work. In my last company, I often had to do this - and occasionally was pulling data from Access and Oracle via VBA in Excel.

Do I pay a performance penalty (vs.using forms in Access as the UI)?

My experience is that there is definitely a perf. penalty in doing this. I never cared (in my use case, things were small enough that it was reasonable), but going Excel<->Access is a lot slower than just working in Access directly. Part of it depends on what you want to do....

In my case, the thing that seemed to be the absolute slowest (and most painful) was trying to fill in Excel spreadsheets based on Access data. This wasn't fun, and was often very slow. If you have to go down this road, make sure to do everything with Excel hidden/invisible, or the redrawing will absolutely kill you.

Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.?

You're pretty much using Excel as a client - the same way you would use a WinForms application or any other tool. The ADO/DAO clients for Access are pretty good, so you probably won't run into any concurrency issues.

That being said, Access does NOT scale well. This works great if you have 2 or 3 (or even 10) users. If you are going to have 100, you'll probably run into problems. Also, I tended to find that Access needed regular maintenance in order to not have corruption issues. Regular backups of the Access DB are a must. Compacting the access database on a regular basis will help prevent database corruption, in my experience.

Any other things I should be aware of?

You're doing this the hard way. Using Excel to hit Access is going to be a lot more work than just using Access directly.

I'd recommend looking into the Access VBA API - most of it is the same as Excel, so you'll have a small learning curve. The parts that are different just make this easier. You'll also have all of the advantages of Access reporting and Forms, which are much more data-oriented than the ones in Excel. The reporting can be great for things like this, and having the Macros and Reports will make life easier in the long run. If the user's going to be using forms to manage everything, doing the forms in Access will be very, very similar to doing them in Excel, and will look nearly identical, but will make everything faster and smoother.

Reed Copsey
+1; Well said, well spoken.
Patrick Cuff
+1 good and detailed answer, but sometimes, an Excel interface is more convenient, based on the purpose of the application
Martin
+2  A: 

Unless there is a strong advantage to running your user form in Excel then I would go with a 100% Access solution that would export the reports and data to Excel on an ad-hoc basis.

From what you describe, Access seems the stronger contender as it is built for working with data:
you would have a lot more tools at your disposal to solve any data problems than have to go around the limitations of Excel and shoehorn it into becoming Access...

As for your questions:

  1. Very easy. There have been some other questions on SO on that subject.
    See for instance this one and that one.

  2. Don't know, but I would guess that there could be a small penalty.
    The biggest difficulty I see is trying to get all the functionalities that Access gives you and re-creating some of these in Excel.

  3. Yes, you can have multiple Excel users and a single Access database.
    Here again, using Access as a front-end and keeping the data in a linked Access database on your network would make more sense and it's easy as pie, there's even a wizard in Access to help you do that: it's just 1 click away.

Really, as most other people have said, take a tiny bit of time to get acquainted with Access, it will save you a lot of time and trouble.
You may know Excel better but if you've gone 80% of the way already if you know VBA and are familiar with the Office object model.

Other advantages of doing it in Access: the Access 2007 runtime is free, meaning that if you were to deploy to app to 1 or 30 PC it would cost you the same: nothing.
You only need one full version of Access for your development work (the Runtime doesn't have the designers).

Renaud Bompuis
+3  A: 

I do this all the time. If you're using ADO, you're not really using Access, but Jet, the underlying database. That means anybody with Excel can use the app - Access not required. Oh I should mention, the place I work bought a bunch of Office Small Business licenses - no Access. Prior to working here, I would have assumed that anyone who had Excel would also have Access. Not so.

I create one class for every table in Access. I very rarely run queries through ADO, instead I keep that logic in the class modules. I read in with a SELECT statement and write out with and UPDATE or INSERT using the Execute method of the ADODB.Connection object.

See http://www.dailydoseofexcel.com/archives/2008/12/21/vba-framework-ii/

if you want to see how I set up my code.

To answer your questions: It will be a small learning curve for you if you already know Excel VBA, but there will be some learning to do; you will pay a performance penalty over doing it all in Access, but it's not that bad and only you can decide if it's worth it; and you can have multiple people accessing the database.

Dick Kusleika
Agreed, if you are familiar with Excel VBA and familiar with the Access UI then the learning curve will be smaller sticking with Excel. There's no reason to assume that performance will be significantly slower in absolute terms than via Access Forms.
onedaywhen
Could you clarify that comment? How could using Jet as data store and creating your UI in Excel be slower than creating a UI in Access for a Jet data store?
David-W-Fenton
Typo: I meant to say, "if you are familiar with Excel VBA and NOT familiar with the Access UI..."
onedaywhen
Creating your UI in Excel be slower than creating a UI in Access if you are unfamiliar with Excel.
onedaywhen
I don't know if it's slower, I just always assumed. I'm not even sure how I'd test it - an Access form bound to a query vs. running SQL statement through ADO or DAO.
Dick Kusleika
A: 

Given the ease of use of Access, I don't see a compelling reason to use Excel at all other than to export data for number crunching. Access is designed to easily build data forms and, in my opinion, will be orders of magnitude easier and less time-consuming than using Excel. A few hours to learn the Access object model will pay for itself many times over in terms of time and effort.

pro3carp3
A: 

It really depends on the application. For a normal project, I would recommend using only Access, but sometimes, the needs are specific and an Excel spreadsheet might be more appropriate.

For instance, in a project I had to develop for a former employer, the need was to give access to different persons on forms(pre-filled with some data, different for each person) and have them complete them, then re-import the data.

Since the form was using heavy number crunching, it made more sense to build it in Excel.

The Excel workbooks for the different persons were built from a template using VBA, then saved in a proper location, with the access rights on the folder.

All workbooks were attached as External tables to the workbooks, using named ranges. I could then query the workbooks from the Access Application. All administrative stuff was made from the db, but the end users only had access to their respective workbook.

Developping an Excel/Access application this way was a pleasant experience and the UI was more user-friendly than it would have been using Access.

I have to say that in this case, it would have taken a lot more time doing it in Access than it took using Excel. Also, the Application Object Model seems better though in Excel than in Access.

If you plan to use Excel as a front-end, do not forget to lock all the cells, but the editable ones and don't be affraid to use masked rows and columnns (to construct output tables for the access database, to perform intermediate calculations, etc).

You should also turn off autocalculation while importing data.

Martin
A: 

To connect Excel to Access using VBA is very useful I use it in my profession everyday. The connection string I use is according to the program found in the link below. The program can be automated to do multiple connections or tasks in on shot but the basic connection code looks the same. Good luck!

http://vbaexcel.eu/vba-macro-code/database-connection-retrieve-data-from-database-querying-data-into-excel-using-vba-dao

A: 

It Depends how much functionality you are expecting by Excel<->Acess solution. In many cases where you don't have budget to get a complete application solution, these little utilities does work. If the Scope of project is limited then I would go for this solution, because excel does give you flexibility to design spreadsheets as in accordance to your needs and then you may use those predesigned sheets for users to use. Designing a spreadsheet like form in Access is more time consuming and difficult and does requires some ActiveX. It object might not only handling data but presenting in spreadsheet like formates then this solution should works with limited scope.

Sajid Iqbal
A: 

You could try something like XLLoop. This lets you implement excel functions (UDFs) on an external server (server implementations in many different languages are provided).

For example you could use a MySQL database and Apache web server and then write the functions in PHP to serve up the data to your users.

BTW, I work on the project so let me know if you have any questions.

Peter Smith
What does this answer have to do with the question?
David-W-Fenton
err, it's a very simple way to get dynamic data into excel. the question was about using excel as a front-end to access data in a database.
Peter Smith
@Peter Smith: There are four specific sub-questions. Why not do your project a favor and explain in detail how it addresses each of those questions in turn? That is, give me a reason to follow your link.
David-W-Fenton
You're recommending an awful lot of significant changes, i.e., after chucking Access, 1. installing/maintaining MySQL, 2. installing/configuring Apache, 3. programming in PHP. You should clarify in your answer that all of these things are well beyond the implied scope of the question being asked. Except for Excel, your tool requires chucking everything else involved, installing a bunch of software on a server and then programming in a language the original questioner likely is unfamiliar with. Knowing that now, I'd downvote a second time, were I able.
David-W-Fenton
Actually, its not as difficult as you make it sound - WampServer is a free one click install for windows and gives you MySQL, Apache and PHP. http://www.wampserver.com/en/But this was just an example, there are many other language options with XLLoop, eg Java, Ruby, Python. I was just highlighting that just because Excel is required as a front-end it doesn't mean you are tied to MS products for a back-end.
Peter Smith
A: 

Alex,

Using the add-in I have created you can query a database directly from Excel and bring the results back to Excel. It's called QueryCell. www.QueryCell.com

It might be worth checking out.

Cheers Sam

SamH
A: 

It's quite easy and efficient to use Excel as a reporting tool for Access data. A quick "non programming" approach is to set a List or a Pivot Table, linked to your External Data source. But that's out of scope for Stackoverflow.
A programmatic approach can be very simple:

strProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";"
Set cnn = New ADODB.Connection  
cnn.Open strProv
Set rst = New ADODB.Recordset
rst.Open strSql, cnn
myDestRange.CopyFromRecordset rst

That's it !

iDevlop