Home » Visual Studio

Excel as front end and SQL Server as back end

I read some documentation on the web today about using Excel as front end and
SQL Server as back end.

Actually, this link was pretty good:

This is pretty good too: http://bytes.com/topic/sql-server/answers/486783-updating-sql-server-Table-using-excel

This is very good; gets pretty complicated though:

I’m still pretty confused about how it all works. I found a sample of VBA
online that lets me import a couple columns from a Table:
Sub Import()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "select LastName, FirstName from Employees"
' Set up the connection string, reference an ODBC connection
connstring = _
' Now implement the connection, run the Query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=Range("A1"), Sql:=sqlstring)
End With
End Sub

That’s pretty slick, actually! However, I’m thinking I need to use Excel to
LINK to SQL Server, not COPY or IMPORT. Basically, I’m trying to find an
easy way to use Excel as a front end interface so that a user may make
changes in Excel and save all changes back to SQL Server? This should most
likely update a Query and have this Query update several Tables, I would
think. I believe this would be some type of ‘append Query’ or I would
somehow append new records. I do a lot of work in MS Access and if I was
using an Access Form, I’d connect the Form to a Query, make changes to
records in the Form, and save the changes back to the Query, which
subsequently updates the data in the underlying Tables. How can I do
something similar, but use Excel as a front end and SQL Server as a back end?

I’m using Excel 2007 and SQL Server 2008 Express.

I’d definitely appreciate any/all help with this! I guess if I could just
see some simple examples, step by step, and get something working, I could
probably figure out the rest.


4 Answers Found


Answer 1

Since you are using Excel 2007, so try this, Click Menu "Data->From Other Sources->From SQL Server", then just follow the wizard and fill the information required.

Answer 2

Right, that's very easy.  However, I need to link  to SQL Server, not IMPORT from SQL Server.  Basically, I'd like to open an Excel workbook that is linked to SQL Server, make a few changes in the spreadsheet, and then run  some code and see the updates in SQL Server.  I know it's possibly because I've seen it done before, but I don't have the code to do it, so I'm looking for some sample  code to get started.


Answer 3

Hi Ryan!

I'm looking for the same VBA code based solution. I've also bumped to the links above during my web  research. To me this problem should be somewhat standartized as the solution itself is not that difficult to implement technically, but the problem is in customization.

Did you make any progress with your project? If so please let me know the outcome.

Thanks in advance,



Answer 4


Were either of you able to get this working?





I splitted my access 2007 database into back-end/front-end files. From another computer, belonging to the same workgroup as the computer that holds the back-end file, I am currently trying to open the front-end file and access return this error message: The Microsoft office Access database engine cannot open or write to the file "path\file_name." It is already opened exclusivly by another user, or need permission to view and write its data.

path\file_name: is the file name that I put while spliting the database. I am sure the path is correct.

My question is to know if I there is a way to define permission as far as who can access the back-end file from the network.

Thanks for helping.




As you know there are front-end tasks and back-end tasks associated with the Web. What could we consider ASP.NET, is it suited only for front-end tasks or it could reach to write any "sort of middle-layer system for a three-tier architecture"?



Looks like MS Access 2003 security has a lot of workarounds and issues. For example, I have two databases (a front-end and a back-end linked to the front-end). I wanted to secure both of them. For example setting password (this is database password and not .mdw) to back-end (BE) blocks the starting up of front-end (FE) database. Joining .mdw in both the databases (FE and BE) also doesn’t work which means it causes other databases to also prompt for credentials. So a combination of setting password to front-end and joining back-end to secure .mdw has worked. Now the databases are secure and can be opened by credentials (.mdw creds for back-end and .mdw creds + database password for front-end). Does someone have an idea why this happens?


I'm trying to connect a database to an ASP.Net front end. The .net compiler gives me an error

c:\Work\Web-based Applications\XYZCorp\Login.aspx.cs(13,40): error CS1009: Unrecognized escape sequence


The line at which the error resides, is:

string CONNSTR = "server=VARUN\S QLEXPRESS;integrated security=true;database=cs555";


The compiler considers '\S' in the server name as an escape sequence. How do I overcome this without renaming the server???



Hi Guys,

I am trying to use SQL authentication on my moss portal server (to be my web front end) and I am unable to connect.

When I run:

PSConfig -cmd -configdb
-server servername\sqlinstance
-database SharePoint_Config
-admincontentdatabase SharePoint_AdminContent database name
-user domain\SPExternalAdmin
-password password
-dbuser SPExternalUser
-dbpassword password

it tells me that the username is invalid.  the account must be a valid domain account.

am I able to use a domain account for this (keep in mind the moss server is not on the domain)

I can run the scrip differently (see below)

PSConfig -cmd -configdb
-server servername\sqlinstance
-database SharePoint_Config
-admincontentdatabase SharePoint_AdminContent database name
-user local admin
-password password
-dbuser SPExternalUser
-dbpassword password

and it gets farther...starts to process...but then says it cannot run because it is not a domain account.


Have any one of you used sql authentication on a moss server?  is there some sort of tutorial to see what i am missing?

The end goal is to put this moss-portal server on our DMZ but i want to make sure i can reach it before hand...should i just put it on the domain...connect it to our sharepoint db...and then toss it in the DMZ?




We are upsizing our MS Access database to SQL Server 2008 and want to let users keep on using MS Access as a front end since they are familiar with the current application's reports and data entry screens. The users have been using MS Access replication so what is the best way to let these "Occasionally Connected" users replicate their local MS Access data to SQL Server?

On the SQL Server side, merge replication is the best option. I thought one option was to use SQL Server Compact Edition on the laptops so it would be a SQL Server to SQL Server replication scenario with MS Access being a front end to SSCE but that doesn't look feasible from other posts I've read.

How have other people solved this? It would seem that the synchronization framework would be a natural fit but it looks like this is not feasible as well.

Thanks in advance for any advice.





We have a SharePoint product and we have based our licensing on the numer of front-ends. We are using the SPServerRole enumeration (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spserverrole(office.12).aspx  ) to check the number of front-ends in the farm.


My question is – when will the server introduce itself as the front-end rather than application? What services/components have to be on the server to switch from one to another? Is a server with only the central admin a front-end or application? How about with only the indexing service? Or when it host web app and is indexing at the same time?


I understand the logical definition of front-end and application server – I need to know when SharePoint will define server as front end and when as application.


Best regards,



So, I've cajoled my client into installing a new server (with MS Server 2008!), and SQL Server 2005 just for me.

My app is a split app with 103 tables in the back end, and maybe 200 queries, 150 forms, 1,000 lines of code in modules/classes, etc.

I have time. I want to do it right. Some strategems would be appreciated....perhaps a checklist or roadmap. I've fired up MSSQLServer Migration Assistant. Took awhile to churn through all me tables, but did finish. Not sure what to make of the results, yet, but I did do that.


    ....   Security. I figured I'd set up a sql-signon with two users -- "App-User", and "App-Admin". Figured I'd somehow code the username and password into the connection strings for the linked tables in the FE. I'd appreciate any experience or advice on that. Thought I'd put the username/password in a hidden table in the front end. Or, since I use ACCDE files, maybe it's just as safe to hard-code it into the FE. Not sure how to then post that into the linked tables. I know how to alter the tabledef collection. I suppose that's the trick? Do that at startup?

   ...    Queries. I figure I should make the read-only queries I use for reports, etc. into views. Other rules of thumb on that? And others should become passthru queries? Again, rules of thumb on that?

   ...    Utility Functions.  Some are used in queries. Those maybe should become stored procs? Maybe some of the module code gets cast the same way? Particularly for stuff I have them run periodically as maintenance (create shadow report table...import stuff, etc.)


What else do I need to think about?


Thanks in advance!


What makes a web front-end server a front-end server ?

I know it might be strange, but are there any recommendations regarding on how to configure a WFE in a small farm?


Thank you for your help



Is it possible to do a distributed BizTalk UDDI 3.0 so that we have one UDDI backend database and multiple UDDI web front ends.  We would like to have uddi web service for each environnents (Dev, Uat, Integratio, Pre-production and production). My constraint is that i can't have more than one sql server and i think that UDDI 3.0 don't support multiples instances on the same server.

Thanks for help.


Sorry bit of a SQL newbie here.



HOSTED SQL 2008 Server

Access 2000 Front end

I have a table 'Trainees' which has 65 columns. The access front end is used to update data in this table and one field 'Photo' stores an image in a VARBINARY(MAX) field.

Now everything worked perfectly until I recently added 2 more columns to the table using SQL Server Management Studio. As soon as I re-linked the table in the access front end, the images will not save to the database.


Now from what I can see happening:

Backup of Access Front end, will still post images to the 'Photo' field.

Current Access Front end (re-linked) with extra 2 fields showing - will not post to 'Photo' field. In fact, upon clicking the save button, you can see the image blank before the form closes.

Is there a limit to the number of fields a linked table can hold in Access?

As I say bit of a novice and not sure how to rectify this, any suggestions would be appreciated.


Can someone give guide how we can create seprate web application on different front end server? In current server we have 2 application server, 1 index server, 1 query servr and 1 db server. Currently we have only 1 application on 1 front end server. We would like to create another web application, I know we can create another web application on same server where we have already one web application, but I would like to know that can we not create a new application on different web application, so we will have seprate SSP an.

What are the steps for this, is this recommended and what are pros and cons for this?


I installed SP 2010 Foundation.

I haveone AD DC servr,  one DB server (only SQL 2008 on it) and one SP2010 server (with all other services).

That all works.

Now i wanted to expand to three tier farm.

So my question is if I add more servers with SP on them (they should be frontend) how can i separate so they only act as web servers and this one as application.

If someone could clarify me the differences and steps to make this.




So, we have a typical medium size farm that we are in process of setting up. Two Web Front End servers, one Application server, and clustered SQL server.

We would like to have our two Web Front End servers act as query servers. So we found the article below (see bottom) on moving Query Component to Web Front End servers. On our Application server we went into Central Admin, Manage Service Applications, Search Service Application, clicked on Modify...and tried to edit Query Component 0 under Index Partition. Now if you follow the steps below, you will end up with three items:

Query Component 0                  SPWF2 (Web Front End Server 2)

Query Component New-1         SPWF1 (Web Front End Server 1)

Query Component New-2         SPWF2 (Web Front End Server 2)

Shouldn't there be only two items which would be our two Web Front End Servers? Are we doing something wrong?


To move the query component to a Web server

On the Central Administration Home page, click Manage service applications.

On the Services Applications page, click Search Service Application.

On the Search Administration page, in the Search Application Topology section, click Modify.

On the Topology for Search Service Application: Search Service Application page, in the Index Partition section, click Query component 0, and then click Edit Properties.

On the Edit Query Component page, select one of the front-end Web servers from the Server drop-down list, and then click OK.

On the Topology for Search Service Application: Search Service Application page, in the Index Partition section, click Query component 0, and then click Add mirror.

A query component mirror is a replica of the index partition on another server — in this case, on one of the front-end Web servers.

On the Add mirror query component page, in the Server drop-down list, select the front-end Web server that you identified on the Edit Query Component page, and then click OK.

Repeat the preceding steps and configure the second front-end Web server as a host for a query component and its mirror.





We have a SharePoint product and we have based our licensing on the numer of front-ends. We are using the SPServerRole enumeration (http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spserverrole(office.12).aspx ) to check the number of front-ends in the farm.


My question is – when will the server introduce itself as the front-end rather than application? What services/components have to be on the server to switch from one to another? Is a server with only the central admin a front-end or application? How about with only the indexing service? Or when it host web app and is indexing at the same time?


I understand the logical definition of front-end and application server – I need to know when SharePoint will define server as front end and when as application.

Pawel Wróbel Datapolis.com
Is there any way to set call forwarding settings on behalf of end users on the FE server? We have a deployment where we need to temporarily forward a large number of users calls to their old IP-PBX number and would like to avoid having the end users setting the CFA in MOC themselves.


I have a three WFE and a SQL server in my farm.

I have 9 web applications.  (every web application has its own app. pool & web site on IIS)

When I create a web application on CA it creates it on all 3 WFE servers. (I can see on their IIS manager)

Is there a way to separete them? I mean I want 1 web application to be separeted on a WFE, I want to dedicate 1 wfe for that web application. Is there a way to do that?

Or at least is there a way to divide/distribute  web applications between servers? (3 pairs, each on one)


I have a MOSS 2007 WFE that on a VMWare virtual machine that has extreme lag.  The SQL backend is reporting no issues.  Just accessing the VM to move around in the OS (start, run, etc) takes 2-5 minutes, and as a result the users are seeing extreme delays in accessing pages, search results populate 30+ secs rather than sub-second, if at all, clicking links take forever to respond, if at all, etc.

i'd like to move the a new VM because attempting to work in that VM is maddening.  Whats the proper procedure to migrate all the pertinent configs etc for the WFE to another machine?  NOTE:  The second VM has been created and already has a different name and IP.


Hi All, What is the correct procedure for deploying MicrosoftSoft  Network  Load Balancing on Windows Server 2008. There is no requirement for a hardware soultion and am only doing this to have availability in the event of one of the servers not being available. This will be a production environement in a smalll server farm setup with very light user usage. Setup consists Two web server, an application server (although there really isn't a need for one but their all virtual and customer is happy to allocate the resource) plus the SQL server.

I thought i would just enable NLB on the two web front servers and use these, then a colleague pointed out that having the web servers laod balancing themselves would only add to the load and he suggested adding another virtual server to act as the load balancer. This seems like a watse of a server just to ast as a load balancer.

 Is it okay in a production environment (with a very light user load) to run the load balancing on the two front end web servers or does this increase the load? i thought i knew the answer until i though about it?

Thanks in advance.  



I added a table of contnets webpart to the page.when I browse the site from the appserver every this is working fine.When I borwse fron the front end server i am getting error file not found when i select a site,And when i try to expand the site

There was an error in the call back.

Can any one suggest me why this erros is occuring.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure