+ Reply to Thread
Results 1 to 6 of 6

Excel as a front End for SQL server

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

    Excel as a front End for SQL server

    I was recently asked about using a database backend with excel, so here goes ...

    Access is often cited as a useful back-end database for Excel, especially where you want to share data between multiple users at the same time (primarily due to the shortcomings and poor stability of shared workbooks). Whilst this is fair comment, I would always prefer SQL server as a backend to Access, the express version is free, handles concurrency better than access, but more importantly offers stored procedures. In addition if you ever need to upscale the application to a .NET/ Web application, sql server is a much better.

    I've created an Excel front-end / sql back end a few times and it works well, some of the below might be a steep learning curve, but I've tried to be as explicit as possible and keep things straightforward.

    Ok, first things first, you're gonna need a copy of SQL server Express http://www.microsoft.com/en-us/downl....aspx?id=29062, the download includes the tool that you'll be using mostly - SQL server management studio.

    Once you've installed sql server and the management studio and opened it up you'll see the object explorer on the left containing the instance of the sql server you've just installed. At the top left of the screen, there's a button titled new Query, hit it and paste in the contents of the TMS.txt file I've attached. This should recreate the database I have used for this post. It might take a while to run (you can keep an eve on how long it's been running on the taskbar on the bottom right). Once it's finished, if everything's worked, in your database explorer, there'll be a database called TMS.

    The database consists of 3 tables, a Users table (USRUsers), a Condition table (CNDCondition) and a Log table (LOGDataLog). These 3 tables allow us to create a very simple logging application that can be used by multiple users. Essentially users can create logs, in the Log table that capture the user who made the log, the date/time it was made, the condition of whatever is being logged as well as some notes.

    The Log table consists of 5 fields:
    • LOGIDNumber - This is the PRIMARY KEY of the table and is auto-generated, this is not editable and provides us with a constant record that allows us to change any of the fields that relate to that record.
    • LOGLogUser - This is the user that created the log, this field is a FOREIGN KEY, this means that it contains only the record id (PRIMARY KEY) of the user that made the record from the users table. This is the fundamental crux of relational databases, you have the data listed as few times as possible and link records together through the constant KEY of the table that doesn't change. Since, for example, peoples names change if this were hardcoded into every record, when the name changed each record would also need updating. By only holding a reference to the name through the KEY of the table, the name of the person is pulled in from the users table - that means each persons name is only on a single record so it only need's updating in a single place for it to update everywhere.
    • LOGDateLogged - The date and time the log wad created
    • LOGCondition - The condition of the thing being logged, again this is a FOREIGN KEY, but this time to the Condition table (conditions may change and will be used multiple times in the Log table)
    • LOGNotes - any notes that relate to the specific log entered

    The Users Table consists of 5 fields:
    • USRUSerID - The PRIMARY KEY of the table, used in other tables (LOGDataLOG) to link to a particular user
    • USRFirstName
    • USRSurname - Note that I've stored these seperately (as opposed to first name and surname in one field), this is since it's often useful to order by surname
    • USRisActive - A flag as to whether the user is active or not, since the log table holds a reference to the users table, we can't just delete a user or the record in the log table that belongs to that user will be deleted also - we can however make them invisible to our application by flagging this as False (or specifically 0 since SQL server doesn't use booleans)
    • USRNetworkID - It's useful to capture the windows log on of a user, that we we can identify them without them needing to log in to our application

    The Condition table consists of 3 fields:
    • CNDIDNumber - The PRIMARY KEY
    • CNDCondition - The description of the condition
    • CNDIsActive - flag as to whether the condition can be used or not (the same as with users)

    So far all of this could have been accomplished in access, this is where we veer off course slightly. To talk to database we use a language called SQL, this is actually pretty simple http://www.w3schools.com/sql/default.asp, however you can do some really complex things with it. When using access from Excel, it's common to write sql in VBA and send it to the database to return / update / insert data. I don't like writing sql in code, it's messy/fiddly and hard to debug it's also painful to change if you re-structure something in your database. Instead, stored procedures offer a layer of abstraction. Fundamentally, they're just functions, that you can pass parameters into and they do things on the database and may or may not return a result. This means that you can write your sql in SQL Server Management Studio and just call functions to execute it - AFAIK stored procedures aren't part of the general set up of access (but I haven't used it since the 2003 version).

    As well as being easy to call from VBA, stored procedures also let you change the database without having to change your vba code, you just change the internals of the function you've written. If you have a look in programmability in the database object explorer window, then in Stored Procedures, you'll see that I've created a few for interacting with the database to perform the most common requirements - adding new and updating existing new users for example. If you want to have a look at them, right click on one and click modify.

    So now we have a base database, back to Excel

    To talk to sql server, we need to use a library called Active Data Objects. so you need to add a new reference to Microsoft Active Data Objects (use the highest number you've got). This allows us to send and receive data from all kinds of databases (not just sql server). In the attached workbook, I've only used 2 objects from the library - ADODB.Connection (this creates a connection to the database) and ADODB.Recordset (all data recieved from a database is in a recordset, which is like a table, it has rows and columns (fields) which are generated from the underlying tables).

    The most fiddly bit of getting sql server talking to excel is usually the connection string - this just tells Excel where the database is and how to connect to it. By default SQL server express uses Windows authentication - this means that you probably won't use a username and password, but you can certainly set one up - you just need to alter your connection string.

    The connection string should look something like :
    Please Login or Register  to view this content.
    Database is the name of the database and the server is the name of the computer you installed sql server on, if you're struggling with this, go back to the database object explorer and look right at the top, it will have the computer name and the instance that you need to use.

    I need to go now, so I'll put up part two when I get chance - in the meantime I've attached a copy of a working workbook
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Excel as a front End for SQL server

    was a part 2 ever published? This is extremely useful!!

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

    Re: Excel as a front End for SQL server

    Er no, I never got round to writing it, but I'll see if I can put something together over the next couple of days if its useful. Otherwise, if you have any specific queries, feel free to start a thread and link to this

  4. #4
    Registered User
    Join Date
    06-05-2013
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Excel as a front End for SQL server

    Wow Thank you sir... :D :D :D :D

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

    Re: Excel as a front End for SQL server

    No problem, gald it helped - could you follow it enough to get it working?

  6. #6
    Registered User
    Join Date
    06-05-2013
    Location
    melbourne
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Excel as a front End for SQL server

    Hi Kyle, I am having issues with the connection string. I get an error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

    I am using SQL Server Express 2012
    Microsoft Excel 2010
    Windows 7 64 bit OS

    Do I need to install any specific driver. I have tried to tick all the relevant libraries in VBA using tools-> references.

    Don't know what else to do?

    Can you please help me with it?

    If anyone else can help me with this that would be great.

    Thanks.
    Last edited by behumble; 07-13-2013 at 09:39 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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