November 22, 2011 at 7:16 am
Hello,
I was wondering if someone could tell me how to delete data from SQL Server 2008 in a table using Excel 2010.
What i have done so far
1. So far i have connected a single table from SQL server to a worksheet in Excel 2010 which shows me all the data. I've done this by using the connections features under the data tab.
2. Open the SQL Server Surface Area Configuration and enabled 'Openrowset and openDatasource support'
3. Done some research on the Internet so far i have found this method
DELETE SP
FROM [AdventureWorks].[Sales].[SalesPerson] SP
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;',
'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus
FROM [UPDATE_Example$]') T
ON SP.SalesPersonID = T.SalesPersonID
AND SP.TerritoryID = T.TerritoryID
GO
SELECT * FROM [AdventureWorks].[Sales].[SalesPerson]
WHERE SalesPersonID IN (1, 2, 3, 4, 5)
GO
The area I'm having problems with
1. I only seem to fine examples joining two tables together where as I'm only using a single table.
2. Where exactly should i place the code in Excel or SQL SP.
3. I want some kinda of button that the user can click to specify which row they want to delete. How do i achieve this?
Would really appreciate guidance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
November 22, 2011 at 8:05 am
Excel is a spreadsheet program, not a database program. It makes a terrible database program.
Excel is a spreadsheet program, not a user interface design program. It makes a terrible interface to a database program.
One of the critical steps in designing a successful application is choosing the right tool for the job. Excel is the wrong tool for this job. MS Access would be a better choice, because it at least is a database program and has basic interface design tools.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2011 at 8:10 am
Thanks for your reply Drew for your reply.
I'm definitely open to new approach, do you have any information or any step by step guides on how to do this using MS Access instead?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
November 22, 2011 at 8:20 am
I've found the information i need to link SQL Server to Access, so ignore my previous question.
Can you tell me instead how to delete data inside of MS Access 2010 which is connected to SQL Server? Advice? or a link would be much appreciated
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
November 22, 2011 at 9:42 am
Hello
Allen thanks for the advice.
It would seem I've answered my own question lol. Just in case anyone else is experiencing the same problem or would like to achieve the same I'll provide a brief steps of how i accomplish this.
Using Microsoft Access 2010
SQL Server 2008 R2
1. Open Microsoft Access create a blank database.
2. Click on the External Data tab and select ODBC Database, in the Get External Data - ODBC Database window select 'Link to the data source by creating a linked table' then click ok.
3. In the 'Select Data Source' windows select ' Machine Data Source' tab and click on New.
4. In the 'Create New Data Source' window select 'User Data source(Applies to this machine only) ' then click on next and select 'SQL Server' and next again then finish.
5. The next window you will see three text field enter the necessary information and select next twice.
6. Change the default database to the DB that contains the table you wish to connect to Access then click on next and finish.
7. Test the connection and click ok, you will now be back to the 'select data source' window. Select the newly created data source name DSN and click ok.
I hope this helps someone.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply