+ Reply to Thread
Results 1 to 7 of 7

Seeking info on Excel 2013 VBA database capabilities

  1. #1
    Registered User
    Join Date
    10-31-2014
    Location
    NE OH
    MS-Off Ver
    Excel 2007
    Posts
    23

    Seeking info on Excel 2013 VBA database capabilities

    Hello,

    I have a fairly complex VBA app in Excel 2007 that needs to be reworked as a database. I know Access would be the best tool, but I've also read that Excel 2013 has introduced some relational DB features. Is anyone here using those features? Am I correct in assuming that tables can now be indexed and contain foreign key relationships? And where can I find some VBA sample code for SQL-like operations? (I'm not seeing those kinds of methods in the object model).

    My budget is limited so if Excel would suffice I would rather spend the $100 upgrading to Office 2013 than Access.

    Thank you...

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Seeking info on Excel 2013 VBA database capabilities

    Use access, Excel has the concept of a data model, but it is in no way a replacement for a relational database. It also fails with large amounts of data

  3. #3
    Registered User
    Join Date
    10-31-2014
    Location
    NE OH
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Seeking info on Excel 2013 VBA database capabilities

    Well neither size nor speed is a concern - my current Excel file is under 20MB. The problem is that my Excel data is too fragile - I need more rigid data and relationship structuring with referential integrity enforcement. I don't need and wouldn't expect Excel to have a robust database engine or powerful development tools. I've had good results with Access in the past but even much older versions would be overkill for this app (I'm sure development would be much faster, but OTOH if Excel 2013 can handle something like this reasonably well it would be worthwhile to get up to speed with it...)

    I suppose I should mention that I tried out OpenOffice Base - it probably would work but too much of a learning curve to use for just one app...

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Seeking info on Excel 2013 VBA database capabilities

    The data model isn't really about adding data - so I very much doubt it supports any kind of referential integrity or cascading. It's more about combining existing data sources to perform analysis like a mini power pivot.

    It's worth noting of course that if you went down the data model route (which I don't think will work) that only 2013 users will be able to use your workbook

    You could always store your data in sql server and just use Excel as the client if your budget is tight - not to mention the fact that MSSQL is about a gazillion times better database than Access
    Last edited by Kyle123; 10-31-2014 at 12:35 PM.

  5. #5
    Registered User
    Join Date
    10-31-2014
    Location
    NE OH
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Seeking info on Excel 2013 VBA database capabilities

    I took a look at the free express version a year or 2 ago, along with MySQL - I should revisit. I could do some of the front-end data cleansing in Excel, and I would guess there's a good chance I could find sample VBA subroutines to help figure out how to construct and call the SQL commands. I guess my fear is investing a lot of time then not being able to get it to work smoothly. I'm familiar enough with older Access versions to have a high level of confidence in getting it running with minimal effort...

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Seeking info on Excel 2013 VBA database capabilities

    If you search for threads I've started, I wrote a tutorial on this a while ago. Mssql is pretty straight forward, MySQL is a pain since you need to download a load of software from oracle to make it work

  7. #7
    Registered User
    Join Date
    10-31-2014
    Location
    NE OH
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Seeking info on Excel 2013 VBA database capabilities

    I'll check that out this weekend - thanks for the info!

    Edit 1Nov>>> Looks interesting. Since Express 2014 doesn't run on Vista I would have to install on my main Win7 desktop and run as a localhost service - that shouldn't be a problem...
    Last edited by RangeRover; 11-01-2014 at 02:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I import log info from an email into Excel 2013?
    By mwatkins in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2014, 03:34 PM
  2. Seeking Help for Optimization of Database
    By tyler.tqp in forum Excel General
    Replies: 0
    Last Post: 11-06-2013, 02:44 PM
  3. Replies: 2
    Last Post: 01-14-2013, 02:30 AM
  4. Replies: 1
    Last Post: 02-21-2006, 10:30 PM
  5. Use info from excel to update my database
    By oscarooko in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2005, 01:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1