Using Excel as front end to Access database with VBA

Question

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    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  Do I pay a performance penalty  vs using forms in Access as the UI   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   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

User · Answer

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    amp  SourceFile  amp      Set cnn   New ADODB Connection   cnn Open strProv Set rst   New ADODB Recordset rst Open strSql  cnn myDestRange CopyFromRecordset rst   That s it

User · Answer

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 lt - 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

User · Answer

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

User · Answer

It Depends how much functionality you are expecting by Excel lt - 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Very easy  There have been some other questions on SO on that subject  See for instance this one and that one  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  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

User · Answer

I did it in one project of mine  I used MDB to store the data about bills and used Excel to render them  giving the user the possibility to adapt it   In this case the best solution is    Not to use any ADO DAO in Excel  I implemented everything as public functions in MDB modules and called them directly from Excel  You can return even complex data objects  like arrays of strings etc by calling MDB functions with necessary arguments  This is similar to client server architecture of modern web applications  you web application just does the rendering and user interaction  database and middle tier is then on the server side  Use Excel forms for user interaction and for data visualisation  I usually have a very last sheet with some names regions for settings  the path to MDB files  some settings  current user  password if needed etc   -- so you can easily adapt your Excel implementation to different location of you  back-end  data

[excel] Using Excel as front end to Access database (with VBA)

Examples related to excel

Examples related to ms-access

Examples related to vba