Solved

Editing data easier with SQL Server 2005

Posted on 2007-12-03
7
151 Views
Last Modified: 2010-08-05
I am new to SQL Server 2005 (and SQL in general)
I mainly used MS Access databases in the past.  
Using Access to edit, look at and sort data is extremely easy.

With SQL Server, I find it almost impossible.  
The only tool I have right now is SQL Server Management Studio

I can't seem to sort properly, on any fields (if at all), I cannot easily edit one field.
There are many jobs I used to do with Access that are no longer practical in SQL Server.
With SQL server, I have to write code to do the easy stuff.

So my question is:

Is there a third party interface to SQL server data that makes it as easy to massage the data?
Something that will alow me to edit column by column or field by field, searchable sortable and as "friendly" as Access?


I am really looking for people who know what I am talking about and have a program they use (or used) in mind. I am not looking for  just a general link to a third party app (I can get that on google), so if you haven't at least tried it, please don't respond.



0
Comment
Question by:EGormly
  • 4
  • 2
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
If you are very comfortable in Access, you can just Access as the front-end to the SQL 2005
database.

1) Create a new Access MDB file

2) Go to File|Get external data|Link tables to launch the table-linking wizard

Once you link in the SQL 2005 tables, you can view/edit/query/report on them as if they were
Access tables.
0
 

Author Comment

by:EGormly
Comment Utility
Hey that's really cool...

ony one problem, nothing is editable, did I miss a setting somewhere?
Its just like in SQL Server, I cannot edit anything (at least I can sort)

I need to be able to edit, editing is more important that sorting.
0
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
If you're looking for buttons to sort columns or search columns you are right you won't find that.  However, you shouldn't have any trouble easily editing any field if you just open a table.  

SQL Server Management Studio really isn't the best place to be editing your data anyway.  You should be doing it through queries which will allow you to use transactions.  Transactions can save your behind if you do the wrong thing.

While MSAccess is "friendly" it's also costly.  When you query data or open tables in MSAccess you are almost always sending your entire database across your network, unless you're using pass-through queries.  
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:EGormly
Comment Utility
cmangus:
Take no offense, I do truly appreciate your efforts to answer.

That said:

You said
>>"If you're looking for buttons to sort columns or search columns you are right you won't find that."

I thought I made that clear, I want to use data like I did before with Access.
So clearly, using SSMS is not what I want.

>>"However, you shouldn't have any trouble easily editing any field if you just open a table. "
I have lots of trouble as I stated in the opening question, I cannot edit the data, I do not know why and thus.. one of the reasons for this post.

>>"You should be doing it through queries which will allow you to use transactions."

When I have an order in my system and I need to change the Purchase Order number from 12456A to 12345B,  using a queries which allows me to use a transaction is a completely ridiculous method (to me at least).

Not only wouldn't I know where to begin with querries and transactions, but my entire goal was to be able to edit like I used to in Access, (a 5 second change in that example)


I just need an easier way to manipulate my data, I have limited time and it is usually devoted to other areas.
0
 
LVL 17

Expert Comment

by:Chris Mangus
Comment Utility
I understand where you're coming from.

If you can't edit data directly in tables in SSMS then you don't have the necessary permissions.  This problem was also reflected in your inability to edit data in linked tables via MSAccess.  You need to check with your DBA and have your permissions corrected.

While you may find queries a ridiculous way to maintain data, it is the safest and best way to do it.  In the long run, you'll find it serves you well to learn how to to it.  It's quite simple.  I do recognize that this doesn't solve your immediate problem.

Once your permissions are fixed, you should be able to work with the data in MSAccess.  No third party tool is required.
0
 

Author Comment

by:EGormly
Comment Utility
Thank you cmangus
I am giving the points to matthewspatrick for hthe first reponse with Access links
(I am a dolt for not realizing that)

but you have helped as well, I will look into queries at a later time.

0
 

Author Closing Comment

by:EGormly
Comment Utility
pretty silly that I didn't already know this or even try it.
Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now