Solved

How do I edit live SQL data with Access 2007

Posted on 2008-06-24
15
283 Views
Last Modified: 2010-05-18
I've previosuly set up Access 2000 to edit live SQL data.

I can't get it to work with 2007 and its doing my head in, this is kinda urgent now, please help.

I can read the data but not write to it. The user does have write/update permissions
0
Comment
Question by:phewdesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21854375
run odbcad32 (from rum prompt) to open odbc manager.  From there, create a link to your live sql server database.
Once you've done this, in Access right click and create a linked server.  Select an odbc connection, and select the connection you just made to your live server.  From there, you should see a listing of the tables on that particular database.  Select the ones you want and then edit away.  Make sure when you setup the odbc link that you include the necessary credentials for the user that you want to edit the data with.
0
 

Author Comment

by:phewdesign
ID: 21854413
Thanks, Got that far.

It gives me a write confict and won't let me save the record. Any more thoughts?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21854709
thats weird...are you just opening the table and editing data by hand?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:phewdesign
ID: 21854724
yes, just opening and editing.

the user is the same as we use on thier website, so i know it has all the access.

This is the same from two different location, and both IP have been added to the SQL firewall too.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21854731
try doing the modification through an update query in access instead...see if that works.
0
 

Author Comment

by:phewdesign
ID: 21854990
it does indeed. Good call.

So any ideas what else I can do?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21855146
Not really...I don't really advise just updating the data through the viewer like that (I've always had problems similar to what you're having).  I would use update queries to update the data instead.

If it makes you feel better, I've never seen a good interface for updating sql data the way you're wanting too.  EM and SSMS are terrible for it.
0
 

Author Comment

by:phewdesign
ID: 21855173
It just an easy way for a client to do it, As they know it worked in 2000. total pants!

As they have builk product to update at a time.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21855209
>>As they have builk product to update at a time.

Not sure what you mean?
0
 

Author Comment

by:phewdesign
ID: 21855219
the table I'm accessing is full of products. They wanted to update many products quickly and this type of view realy helps.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21855230
Do they have management studio?  There is a free version that you can use to connect to a SQL Database...that should give them what they need to modify the data.  

Strange that Access isn't working that great.  
0
 

Author Comment

by:phewdesign
ID: 21855254
do you by any chance have a link?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21855264
0
 
LVL 15

Expert Comment

by:cquinn
ID: 21855501
You can only update a table via Access if there is a unique identifier on the table eg an Identity (autonumber) column.
0
 

Author Comment

by:phewdesign
ID: 21855512
its got a PK from SQL
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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