Excel 2010 Front End to SQL Server 2008 Backend

I have a SQL Server 2008 database that I need to have a couple of interns do a lot of data entry to.  This will be direct writing to a single table.

To this end, I thought the easiest solution would be to connect an Excel spreadsheet as the front end to the SQL Server table.  However, it would appear not so easy. After adding several rows, the data doesn't seem to be moving up to the SQL Server table.

Am I missing a switch that I need to set to allow adds/changes made to the spreadsheet to be added to the database?

LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The better tool to use would be Access for that type of data entry.  Excel really isn't for entering data into SQL Server in real time.

If you want to stay with Excel have the data entered as usual then you'll need to do a data import from SQL Server using the data transformation services.  You'll be able to select your Excel file and import the data that way.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
To import the data into SQL server 2008

1.  Start up SQL Server Management Studio
2.  Right click on your database to which you want to import the data -> Tasks -> Import

A wizard will pop up guiding you through the rest of the process.
ClifAuthor Commented:
Don't have, nor can we afford Access at this point in time.

Importing data into SQL Server as you describe isn't my intent.  I want to use Excel as a front end.  That is, use the Excel grid as a view to the database where information typed in is immediately available (at least at row level).

As an aside, having an expert named "dr_linux" answering questions about Microsoft technology isn't all that comforting.  :)
Hehe, sorry about the name.  Back to point, you are really not going to be able to use Excel as your front end to data entry to SQL Server.  Not without some extensive VBA in the background.  Again Excel really isn't going to be the tool you'll be able to use.  However there are many other options available for cheaper if you just want to enter data.  Navicat has products for SQL Server and you can always use the Managment console that came with SQL Server.  Another good product is http://www.dbqwikedit.com/
We use both of these for data entry.
ClifAuthor Commented:
Well, I did convince the owner of the company to buy Access.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.