Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How do I edit live SQL data with Access 2007

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
phewdesign
Asked:
phewdesign
  • 7
  • 7
1 Solution
 
chapmandewCommented:
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
 
phewdesignAuthor Commented:
Thanks, Got that far.

It gives me a write confict and won't let me save the record. Any more thoughts?
0
 
chapmandewCommented:
thats weird...are you just opening the table and editing data by hand?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
phewdesignAuthor Commented:
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
 
chapmandewCommented:
try doing the modification through an update query in access instead...see if that works.
0
 
phewdesignAuthor Commented:
it does indeed. Good call.

So any ideas what else I can do?
0
 
chapmandewCommented:
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
 
phewdesignAuthor Commented:
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
 
chapmandewCommented:
>>As they have builk product to update at a time.

Not sure what you mean?
0
 
phewdesignAuthor Commented:
the table I'm accessing is full of products. They wanted to update many products quickly and this type of view realy helps.
0
 
chapmandewCommented:
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
 
phewdesignAuthor Commented:
do you by any chance have a link?
0
 
cquinnCommented:
You can only update a table via Access if there is a unique identifier on the table eg an Identity (autonumber) column.
0
 
phewdesignAuthor Commented:
its got a PK from SQL
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now